Aniket Aniket - 19 days ago 12
SQL Question

Entity framework complex query to nested C# object

I am having following query here. how do I get similar linq query for this 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 )


I have used below linq translation, but it is failing somewhere.

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

Here is the LINQ equivalent of your subquery used for SQL IN (...) clause (with unnecessary nesting removed):

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

and the query using it:

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

or simply

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

What you are missing in your attempt is that in SQL SELECT MAX(PublishedRosterShiftId) as LatestShiftId, DayNumber operates on the result of the GROUP BY operator, hence in LINQ the Select should be after GroupBy.

Comments