使用LINQ创建具有计数的UNIQUE列
本文关键字:UNIQUE LINQ 创建 使用 | 更新日期: 2023-09-27 18:20:35
我有一个包含以下列的表
Id
Address
City
Date
maxSeat
StateId [Foreign key with table State with columns Id,Name]
我想写一个LINQ查询来获得唯一StateId的列表及其计数
例如
状态1 5行
状态2 3行
State3 1行
State4 5行
List<int> uniqStates = dbContext.conflocations.Select(item => item.StateId)
.Distinct().ToList();
这只返回stateId的唯一列表。如何使用LINQ获得关联计数和州名称?
您需要GroupBy:-
var uniqStates = dbContext.conflocations.GroupBy(item => item.StateId)
.Select(x => new
{
StateId = x.Key,
Count = x.Count()
});
您可以使用GroupBy
方法:
var uniqStates = dbContext.conflocations.GroupBy(item => item.StateId).Select(g=>new {StateId=g.Key,Count=g.Count()}).ToList();
或者使用查询语法,你也可以做:
var uniqStates= from conf in dbContext.conflocations
group conf by conf.StateId into g
select new {StateId=g.Key,
Count=g.Count()
};
现在要获取州的名称,如果您的Conflocation
实体中有一个类型为State
的导航属性,那么您可以执行以下操作:
var uniqStates= from conf in dbContext.conflocations
group conf by conf.StateId into g
select new {StateId=g.Key,
Name=g.FirstOrDefault().State.Name
Count=g.Count()
};
更新
如果您的StateWiseVenues
类具有与此查询投影结果的匿名类型相同的属性类型,则可以执行以下操作:
var uniqStates= from conf in dbContext.conflocations
group conf by conf.StateId into g
select new StateWiseVenues {StateId=g.Key,
Name=g.FirstOrDefault().State.Name
Count=g.Count()
};
if(uniqStates !=null)
{
state_venues = uniqStates.ToList();
}
您需要对两个表进行排序:
using( var dbContext=...)
{
var results = from c in dbContext.conflocations
from s in dbContext.States.Where(x=>x.Id = c.StateId).DefaultIfEmpty()
group new {c, s} by s.StateId into grp
select new {
StateId = grp.Key,
StateName= grp.Max(x=>x.s.Name),
Count = grp.Count()
};
...
}