Max of 2 columns with multiple group by lambda expression entity framework

c#-4.0 entity-framework-6 lambda linq

Question

I have the below query in lambda.

 var list = dbConnection.Form_Datas.Join(dbConnection.Forms, fd => fd.form_ID, f => f.form_ID,
            (fd, f) => new { form_data = fd, form = f })
            .Where(a => a.form_data.External_ID == personID)
            .Select(s => new FormDataDTO
            {
                FormID = s.form.form_ID,
                FormName = s.form.name,
                FormDataID = s.form_data.form_Data_ID,
                LastEdit = s.form_data.last_Edit,
                UserName = s.form_data.username
            }).OrderByDescending(o=>o.LastEdit).ToList();

I need to get the maximum value of LastEdit and FormDataID columns since formID and FormName is repetitive for one FormDataID. Also UserName should be shown from the max row values.

Thanks in advance.

1
0
5/3/2017 6:39:38 PM

Accepted Answer

I believe what you are looking for is the GroupByclause.

var list = dbConnection.Form_Datas
    .Join(dbConnection.Forms, fd => fd.form_ID, f => f.form_ID,
        (fd, f) => new { form_data = fd, form = f })
    .Where(a => a.form_data.External_ID == personID)
    .GroupBy(s => new 
    {
        FormID = s.form.form_ID,
        FormName = s.form.name,
        UserName = s.form_data.username 
    })
    .Select(s => new FormDataDTO
    {
        FormID = s.Key.form.form_ID,
        FormName = s.Key.form.name,
        FormDataID = s.Max(x => x.form_data.form_Data_ID),
        LastEdit = s.Max(x => x.form_data.last_Edit),
        UserName = s.Key.form_data.username
    }).OrderByDescending(o=>o.LastEdit).ToList();
3
5/3/2017 7:57:59 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow