sql - 如何为此 SQL 查询编写 linq 查询?
问题描述
如何为此 SQL 查询编写 linq 查询
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
解决方案
所以你有一张表Companies
,其中每个Company
都有零个或多个BDetails
,每个 BDetail 恰好属于一个公司,即外键CompanyId
所指的公司:一个简单的一对多关系。
同样,everyBDetail
有零个或多个FDetails
,并且everyFDetail
恰好属于一个BDetail
,即BDetail
外键BankId
所指的 。
对于您想要的每家公司,该公司的名称和该公司的 BDetails 数量。在我看来,您还想要该公司所有 BDetails 的 FDetails 总数。
var result = dbContext.Companies.Select(company => new
{
Name = company.Name,
BDetailIds = dbContext.BDetails
.Where(bDetail => bDetail.CompanyId == company.Id)
.Select(bDetail => bDetail.Id),
})
中间结果:对于每个公司,您都创建了一个对象,该对象包含公司的名称,以及其所有 BDetails 的 Id
继续 Linq:计算总数:
.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();
});
如果您想要更多的属性而不仅仅是总数:
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(),
});
推荐阅读
- tornadofx - Tornadofx Tableview 行数
- sql - WHERE 子句中的 SQL IF 语句
- spring - Spring stomp over websocket SubscribeMapping 不起作用
- react-native - 带有反应原生导航的优步应用导航
- c# - 以 c# 形式创建 Windows 10 透明效果
- google-app-engine - 如何使用 gcloud 推送最新版本的 Web 应用程序以使其上线
- javascript - 从页面源代码中提取特定变量的内容
- go - netlink 序列号乱序
- javascript - Fabrics - 如何在鼠标悬停之前呈现免费的绘图内容
- nosql - Arangodb 远程复制