INNER Join and LEFT OUTER JOIN in EF-Core with Linq

.net-core c# entity-framework-core linq sql

Question

I am trying to convert below SQL to Linq query in c# (.net core 2.1 and EF core 2.2)

SELECT  TD.*, RD.Match
        FROM    TransactionDetail TD
                INNER JOIN dbo.Measure M ON M.InternalID = TD.MetricCode
                LEFT OUTER JOIN (
                    SELECT tmp.ID, tmp.ReportingDate, 1 AS Match
                    FROM tmp
                ) AS RD ON RD.ID = M.Frequency AND RD.ReportingDate = TD.ReportingDate
        WHERE   RD.Match IS NULL AND
                TD.BatchID = @batchID AND
                NOT EXISTS (SELECT TransactionFailureReasonID FROM TransactionDetailFailureReasons R WHERE R.TransactionDetailID = TD.ID and R.TransactionFailureReasonID = 'NRD') AND
                NOT EXISTS (SELECT TransactionFailureReasonID FROM TransactionDetailFailureReasons R WHERE R.TransactionDetailID = TD.ID and R.TransactionFailureReasonID = 'RDP') AND
                NOT EXISTS (SELECT TransactionFailureReasonID FROM TransactionDetailFailureReasons R WHERE R.TransactionDetailID = TD.ID and R.TransactionFailureReasonID = 'RDF')

Could someone please help me how I can do first inner join and then LEFT OUTER JOIN on same?

I have come so far,

var IQ1 = (from TD in IngestionHubContext.TransactionDetail
                   join M in  ModelHospitalPreviewContext.Measure on TD.MetricCode equals M.InternalId 
                   join R in RD.DefaultIfEmpty on new {ID = M.Frequency, TD.ReportingDate} equals new { R.ID, R.ReportingDate} into J
                   //????? 

Any help or suggestion is appreciated.

Thanks in advance.

1
1
3/1/2019 4:50:56 PM

Popular Answer

Below you can find a simplified version of your example, using a single DbContext:

var batchId = 123;

var transactionDetailFailureReasonsToExcludeQuery =
    from r in dbContext.TransactionDetailFailureReasons
    where
        r.TransactionFailureReasonID == "NRD" ||
        r.TransactionFailureReasonID == "RDP" ||
        r.TransactionFailureReasonID == "RDF"
    select r.TransactionDetailID;

var query =
    from td in dbContext.TransactionDetail
    join m in dbContext.Measure on td.MetricCode equals m.InternalID
    join rd in dbContext.Tmp on new { m.Frequency, td.ReportingDate } equals new { Frequency = rd.ID, rd.ReportingDate } into rdItems
    from rd in rdItems.DefaultIfEmpty()
    where
        rd == null &&
        td.BatchID == batchId &&
        transactionDetailFailureReasonsToExcludeQuery.Contains(td.ID) == false
    select td;

Now you need to update it to use the right DbContext for the entities.

Hope this helps.

3
3/1/2019 3:21:48 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