WHERE condition with SELECT, MAX statement using LINQ/Lambda

Question

I am trying to convert below SQL query to LINQ/Lambda in C#

SELECT DISTINCT M.InternalID, P.Code
    FROM (
        dbo.MeasureValue MV
        INNER JOIN dbo.Measure M ON MV.MeasureID = M.ID
        INNER JOIN dbo.Provider P ON MV.ProviderID = P.ID
    )
    WHERE MV.ReportingDate = (
        SELECT MAX(ReportingDate)
        FROM (
            SELECT ReportingDate
            FROM dbo.MeasureValue
            WHERE MeasureID = MV.MeasureID
        )  MaxReportingDate
    );

I have got so far,

    (from MV in MeasureValues
join M in Measures on MV.MeasureID equals M.ID
join P in Providers on MV.ProviderID equals P.ID
Where //???
select new //Distinct?? 
{  M.InternalID, P.Code} )

Could someone please guide me how to use nested WHERE condition as in SQL query and do MAX of nested SELECT and DISTINCT on whole?

As a whole the LINQ/Lamda should output same result as SQL query.

*I am new to SQL and LINQ

Thanks in advance.

Maybe order by ReportingDate desc and select First (or FirstOrDefault)? (Unless several rows could have the same date) — Rafalon
I would group by that subselect getting two columns with MeasureId and MaxReportingDate. Then you could join that data with your main query. BTW, that is also what you could/should have done with your SQL query (better performance) — Cleptus
@FortyTwo That product has only a trial version, its not free to use. — Cleptus
The SQL you should try to convert imho is this one: select MV.* from (select MeasureID, Max(ReportingDate) MaxReportingDate from MeasureValue group by MeasureID) maxValues inner join MeasureValue mv on maxValues.MeasureID = mv.MeasureID and mv.ReportingDate = maxValues.maxReportingDate — Cleptus

Dmitry Stepanov Dmitry Stepanov · Accepted Answer · 2019-02-26T14:55:39

Try this one:

var query = 
    from mv in MeasureValues
    join m in Measures on mv.MeasureID equals m.ID
    join p in Providers on mv.ProviderID equals p.ID
    where mv.ReportingDate == 
        (from mv2 in MeasureValues
        where mv2.MeasureID == mv.MeasureID
        orderby mv2.ReportingDate descending
        select mv2.ReportingDate
        ).FirstOrDefault()
    select new { m.InternalID, p.Code };

var distinct = 
    from q in query
    group q by new { q.InternalID, q.Code} into gr
    select new 
    { 
        InternalID = gr.First().InternalID, 
        Code = gr.First().Code 
    };

var result = distinct.ToList();

Another option to find max ReportingDate:

var query = 
    from mv in MeasureValues
    join m in Measures on mv.MeasureID equals m.ID
    join p in Providers on mv.ProviderID equals p.ID
    where mv.ReportingDate == MeasureValues.Where(x => x.MeasureID == mv.MeasureID).Select(x => x.ReportingDate).Max()
    select new { m.InternalID, p.Code };

WHERE condition with SELECT, MAX statement using LINQ/Lambda

1 Answers