LINQ，EF联合查询join GroupBy分组

public object GetListAdmin()
        {
            //return db_C56.Admins
            //   .Where(a => a.Status != "D").ToList();

            var query1 = db_C56.Admins.Join(db_C56.Area, a => a.AreaID, ar => ar.ID, (a, ar) => new
            {
                userName = a.UserName,
                pwd = a.Password,
                dName = a.DisplayName,
                areaId = a.AreaID,
                hasNode = a.HasNode,
                roleName = a.RoleName,
                status = a.Status,
                areaName = ar.Name
            });

            var query = from a in db_C56.Admins
                        join ar in db_C56.Area
                        on a.AreaID equals ar.ID
                        where a.Status != "D"
                        select new
                        {
                            userName = a.UserName,
                            pwd = a.Password,
                            dName = a.DisplayName,
                            areaId = a.AreaID,
                            hasNode = a.HasNode,
                            roleName = a.RoleName,
                            status = a.Status,
                            areaName = ar.Name
                        };
            return query.ToList().Select(C => new Admin
            {
                UserName = C.userName,
                Password = C.pwd,
                DisplayName = C.dName,
                AreaID = C.areaId,
                AreaPath = C.areaName,
                HasNode = C.hasNode,
                RoleName = C.roleName,
                Status = C.status,
            });
        }

from v in Pdt_Versions
join t in Tb_TypeDics 
on v.TypeName equals t.TypeName into ignored
from i in ignored.DefaultIfEmpty()
where v.Status != "D"
select new 
{
ID = v.ID,
VersionName = v.VersionName,
VersionCode = v.VersionCode,
DownloadName = v.DownloadName,
DownloadURL = v.DownloadURL,
VType = v.VType,
TypeName = v.TypeName,
DisplyTypeName = i.DisplyTypeName,
}

Linq 多层嵌套查询 

 var query1 = from p in dbContent.PostService
              where p.post_type == "product" &&
                  (from ot1 in dbContent.OrderItemmetaService
                   where
                       (from ot2 in dbContent.OrderItemsService
                        where ot2.order_item_type == "line_item" &&
                            (from p1 in dbContent.PostService
                             where p1.post_type == "shop_order" && p1.post_author == userid && p1.post_status == "wc-completed"
                             select p1.ID).Contains(ot2.order_id)
                        select ot2.order_item_id).Contains(ot1.meta_id)
                   select ot1.meta_value).Contains(p.ID)
              select new
              {
                  id = p.ID,
                  name = p.post_title
              };

 var query2 = dbContent.PostService.Where(p =>
     p.post_type == "product" &&
     (dbContent.OrderItemmetaService.Where(ot1 =>
         (dbContent.OrderItemsService.Where(ot2 =>
             ot2.order_item_type == "line_item" && (dbContent.PostService.Where(p1 =>
                 p1.post_type == "shop_order" && p1.post_author == userid && p1.post_status == "wc-completed").Select(p1 => p1.ID).Contains(ot2.order_item_id))
                 ).Select(ot2 => ot2.order_item_id).Contains(ot1.meta_id))
                 ).Select(ot1 => ot1.meta_value).Contains(p.ID))
                 ).Select(p => new
                 {
                     id = p.ID,
                     name = p.post_title
                 }).ToList();

Left Join 查询 

from d in Doctors
join c in (
(from t in Commentaries where t.State != 'D' group t by new { t.DoctorID } into g 
select new {
DoctorID = (Int64?)g.Key.DoctorID,
Total = (Int32?)g.Sum(p => p.Rating),
Evaluate = (System.Double?)g.Average(p => p.Rating)
})) on new { UserID = d.UserID } equals new { UserID = (Int64)c.DoctorID }into a_join
from p in a_join.DefaultIfEmpty()
select new {
  d.ID,
  UserID = (Int64?)d.UserID,
  d.Name,
  Evaluate = ((int?)p.Evaluate ?? (int?)0)
}

Lambda表达式 

