如何更新此EF查询 [英] how do update this query of EF

问题描述

我有两个表Employeestatus和EmployeeCategory

我正在使用以下查询来获取我的数据，它工作正常，但我需要修改它。新查询IN EF

i have two table Employeestatus and EmployeeCategory
i am using following query to get my data, its working ok but i need modification in it . new query IN EF

var sectorEmployees = from ec in entities.EmployeeCategories.Where(x => x.Category_Id == 1 || x.Category_Id == 2 && x.Sector_Id == sectorId)
                                     join es in entities.EmployeeStatus.Where(x => x.EmployeeStatusType_Id == 1) on ec.Employee_Id equals es.Employee_Id
                                     select es;

但问题是，对一名员工有多条记录，但我想要最后一条该表中该员工的记录，我正在使用满足我需求的以下查询

BUT problem is that there are multiple record against one employee but i want last records of that employee from the table, i was using following query that was fullfilling my needs

var sectorEmployees = _service.GetEmployeeStatusBySector_Id(sectorId).OrderByDescending(x => x.EndDate).GroupBy(x => x.Employee_Id).Select(x => x.LastOrDefault()).ToList();

如何修改第一个查询以满足第二次查询完成的需求。

how can i can modify 1st query to full fill my needs that is done by 2nd query.

推荐答案

我不使用linq语法。我更喜欢扩展语法，因为在将任何请求发送到数据库之前，将任何查询分解为更小的步骤要容易得多。我会尝试回答，但我的答案将是扩展语法。很抱歉，如果这不适合您的需求。

I don't use linq syntax. I prefer Extension syntax purely because it's so much easier to break any query down into smaller steps before any requests are sent to the db. I will try to answer but my answer will be in Extension syntax. Sorry if that is not appropriate for your needs.

var sectorEmployees = entities.EmployeeCategories.Where(x => x.Category_Id == 1 || x.Category_Id == 2 && x.Sector_Id == sectorId);

var sectorEmployeesByStatuses = sectorEmployees
    .Join(
        entities.EmployeeStatus, //table to join to
        sEmployees=>sEmployees.Employee_Id, //source tables join value
        statuses=>statuses.Employee_Id,  //join tables join value
        (sEmployees,statuses)=>new{sEmployees,statuses}) //selector (create an anon type for now.  Select the bits you want later
    .GroupBy(anonType => anonType.sEmployees) // group by employee
    .Select(group=>new{group.key,group.OrderByDescending(anonType => anonType.statuses.EndDate).FirstOrDefault().statuses})  //new anon pairing only latest status
    .Where(anonType=>anonType.statuses.EmployeeStatusType_Id == 1) //trim out the employees that don't have this type status currently (or latest)
    .Select(anonType=>anonType.sEmployees).ToList(); //select the employee types so we get a neat List<entities.employeecategory>
</entities.employeecategory>

希望你能看到我是如何构建查询的。我个人扩展了我的所有实体，以包含一些返回IQueriable<>的基本和特定方法。所以像这样的查询看起来更整洁。您必须确保使用该方法传递上下文对象。



请放心，Link将优化查询，并且只会查询数据库（假设您'最后使用linq to sql）.ToList（）。

Hopefully you can see how I am building the query. I personally extend all of my entities to include some basic and specific methods that return IQueriable<> so queries like this look neater. You have to make sure to pass the context object around with that approach though.

Rest assured that Link will optimize the query and will only query the db (assuming you're using linq to sql) at the last .ToList().

这篇关于如何更新此EF查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！