Multiple JOINS with Conditions between them : LINQ query

Question

I am trying to convert a SQL query into c# equivalent LINQ/Lambda like below

SELECT  mv.MeasureID, mv.ProviderID, MAX(mv.ReportingDate) AS mostRecentReportingDate
FROM    (((TransactionDetail AS td
           INNER JOIN #provider AS p ON td.TrustCode = p.Code)
           INNER JOIN #measure AS m ON td.MetricCode = m.InternalID)
           INNER JOIN #measureValue AS mv ON m.ID = mv.MeasureID AND p.ID = mv.ProviderID)
WHERE   td.BatchID = @batchID AND
        td.RowAction = 'A' AND
        (m.Type = 7 OR m.Type = 8) AND
        td.Value <> mv.Value
GROUP BY mv.MeasureID, mv.ProviderID

I am stuck at this line

INNER JOIN #measureValue AS mv ON m.ID = mv.MeasureID AND p.ID = mv.ProviderID)

Below is what I have tried so far

 var total = (from TD in ingestionHubContext.TransactionDetail
                    join P in provider on TD.TrustCode equals P.Code
                    join M in measure on TD.MetricCode equals M.InternalId
                    join MV in measureValue on M.Id equals MV.MeasureId //and logic to be fixed
                    where TD.BatchId == batchId && TD.RowAction == "A"
                    && (M.Type == 7 || M.Type == 8) && TD.Value != MV.Value
                    group TD by new { MV.MeasureId, MV.ProviderId } into Total
                    select Total);

Could you please suggest how I can write this in a better way? and handle the JOIN with AND condition in the 4th line of my code.

I have tried below which does not work since they need to match (errors at P.Id)

join MV in measureValue 
on new { M.id, P.Id } equals new { MV.MeasureId, MV.ProviderId }

I also need help in doing the MAX(mv.ReportingDate)

Any help is much appreciated. Thanks in advance.

tgralex · Accepted Answer · 2019-02-22 15:13:23Z

2

When there are multiple fields in ON clause, you need to use objects for comparing. Your LINQ query should be written as following:

    var total = (from TD in ingestionHubContext.TransactionDetail
                        join P in provider on TD.TrustCode equals P.Code
                        join M in measure on TD.MetricCode equals M.InternalId
// Here is how
                        join MV in measureValue on new {M.Id, P.Id} equals new {MV.MeasureId, MV.ProviderId}
// the rest is your original where statement
                        where TD.BatchId == batchId && TD.RowAction == "A"
                        && (M.Type == 7 || M.Type == 8) && TD.Value != MV.Value
                        group TD by new { MV.MeasureId, MV.ProviderId } into Total
                        select Total);

answered Feb 22, 2019 at 15:13

tgralex

8146 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

PUBG Over a year ago

This gives error saying 'Anonymous type cannot have multiple properties in the same name'(P.Id). which is exactly where I am stuck. could you please elaborate your answer.

Josh Gust Over a year ago

you're getting this because the anon is trying to name the property "Id" for the "P.Id" value but it already exists from the "M.Id". If the Id field name for M were mId, and the Id field name for P were pId, this would work fine, but because the columns from the two tables share the same name the anon can't handle the ambiguity. This is why the answer you posted for yourself new {mId = M.Id, pId = P.Id} is valid.

PUBG Over a year ago

Any suggestions on how I can select the MAX(mv.ReportingDate) ??

tgralex Over a year ago

Thanks Josh, since I was replying away from VS, I missed that both properties are called the same :)

tgralex Over a year ago

PUBG, sure, see my separate answer

tgralex · Accepted Answer · 2019-02-24 03:39:58Z

Sorry I did not see your second question. The final LINQ query, which will achieve what you are looking for, including Max will be the following statement:

var query = from td in transactionDetails
                        join p in providers on td.TrustCode equals p.Code
                        join m in measures on td.MetricCode equals m.InternalId
                        join mv in measureValues on new { mId = m.Id, pId = p.Id } equals new { mId = mv.MeasureId, pId = mv.ProviderId }
                        where td.BatchId == batchId && td.RowAction == "A"
                        && (m.Type == 7 || m.Type == 8) && td.Value != mv.Value
                        select new { td, p, m, mv } into queryList
                        group queryList by new { queryList.mv.MeasureId, queryList.mv.ProviderId } into groupedList
                        select new {
                            groupedList.Key.MeasureId,
                            groupedList.Key.ProviderId,
                            mostRecentReportingDate = groupedList.Max(g => g.mv.ReportingDate) };

I used simplified names for the collections of providers, measures etc, you can put your real providers there.

This LINQ query will return you the result of your original SQL query and will be as optimized by SQL Server as it can get, there is not much you can do for optimization on LINQ to DB...

Good luck!

PUBG · Accepted Answer · 2019-02-22 15:24:17Z

1

I solved by writing the query like below

join MV in measureValue on new { mID = M.Id, pID = P.Id } equals new {mID = MV.MeasureId, pID = MV.ProviderId }

This allowed conditions which use multiple table columns.

I would still appreciate if someone could help rewrite this more efficiently.

Thanks.

answered Feb 22, 2019 at 15:24

PUBG

1992 silver badges12 bronze badges

Collectives™ on Stack Overflow

Multiple JOINS with Conditions between them : LINQ query

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related