CODEWITHSUNDEEP
c#sqlentity-frameworklinqlinq-to-entities
Raajkumar
Raajkumar

Reputation: 897

Entity Framework - Most recent record in the left join

I know there are lot of similar queries posted around, but I would like to know how can we write the below SQL query in entity framework,

select * from RequestDetail d 
    left join (select RequestDetailId, Max(RequestedOn) RequestedOn from RequestHistory group by RequestDetailId) as h
    on h.RequestDetailId = d.Id

Read lot of post and I not able to find exact replica.

Upvotes: 0

Views: 1403

Answers (1)

ocuenca
ocuenca

Reputation: 39376

You can do the same in Linq to Entities:

var innerquery=from e in  RequestHistory 
               group e by e.RequestDetailId into g 
               select new {
                             RequestDetailId=g.Key, 
                             RequestedOn =g.Max(r=>r.RequestedOn)
                          };

var query= from d in RequestDetail 
           join h in innerquery on d.Id equals h.RequestDetailId into gj
           from e in gj.DefaultIfEmpty()
           select new {d, e};

I have created the inner query first to help you understand better how to do it, but you can merge both queries in one, but that doesn't make any difference.

Upvotes: 3

Related Questions