如何将指定多表关联SQL查询转换为LINQ查询语法与Lambda语法？

I’ll break this down into both LINQ query syntax (the readable, SQL-like format) and Lambda method syntax, matching your exact select columns and join logic.

LINQ Query Syntax #

This format closely mirrors your original SQL structure, making it easy to map and understand:

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

LINQ Lambda Method Syntax #

If you prefer method chaining with lambda expressions, here’s the equivalent implementation:

var query = dbContext.Claims
    .Join(dbContext.Stores,
          claim => claim.StoreId,
          store => store.StoreId,
          (claim, store) => new { claim, store })
    .Join(dbContext.ClaimPatients,
          cs => cs.claim.ClaimId,
          cp => cp.ClaimId,
          (cs, cp) => new { cs.claim, cs.store, cp })
    .Join(dbContext.Patients,
          cscp => cscp.cp.PatientId,
          patient => patient.PatientId,
          (cscp, patient) => new 
          {
              ClaimId = cscp.claim.ClaimId,
              Ncpdp = cscp.store.Ncpdp,
              RxNumber = cscp.claim.RxNumber,
              RefillNumber = cscp.claim.RefillNumber,
              StoreId = cscp.store.StoreId,
              FirstName = patient.FirstName,
              MiddleNameInitial = patient.MiddleNameInitial,
              LastName = patient.LastName,
              NameSuffix = patient.NameSuffix
          });

Quick Notes: #

• Replace dbContext with your actual DbContext instance name.
• The entity set names (like Claims, Stores) should match what’s defined in your DbContext (pluralized table names are the standard convention).
• Both versions will generate SQL identical to your original query when executed (e.g., when calling .ToList() or iterating over the result).

内容的提问来源于stack exchange，提问作者Mike Henry