Entity Framework Query with MAX

Question

I've been banging my head against the wall trying to translate a simple SQL Query into EF query..

Can anyone help please.. Following is the query I am trying to translate.

SELECT p.[UniqueId]
  ,p.[CAI]
  ,p.[HRGuid]
  ,p.[FullName]
  ,p.[Email]
  ,a.*
FROM [Participant] p
INNER JOIN 
(
    Select * FROM Assignment where assignmentNumber = 
    (Select MAX(AssignmentNumber)FROM 
    Assignment GROUP BY UniqueId)
) a
ON p.UniqueId = a.UniqueId

Basically I'm trying to get Participant along with their latest assignment.

Aducci · Accepted Answer · 2011-12-21T19:12:36.707

0

You will need to create your Participant entity using Linq-Objects. You need to customize after AsEnumerable in order to create your entities

var query = (from p in context.Participant
            join a in context.Assignment on p.UniqueId equals a.UniqueId into ag
            select new
            {
              Participant = p,
              Assignment = ag.OrderByDescending(x => x.AssignmentNumber).FirstOrDefault()
            }).AsEnumerable()
              .Select(x => new Participant(x.Participant )
                     {    
                        Assignments = new Assignment[] { x.Assignment }
                     };

edited Dec 21 '11 at 19:12

answered Dec 21 '11 at 18:03

Aducci

26,101
8
63
67

Thank you for the quick response. This is indeed what I was looking for. However I do not want anonymous types. Is there any possibility I can get Entities back (Assuming I am projecting all the columns from Participant). – Armaghan Dec 21 '11 at 19:02
@Armaghan - Can you please show me the structure of Participant and Assignment entities? I modified the answer to add a linq to objects step – Aducci Dec 21 '11 at 19:08
Participant and Assignment are POCO entities. – Armaghan Dec 21 '11 at 19:28
as simple structure is as follows: `class Participant () { String UniqueId {get; set;} String Email {get; set;} IEnumerable Assignments {get; set;} } class Assignment() { Int32 AssignmentNumber {get; set;} DateTime StartDate {get; set;} }` – Armaghan Dec 21 '11 at 19:39
It works perfectly with a slight modification to the second select expression. Thank you very much! – Armaghan Dec 21 '11 at 23:20

Entity Framework Query with MAX

1 Answers1