如何创建 Linq2Sql 查询,该查询将对链接表中的记录进行分组并计算 2 个计数字段
本文关键字:查询 记录 数字段 计算 链接表 创建 何创建 Linq2Sql | 更新日期: 2023-09-27 18:31:24
在这里,我找到了如何使用 Linq2Sql 连接表并计算链接记录的数量 LINQ - 左联接、分组依据和计数
我已经实现了它,它对我来说工作正常:以下表达式
var v = from c in db.GetTable<Country>()
join t0 in db.GetTable<Team>() on c.Id equals t0.CountryId into t1
from team in t1.DefaultIfEmpty()
group team by c.Id into teamsGrouped
select new CountryTeamsInfo
{
CountryId = teamsGrouped.Key,
TeamsTotal = teamsGrouped.Count(),
// TeamsWithoutOwnerFree = teamsGrouped.Count(t => t.OwnerId==0)
}
;
List<CountryTeamsInfo> res = v.ToList();
生成以下查询:
SELECT c.Id, Count(*) as c1
FROM countries c
LEFT JOIN teams t1 ON c.Id = t1.Country
GROUP BY c.Id
实际上,我还需要计算那些 OwnerId 字段不等于 0 的链接器记录。
看起来我应该在linq表达式中取消注释该行(TeamsWithoutOwnerFree = teamsGrouped.Count(t => t.OwnerId==0)),但这不起作用,尝试执行会导致错误:
字典中不存在给定的键
查询不会进入 SQL 日志文件,我无法在调试器中检查它。
从"团队"表中计算符合其他条件的行的正确方法应该是什么。
附言:如果重要的话,我使用 C# 4.0、MySql 5.1 和 BLToolkit 4.1
也许尝试使用 GroupJoin()
进行查询:
var result = db.GetTable<Country>()
.GroupJoin(db.GetTable<Team>(),
c => c.Id,
t => t.CountryId,
(country, teams) => new
{
CountryId = country.Id,
TeamsTotal = teams.Count(),
TeamsWithoutOwnerFree = teams.Count(t => t.OwnerId == 0)
})
.ToList();
由于RePierre的帮助,我能够找到正确的查询:
var v = db.GetTable<Country>().Where(country => country.Allowed)
.GroupJoin(
db.GetTable<Team>(),
country => country.Id,
team => team.CountryId,
(country, teams) => new CountryTeamsInfo
{
CountryId = country.Id,
TeamsTotal = teams.Count(),
TeamsWithoutOwnerFree = teams.Count(t => t.OwnerId != 0),
}
).GroupJoin(
db.GetTable<Team>().Where(te=>te.OwnerId==0),
cti => cti.CountryId,
team => team.CountryId,
(cti, teams) => new CountryTeamsInfo
{
CountryId = cti.CountryId,
TeamsTotal = cti.TeamsTotal,
TeamsWithoutOwnerFree = teams.Count(t => t.OwnerId != 0),
}
)
;
我担心的是它使用 2 个子查询...将尝试优化它。任何想法都会很棒
附言:实际上,生成的SQL查询看起来也很丑陋:
SELECT
cti.Id as Id1,
cti.c1 as c11,
(
SELECT
Count(*)
FROM
teams te
WHERE
cti.Id = te.Country AND te.User = 0
) as c2
FROM
(
SELECT
country.Id,
(
SELECT
Count(*)
FROM
teams t1
WHERE
country.Id = t1.Country
) as c1
FROM
countries country
WHERE
country.allow
以下是在我的环境中工作的查询:
from c in db.GetTable<Country>()
where c.Allowed
select new CountryTeamsInfo
{
CountryId = c.Id,
TeamsTotal = db.GetTable<Team>().Count(t => t.CountryId == c.Id && t.Allowed),
TeamsHasOwner = db.GetTable<Team>().Count(t => t.CountryId == c.Id && t.Allowed && t.OwnerId != 0),
}
不是最好的解决方案(我需要在每个子查询中重复团队选择标准t.Allow),但仍然运行良好,生成的SQL很好。