Linq中的group by多表多字段，Sum求和

 //Line to Sql 写法
 var data = (from a in Items
             group a by new { a.GroupId, a.Id } into b
             //orderby new ComparerItem() { GroupId = b.Key.GroupId, Id = b.Key.Id } descending
　　　　      .where(o => o.Id>1000)
             select new
              {
                 GroupId = b.Key.GroupId,
                 Id = b.Key.Id,
                 Count = b.Sum(c => c.Count),
                 Weight = b.Sum(c => c.Weight)
               }).OrderBy(t => t.GroupId).ThenBy(t => t.Id);

items 是一个包含4个字段(GroupId, Id, Count, Weight)的list.

效果，按GroupId,Id 分组 ，并统计Count字段和Weight字段

//labmda 写法
       
var data = items.GroupBy( t=> t.GroupBy,t=> t.Id)
        .where( f=> f.Id>1000)
        .Select( g=> new 
            {
                GroupId = g.GroupId,
                Id = g.Id,
                Count = g.Count( ),
                SumWeight = g.Sum( x=>x.Weight)
            }
        );

 

//多表写法

from a in TableA  
    join b in TableB on a.Id equals b.aId  
    where ((b.Type == 1 || b.Type == 2 || b.Type == 5) && b.State == 1)  
    group new { a.Id, b.Name,b,CreateDate } by new { a.Id, b.Name } into g  
    select (new Class1 { Id = g.Key.Id, Name = g.Key.Name ?? "" });  
  
class Class1  
{  
    public int Id { get; set; }  
    publid string Name { get; set; }  
}

 

from c in Customers
    join p in Purchases
    on c.ID equals p.CustomerID
    group p.Price by new
    {
    p.Date.Year,
    c.Name
    }
    into salesByYear
        orderby salesByYear.Key.Year descending
    select new
        {
        TotalPrice= salesByYear.Sum(),
        Year=salesByYear.Key.Year,
        Name=salesByYear.Key.Name
    }