How to write linq query for this SQL query?

Question

How to write linq query for this SQL Query

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

Answer 1

So you have a table of Companies, where every Company has zero or more BDetails, every BDetail belongs to exactly one Company, namely the Company that the foreign key CompanyId refers to: a straightforward one-to-many relation.

Similarly, every BDetail has zero or more FDetails, and every FDetail belongs to exactly one BDetail, namely the BDetail that the foreign key BankId refers to.

For every company you want the Name of the Company and the number of BDetails of this company. It seems to me that you also want the total number of FDetails of all BDetails of this Company.

var result = dbContext.Companies.Select(company => new
{
    Name = company.Name,

    BDetailIds = dbContext.BDetails
        .Where(bDetail => bDetail.CompanyId == company.Id)
        .Select(bDetail => bDetail.Id),
})

Intermediate result: for every Company, you have created one object, that holds the name of the Company, and the Ids of all its BDetails

Continuing the Linq: calculate the totals:

.Select(company => new
{
    Name = company.Name,

    // Total1 is the number of BDetails of this Company
    Total1 = company.BDetailIds.Count(),

    // Total2 is the number of FDetails of all BDetails of this Company
    // whenever you need the sub-items of a sequence of items, use SelectMany
    Total2 = company.BDetailIds
        .SelectMany(bDetailId => dbContext.FDetails.Where(fDetail => fDetail.BankId == bDetailId))
        .Count();
});

If you want more properties than just the totals:

var result = dbContext.Companies.Select(company => new
{
    // Select the company properties that you plan to use:
    Id = company.Id,
    Name = company.Name,
    ...

    BDetail = dbContext.BDetails
        .Where(bDetail => bDetail.CompanyId == company.Id)
        .Select(bDetail => new
        {
             // Select only the bDetail properties of the company that you plan to use
             Id = bDetail.Id,
             ...

             // not needed, you know the value:
             // CompanyId = bDetail.CompanyId,

             FDetails = dbContext.FDetails
                 .Where(fDetail => fDetail.BankId == bDetail.Id)
                 .Select(fDetail => new
                 {
                      // you know the drill by now: only the properties that you plan to use
                      Id = fDetail.Id,
                      ...
                 })
                 .ToList(),
        })
        .ToList(),
});