CODEWITHSUNDEEP
linqc#-4.0lambdaentity-framework-6
Rohit Sethi
Rohit Sethi

Reputation: 57

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

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.

Upvotes: 1

Views: 1380

Answers (1)

David Lee
David Lee

Reputation: 2100

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

Upvotes: 4

Related Questions