我有一个数据库表,其中的数据集包含多行数据,如下所示
ItemId Code StatusId
-------------------- ---------------------------------------------
62224 NC0860000 8
62225 NC0860000 8
62226 NC0860000 8
62227 NC0860200 5
62228 NC0860000 5
62229 NC0860000 5
62230 NC0860000 5
我想完成的是一个输出结果
NC0860000 8 3 (code, status, count)
NC0860000 5 3
我不完全了解EF中的分组方式。我可以使用以下查询获取键和单个组的数量:
var results = (from ssi in ctx.StageSubmitItems
join s in ctx.StageSubmissions on ssi.SubmissionId equals s.SubmissionId
where s.ContributorCode == contributorId
group ssi.SubmitItemId by ssi.AgencyCode into g
select new {AgencyCode = g.Key, Count = g.Count() }).ToList();
但是我无法弄清楚如何按代码分组,然后按StatusId分组,然后按状态对行总数进行计数。
对于在哪里可以看到如何完成此操作或查询中我做错了什么,我将不胜感激。
您可以按如下方式按新的anon类分组:
// I created a Foo class to show this working
var fooList = new List<Foo> {
new Foo { ItemId = 62224, Code = "NC0860000", StatusId = 8 },
new Foo { ItemId = 62225, Code = "NC0860000", StatusId = 8 },
new Foo { ItemId = 62226, Code = "NC0860000", StatusId = 8 },
new Foo { ItemId = 62227, Code = "NC0860200", StatusId = 5 },
new Foo { ItemId = 62228, Code = "NC0860000", StatusId = 5 },
new Foo { ItemId = 62229, Code = "NC0860000", StatusId = 5 },
new Foo { ItemId = 62230, Code = "NC0860000", StatusId = 5 },
};
var results = (from ssi in fooList
// here I choose each field I want to group by
group ssi by new { ssi.Code, ssi.StatusId } into g
select new { AgencyCode = g.Key.Code, Status = g.Key.StatusId, Count = g.Count() }
).ToList();
// LINQPad output command
results.Dump();
使用提供的数据,以下是输出:
AgencyCode Status Count
NC0860000 8 3
NC0860200 5 1
NC0860000 5 3
我猜“ NC0860200”是一个错误,但它存在于您的示例数据中,因此我将其包括在内。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句