-- EF(Entity Framework) Linq to Sql 多表join查询并对结果group by分组,之后Max,Sum,Count等操作
【官网】:https://docs.microsoft.com/en-us/ef/core/
应用场景
有时我们需要实现复杂一些的多表联合查询,并对结果分组。基础资源
ef框架
使用须知
需要注意企业开发框架及业务实际场景确认使用何种框架,何种写法。
配置步骤
【多表left join查询+where过滤+group by+count】
var query = from u in ctx.User
join t in ctx.UserSignTask
on u.id equals t.uid
into utlist
from ut in utlist.DefaultIfEmpty()
where u.Status==1
group u.id by u.id into GroupByUser
where GroupByUser.Count()<requiredNum
select GroupByUser.Key;
【多表left join查询+where过滤+group by+Sum】
var query = from a in db.AccountTable
join p in db.InOrOutProjectTable
on a.InOrOutProjectID equals p.ID
where p.ProjectType == false & a.Date >= dateStart & a.Date < dateEnd
group new { p.ProjectName, a.Amount } by p.ProjectName into g
select new { g.Key, totalExpense = g.Sum(x => x.Amount) };
【按产品类别分组查询其价格最高的产品: group by ,max】
var query =
from p in db.Products
group p by p.CategoryID into g
orderby g.Key
select new {
g.Key,
MostExpensiveProducts =
from p2 in g
where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
select p2
};
常见问题
快速入门
无