Complex SQL join in LINQ (Normal and Lamba formats)

Question

Here is my table layout:

Table layout

And here is my SQL query:

SELECT
  Claim.ClaimId,
  Store.Ncpdp,
  Claim.RxNumber,
  Claim.RefillNumber,
  Store.StoreId,
  Patient.FirstName,
  Patient.MiddleNameInitial,
  Patient.LastName,
  Patient.NameSuffix
FROM Claim
INNER JOIN Store
  ON Claim.StoreId = Store.StoreId
INNER JOIN ClaimPatient
  ON Claim.ClaimId = ClaimPatient.ClaimId
INNER JOIN Patient
  ON ClaimPatient.PatientId = Patient.PatientId

How would I write this in LINQ in both normal and Lambda formats?

This is not a converter service. What did you try? Where are you stuck? What can we help you with? — nvoigt, Apr 26 '18 at 15:38
Well, I have been looking for an example where there are multiple tables that join with a "go between" table and have not had any luck - this is why I have reached out for help. I am specifically looking for the lamba syntax but thought that others might benefit from seeing both views. — Mike Henry, Apr 26 '18 at 15:51
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you? — NetMage, Apr 26 '18 at 17:37

mshwf · Accepted Answer · 2018-04-30T20:19:01.720

Here's the query expression of your SQL query (it's recommended over the lambda expression, much simpler to deal with):

var data = from claim in Claims
                   join store in Stores on claim.StoreId equals store.StoreId
                   join claimPatient in ClaimPatients on claim.ClaimId equals claimPatient.ClaimId
                   join patient in Patients on claimPatient.PatientId equals patient.PatientId
                   select new
                   {
                       claim.ClaimId,
                       store.Ncpdp,
                       claim.RxNumber,
                       claim.RefillNumber,
                       store.StoreId,
                       patient.FirstName,
                       patient.MiddleNameInitial,
                       patient.LastName,
                       patient.NameSuffix
                   };

EDIT

If you want it here's the lambda version:

var data =
            Claims.Join(Stores, claim => claim.StoreId, store => store.StoreId, (claim, store) =>
           new { claim.ClaimId, claim.RxNumber, claim.RefillNumber, store.Ncpdp, store.StoreId })

            .Join(ClaimPatients, claim => claim.ClaimId, cp => cp.ClaimId, (claim, cp) => 
            new { claim.ClaimId, claim.RxNumber, claim.RefillNumber, claim.Ncpdp,claim.StoreId, cp.PatientId })
            .Join(Patients, c => c.PatientId, p => p.PatientId, (c, p) => new
            {
                c.ClaimId,
                c.Ncpdp,
                c.RxNumber,
                c.RefillNumber,
                c.StoreId,
                p.FirstName,
                p.MiddleNameInitial,
                p.LastName,
                p.NameSuffix
            });

If it solves the problem, I will appreciate accepting the answer! — mshwf, Apr 26 '18 at 22:34
I would like to see the Lambda version of this - just so others have a reference (I just don't see many examples out there)...I will mark it as complete if no one responds soon - thx for your help. — Mike Henry, Apr 30 '18 at 12:55

Complex SQL join in LINQ (Normal and Lamba formats)

1 Answers1