Entity framework complex query to nested C# object

Question

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;

Ivan Stoev · Accepted Answer · 2016-11-15 12:03:51Z

1

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.

edited Nov 15, 2016 at 12:03

answered Nov 15, 2016 at 11:57

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aniket Over a year ago

Thanks alot. That helped. :)

Collectives™ on Stack Overflow

Entity framework complex query to nested C# object

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related