CODEWITHSUNDEEP
c#sql.netlinqlambda
PUBG
PUBG

Reputation: 199

Multiple JOINS with Conditions between them : LINQ query

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.

Upvotes: 2

Views: 88

Answers (3)

tgralex
tgralex

Reputation: 814

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!

Upvotes: 1

PUBG
PUBG

Reputation: 199

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.

Upvotes: 1

tgralex
tgralex

Reputation: 814

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

Upvotes: 2

Related Questions