实体框架复杂查询到嵌套c#对象

Question

我在这里有以下查询。我如何得到类似的linq查询这个sql.

SELECT * 
FROM PublishedLineBar 
WHERE PublishedRosterShiftId 
IN (SELECT LatestShiftId FROM 
       ( SELECT MAX(PublishedRosterShiftId) as LatestShiftId, DayNumber 
         FROM PublishedRosterShift
         WHERE employeeid = 14454
         GROUP BY DayNumber) 
       as ShiftProjection )

我已经使用下面的linq翻译，但它是失败的地方。

var shifts = dbContext.PublishedRosterShifts
                .Where(h => h.EmployeeId == EmployeeId);
var inner = shifts
    .Select(x => new
        {
            LatestShiftId = shifts.Max(p => p.PublishedRosterShiftId),
            DayNumber = x.DayNumber
         })
     .GroupBy(s => s.DayNumber)
     .Select(g => g.FirstOrDefault());
 var q = from f in shifts
     select new
     {
         LatestShiftId = shifts.Max(p => p.PublishedRosterShiftId),
         DayNumber = f.DayNumber
     };
 var query = from l in dbContext.PublishedLineBars
     where inner.Select(s => s.LatestShiftId).Contains(l.PublishedRosterShiftId)
     select l;

Answer 1

下面是用于SQL IN (...)子句的LINQ等价子查询(删除了不必要的嵌套):

var inner = dbContext.PublishedRosterShifts
    .Where(s => s.EmployeeId == EmployeeId)
    .GroupBy(s => s.DayNumber)
    .Select(g => g.Max(s => s.PublishedRosterShiftId));

和使用它的查询:

var query = from l in dbContext.PublishedLineBars
            where inner.Contains(l.PublishedRosterShiftId)
            select l;

或

var query = dbContext.PublishedLineBars
    .Where(l => inner.Contains(l.PublishedRosterShiftId));

您在尝试中缺少的是，在SQL中SELECT MAX(PublishedRosterShiftId) as LatestShiftId, DayNumber对GROUP BY操作符的结果进行操作，因此在LINQ中Select应该在GroupBy之后。