如何针对指定SQL查询编写对应的LINQ查询语句？

Let's walk through converting your SQL statement to LINQ, covering both query syntax (which reads a lot like SQL) and method syntax, plus a bonus shortcut if you're using Entity Framework with navigation properties.

First, let's recap what your original SQL does:

select c.Name, Count(cb.Id) as Total1, Count(cf.Id) as Total2
from Company c
left join CompanyBDetails CB on C.Id = CB.CompanyId
left join CompanyFDetails CF on CF.BankId = CB.Id
group by C.Name

It performs two left joins, groups by company name, and counts non-null records from each detail table (since Count(column) ignores NULL values in SQL).

Option 1: LINQ Query Syntax (Most SQL-like) #

This syntax mirrors your SQL structure closely, making it easy to follow:

var query = from c in db.Companies
            // Left join Company with CompanyBDetails
            join cb in db.CompanyBDetails on c.Id equals cb.CompanyId into cbLeftJoin
            from cb in cbLeftJoin.DefaultIfEmpty()
            // Left join the result with CompanyFDetails (safe null check for cb.Id)
            join cf in db.CompanyFDetails on cb?.Id equals cf.BankId into cfLeftJoin
            from cf in cfLeftJoin.DefaultIfEmpty()
            // Group by company name
            group new { cb, cf } by c.Name into companyGroup
            // Project the final result
            select new {
                CompanyName = companyGroup.Key,
                Total1 = companyGroup.Count(item => item.cb != null), // Matches Count(cb.Id)
                Total2 = companyGroup.Count(item => item.cf != null)  // Matches Count(cf.Id)
            };

• into cbLeftJoin + DefaultIfEmpty() handles the left join, ensuring we keep all companies even if they have no CompanyBDetails.
• cb?.Id safely handles cases where cb is null (from the first left join) to avoid null reference errors.
• Count(item => item.cb != null) replicates SQL's Count(cb.Id) because it only counts non-null records.

Option 2: LINQ Method Syntax #

If you prefer method chaining, here's the equivalent implementation:

var query = db.Companies
    // First left join with CompanyBDetails
    .GroupJoin(db.CompanyBDetails,
        company => company.Id,
        bDetail => bDetail.CompanyId,
        (company, bDetails) => new { Company = company, BDetails = bDetails })
    .SelectMany(x => x.BDetails.DefaultIfEmpty(),
        (x, bDetail) => new { x.Company, BDetail = bDetail })
    // Second left join with CompanyFDetails
    .GroupJoin(db.CompanyFDetails,
        joinResult => joinResult.BDetail?.Id,
        fDetail => fDetail.BankId,
        (joinResult, fDetails) => new { joinResult.Company, joinResult.BDetail, FDetails = fDetails })
    .SelectMany(x => x.FDetails.DefaultIfEmpty(),
        (x, fDetail) => new { x.Company.Name, x.BDetail, x.FDetail = fDetail })
    // Group by company name
    .GroupBy(item => item.Name)
    // Project the final counts
    .Select(group => new {
        CompanyName = group.Key,
        Total1 = group.Count(item => item.BDetail != null),
        Total2 = group.Count(item => item.FDetail != null)
    });

• GroupJoin + SelectMany is the standard way to implement left joins in method syntax.
• We use null checks here too to match SQL's behavior of ignoring null records in Count.

Bonus: Using Entity Framework Navigation Properties #

If your entities have configured navigation properties (e.g., Company has an ICollection<CompanyBDetails> called BDetails, and CompanyBDetails has an ICollection<CompanyFDetails> called FDetails), you can simplify this drastically:

var query = from c in db.Companies
            select new {
                CompanyName = c.Name,
                Total1 = c.BDetails.Count(), // Automatically counts related BDetails
                Total2 = c.BDetails.SelectMany(b => b.FDetails).Count() // Counts all related FDetails via BDetails
            };

This is cleaner because Entity Framework handles the joins behind the scenes using the navigation relationships you've defined.

内容的提问来源于stack exchange，提问作者Ajay Prajapati