Ruby Ruby - 3 months ago 11
SQL Question

Lambda expression Join with last occurrence of right table

I have two tables

Task
and
EmpTask
.

Table: Task Table: EmpTask
__________ ________________________
ID | Title ID | TaskID | XXXX
__________ ________________________
1 | task1 1 | 1 | emptask1
2 | task2 2 | 1 | emptask2 (this is last occurance of taskID 1)
__________ 3 | 2 | emptask3
4 | 2 | emptask4 (this is last occurance of taskID 2)
________________________


Result expected:

________________________
ID | Title | XXXX
________________________
1 | task1 | emptask2
2 | task2 | emptask4
________________________


I want help to write query in Lambda expression.

Thanks in advance.

Answer

I Managed to get the answer. Thanks for everyone helped :)

var result = db.Task.Join(db.EmpTask, t => t.ID, et => et.TaskID, (t, et) => new { t, et })
                    .Select(m => new  
                    {
                        ID = m.t.ID,
                        Title = m.t.Title,
                        XXXX= m.et.XXXX
                    }).GroupBy(m => m.ID, (key, g) => g.OrderByDescending(m => m.EmpTaskId).FirstOrDefault()).ToList();