组合了两个linq查询- group和join
本文关键字:查询 group join linq 两个 组合 | 更新日期: 2023-09-27 18:13:13
我的数据库中有2个单独的表用于客户及其相关种族。客户表保存种族的外键Id。我想创建一个Linq查询,显示用于报告目的的每个种族的总数。例如…
+------------+------------------+
| Ethnicity | Customer Count |
+------------+------------------+
| White | 100 |
+------------+------------------+
| Black | 50 |
+------------+------------------+
| Chinese | 20 |
+------------+------------------+ etc...
到目前为止,我有以下两个LINQ查询:
var customers = repository.GetAll<Customer>();
var ethnicities = repository.GetAll<Ethnicity>();
var query1 = customers.GroupBy(c => c.EthnicityId).Select(g => new { Key = g.Key, Count = g.Count() });
查询1显示总数,但带有种族ID而不是文本(ethnitytype)。
var query2 = from c in customers
join e in ethnicities on c.EthnicityId equals e.Id
where (c.EthnicityId == e.Id)
select new { Ethnicity = e.EthnicityType, Count = ??? };
查询2连接两个表,但如何分组,使我得到总数,而不是单独的记录?希望这是有意义的,感谢帮助。
var query2 = query1.Join(ethnicities, x => x.Key,
y => EthnicityId,
(x, y) => new { Ethnicity = y.EthnicityType,
Count = x.Count });
有很多方法可以做你想做的事情,但如果种族数量很少,你可以简单地在客户端创建一个查找表,并使用它将ID映射到描述性名称:
var customers = repository.GetAll<Customer>();
var ethnicities = repository.GetAll<Ethnicity>().ToDictionary(e => e.Id);
var query1 = customers
.GroupBy(c => c.EthnicityId)
.Select(g => new { Key = ethnicities[g.Key], Count = g.Count() };
ToDictionary(e => e.Id)
用于创建将ID映射到名称的字典,然后使用该字典查找使用ethnicities[g.Key]
的名称。
我想这可能行得通:
var query2 =
from e in ethnicities
join c in customers on e.Id equals c.EnthnicityId
into g
where g.Any()
select new { Ethnicity = g.First(), Count = g.Count() };