首页 新闻 搜索 专区 学院

ef左联接分组聚合汇总出现使用Count(1)导致汇总数据异常

0
悬赏园豆:30 [待解决问题]

var query =
from c in A
join rl in B on c.Id equals rl.ClassId into rlTemp
from rlItem in rlTemp.DefaultIfEmpty()
group rlItem.Id by new
{
c.Id,
c.Name,
c.CreateTime,
c.Remark
} into gb
select new TeachingClassStruct
{
Id = gb.Key.Id,
CreateTime = gb.Key.CreateTime,
Name = gb.Key.Name,
Remark = gb.Key.Remark,
Count = gb.Count()
};
以上linq执行结果的时候,如果出现B中没有关联A的数据情况下,最终的Count为1,查看转成的sql为
SELECT
1 AS [C1],
[GroupBy1].[K1] AS [Id],
[GroupBy1].[K2] AS [CreateTime],
[GroupBy1].[K3] AS [Name],
[GroupBy1].[K4] AS [Remark],
[GroupBy1].[A1] AS [C2]
FROM (
SELECT
[Extent1].[Id] AS [K1],
[Extent1].[CreateTime] AS [K2],
[Extent1].[Name] AS [K3],
[Extent1].[Remark] AS [K4],
COUNT(1) AS [A1]
FROM [dbo].[A] AS [Extent1]
LEFT OUTER JOIN [dbo].[B] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ClassId]
WHERE ([Extent1].[IsDelete] <> 1) AND ([Extent1].[Owner] = '1019062600000109')
GROUP BY [Extent1].[Id], [Extent1].[CreateTime], [Extent1].[Name], [Extent1].[Remark]
) AS [GroupBy1]
问题处在Count(1)这个地方,如果修改linq才可以改成count(b.id)

问题补充:

如果在count中添加判断非空的情况会出现嵌套好多层并且distinct都有

simba_shi的主页 simba_shi | 初学一级 | 园豆:141
提问于:2021-08-31 16:10
< >
分享
所有回答(1)
0

Count = gb.Count()
Count = gb.Count(a=>a.xxxx)

diudiu1 | 园豆:610 (小虾三级) | 2021-09-02 18:24

这样改了之后虽然结果是想要的,但是生成的sql语句并不是理想的count(xxx),而是一个带有嵌套很复杂的sql,其中还有distinct

支持(0) 反对(0) simba_shi | 园豆:141 (初学一级) | 2021-09-22 17:24
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册