Linq to SQL 多表Group By

Posted on 2023-05-12 18:24 火冰·瓶阅读(73) 评论(0) 编辑收藏举报

var park = _context.ParkCountRecords
var area = _context.AreaInfos;            
 
                 var data = from p in park
                       join a in area
                       on p.ProCode equals a.Code
                       group p.ParkingNum by new   //ParkingNum是需要求和，求平均等的字段
                       {
                           p.ProCode,            //需要进行分组依据的字段
                           a.ShortName           //最后结果里需要取值的
                       } into g
                       select new ParkingItem
                       {
                           Name = g.Key.ShortName,
                           Code = g.Key.ProCode,
                           Num = g.Sum(),      //对group的字段进行求和
                       };

//上述Linq to SQL语句相当于下面的sql
SELECT  g.Num,g.ProCode,a.ShortName  
from
(
    SELECT sum(ParkingNum) as Num,ProCode 
    from ParkCountRecord 
    group by ProCode
) as g 
INNER JOIN  AreaInfo AS a
on g.ProCode = a.Code