Lambda expression Join with last occurrence of right table

Question

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.

I have written code upto this: var Tasks = db.Task.Join(TS.EmpTask, t => t.ID, et => et.TaskID, (t, et) => new { t, et }) .Select(m => new TaskObject { ID = m.t.ID, Title = m.t.Title, XXXX= m.et.XXXX, }); What should I add here? — Ruby Nanthagopal, Jul 27 '16 at 07:24
I'm not quite sure about your expected result. Do you just want the **last** occurrence of an`EmpTask` related to a `Task`, or should it instead be a list of all `EmpTask` related to a `Task`? — Lars Kristensen, Jul 27 '16 at 07:29
Define "last". There's no notion of first or last if your data isn't ordered. Are you sorting your data on `ID`? or on `XXXX`? — Jeff Mercado, Jul 27 '16 at 07:29
I want a list of all the tasks - that joined with one raw from EmpTask table - that row must have max id and taskId much with id of task table — Ruby Nanthagopal, Jul 27 '16 at 07:36
Possible duplicate of [How to get first record in each group using Linq](http://stackoverflow.com/questions/19012986/how-to-get-first-record-in-each-group-using-linq) — Vladimir Baranov, Jul 27 '16 at 07:41
I checked that link. But I want Nima -12 | John -2 | Sara - 4 as result from that question. — Ruby Nanthagopal, Jul 27 '16 at 07:46

neer · Answer 1 · 2016-07-27T08:06:46.133

Suppose that you want max EmpTask.

var result = (from t in db.Task
         join r in db.EmpTask
                      .GroupBy(i => i.TaskID)
                      .Select(i => new { TaskID = i.Key, EmpTaskId = i.Max(t => t.ID)})
                  on t.ID equals r.TaskID
         join et in db.EmpTask on r.EmpTaskId equals et.ID
         select new
         {
            ID = t.ID,
            Title = t.Title,
            XXXX = et.XXXX
         }).ToList();

Lambda Expression

var result = db.Task.Join(
            db.EmpTask
                      .GroupBy(i => i.TaskID)
                      .Select(i => new { TaskID = i.Key, EmpTaskId = i.Max(t => t.ID)})
                  ,
            t => t.ID,
            r => r.TaskID,
            (t, r) => new { Task = t, EmpTaskTmp = r}
         ).Join(
            db.EmpTask,
            et => et.ID
            y => y.EmpTaskTmp.EmpTaskId,
            (et, y) => new { Task = y.Task, MaxEmpTask = et}
         ).Select(i => new
         {
            ID = i.Task.ID,
            Title = i.Task.Title,
            XXXX = i.MaxEmpTask.XXXX
         });

Could you write it in Lambda expression. Thanks – Ruby Nanthagopal Jul 27 '16 at 07:37 — Ruby Nanthagopal, Jul 27 '16 at 07:37

score 0 · Accepted Answer · answered Jul 27 '16 at 14:17

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();

Lambda expression Join with last occurrence of right table

2 Answers2