CODEWITHSUNDEEP
c#sqllinq.net-coreentity-framework-core
PUBG
PUBG

Reputation: 199

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

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.

Upvotes: 2

Views: 4478

Answers (2)

ibrahimozgon
ibrahimozgon

Reputation: 1187

In Ef Core, you can use your SQL queries, you don't have to change them to Linq query. For now, Ef Core doesn't support converting all Linq operations to SQL. So, converting SQL to Linq can cause a client-side evaluation.

You can find details here: Raw SQL

Please change your Select query with your model values. 

var transactionDetails = IngestionHubContext.TransactionDetail.FromSql("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')", batchIDParameter);

I hope this helps.

Upvotes: 0

yv989c
yv989c

Reputation: 1553

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.

Upvotes: 3

Related Questions