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.

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.

Licensed under: CC-BY-SA with attribution

Not affiliated with Stack Overflow