Advanced queries in linq to entities or stored procedures

Question

Hi all,

I'm currently building an application using entity framework. Normally I would use a stored procedure to get specific data from my database but now i'm experimenting with Entity Framework.

Now i'm facing a small challenge. I have an incident log table with a primary key, an incident id, and some data fields. I need to get all the newest rows for each incident. The sql is quite easy:

select * from incidentLog t join (select incidentId,max(id) as id from incidentLog group by incidentId) tmp on t.id=tmp.id

How can I convert this to linq to entity? Can I do it in one operation at all or should I use a stored procedure instead?

Answer 1

This should do the trick:

var query =
     (from i in context.IncidentLogs                    
     group i by i.IncidentId into g
     let maxID = g.Max(i => i.id)
     select g.Where(i => i.id == maxID)).ToList();