Doctors
   .GroupJoin (
      Commentaries
         .Where (t => ((Int32)(t.State) != 68))
         .GroupBy (
            t => 
               new  
               {
                  DoctorID = t.DoctorID
               }
         )
         .Select (
            g => 
               new  
               {
                  DoctorID = (Int64?)(g.Key.DoctorID), 
                  Total = (Int32?)(g.Sum (p => p.Rating)), 
                  Evaluate = (Double?)(g.Average (p => p.Rating))
               }
         ), 
      d => 
         new  
         {
            UserID = d.UserID
         }, 
      c => 
         new  
         {
            UserID = (Int64)(c.DoctorID)
         }, 
      (d, a_join) => 
         new  
         {
            d = d, 
            a_join = a_join
         }
   )
   .SelectMany (
      temp0 => temp0.a_join.DefaultIfEmpty (), 
      (temp0, p) => 
         new  
         {
            ID = temp0.d.ID, 
            UserID = (Int64?)(temp0.d.UserID), 
            Name = temp0.d.Name, 
            Evaluate = ((Int32?)(p.Evaluate) ?? (Int32?)0)
         }
   )

==========================================================
​​​​​多个left join

from d in Doctors
join f in Functions on new { FunctionID = d.FunctionID } equals new { FunctionID = f.ID } into b_join
from f in b_join.DefaultIfEmpty()
join c in (
(from t in Commentaries where t.State != 'D' group t by new {t.DoctorID } into g
select new {
 DoctorID = (Int64?)g.Key.DoctorID,
 Total = (Int32?)g.Sum(p => p.Rating),
 Evaluate = (System.Double?)g.Average(p => p.Rating)
})) on new { UserID = d.UserID } equals new { UserID = (Int64)c.DoctorID } into a_join
from c in a_join.DefaultIfEmpty()
select new {
  d.ID,
  UserID = (Int64?)d.UserID,
  d.AvatarPic,
  d.Name,
  f.Title,
  f.ContentDescribe,
  Evaluate = ((int?)c.Evaluate ?? (int?)0)
}

Lambda表达式

Doctors
   .GroupJoin (
      Functions, 
      d => 
         new  
         {
            FunctionID = d.FunctionID
         }, 
      f => 
         new  
         {
            FunctionID = f.ID
         }, 
      (d, b_join) => 
         new  
         {
            d = d, 
            b_join = b_join
         }
   )
   .SelectMany (
      temp0 => temp0.b_join.DefaultIfEmpty (), 
      (temp0, f) => 
         new  
         {
            temp0 = temp0, 
            f = f
         }
   )
   .GroupJoin (
      Commentaries
         .Where (t => ((Int32)(t.State) != 68))
         .GroupBy (
            t => 
               new  
               {
                  DoctorID = t.DoctorID
               }
         )
         .Select (
            g => 
               new  
               {
                  DoctorID = (Int64?)(g.Key.DoctorID), 
                  Total = (Int32?)(g.Sum (p => p.Rating)), 
                  Evaluate = (Double?)(g.Average (p => p.Rating))
               }
         ), 
      temp1 => 
         new  
         {
            UserID = temp1.temp0.d.UserID
         }, 
      c => 
         new  
         {
            UserID = (Int64)(c.DoctorID)
         }, 
      (temp1, a_join) => 
         new  
         {
            temp1 = temp1, 
            a_join = a_join
         }
   )
   .SelectMany (
      temp2 => temp2.a_join.DefaultIfEmpty (), 
      (temp2, c) => 
         new  
         {
            ID = temp2.temp1.temp0.d.ID, 
            UserID = (Int64?)(temp2.temp1.temp0.d.UserID), 
            AvatarPic = temp2.temp1.temp0.d.AvatarPic, 
            Name = temp2.temp1.temp0.d.Name, 
            Title = temp2.temp1.f.Title, 
            ContentDescribe = temp2.temp1.f.ContentDescribe, 
            Evaluate = ((Int32?)(c.Evaluate) ?? (Int32?)0)
         }
   )

GroupBy分组

List<DrugDto> list = new List<DrugDto>();
list.Add(new DrugDto { drug_code = 1, drug_name = "a", price = 1 });
list.Add(new DrugDto { drug_code = 1, drug_name = "a", price = 1 });
list.Add(new DrugDto { drug_code = 2, drug_name = "b", price = 3 });
list.Add(new DrugDto { drug_code = 3, drug_name = "c", price = 4 });

var aa = list.GroupBy(t => new { code = t.drug_code })
     .Select(g => new
     {
         DrugID = (Int64?)(g.Key.code),
         Total = (Int32?)(g.Sum(p => p.price)),
         Count = g.Count(),
     }).ToList();


var bb = list.GroupBy(t => new { code = t.drug_name })
     .Select(g => new
     {
         DrugName = g.Key.code,
         Total = g.Sum(p => p.price),
         Count = g.Count(),
     }).ToList();