Linq基本查询

1、基本查询
   from g in TblGroups
   select g

2、带条件查询
   from g in TblGroups
   where g.Name.Contains("组")
   select g

3、查询显示不同的列
   from g in TblGroups
   select new
   {
      g.Name,
      g.Description
   }
   或
   from g in TblGroups
   select new
   {
      姓名 = g.Name,
      描述 = g.Description
   }

4、排序:orderby
   from g in TblGroups

   orderby g.Name descending //ascending

   orderby g.字段名1 descending //ascending

   orderby g.字段名2 descending //ascending

   select g

    或

    Pdt_Traces
   .OrderBy (p => p.BatchID)
   .ThenBy(p => p.CreateDate)
   .Where(p=>p.Status != "D")

5、分组
   from g in TblGroups
   group g by g.Name into gg

   select gg

   多条件分组

   var groupData = from g in result.Data
                   group g by new { g.EmployeeName, g.RouteName } into gg
                   select new
                   {
                       EmployeeName = gg.Key.EmployeeName,
                       RouteName = gg.Key.RouteName,
                       TotalCustomer = gg.Sum(c => Convert.ToInt32(c.TotalCustomer)),
                       TotalBF = gg.Sum(c => Convert.ToInt32(c.TotalBF)),
                       TotalPS = gg.Sum(c => Convert.ToInt32(c.TotalPS)),
                       BFCGL = Division(gg.Sum(c => Convert.ToInt32(c.TotalBF)), gg.Sum(c => Convert.ToInt32(c.TotalCustomer))),
                       BFCJL = Division(gg.Sum(c => Convert.ToInt32(c.TotalPS)), gg.Sum(c => Convert.ToInt32(c.TotalBF))),
                       PSL = Division(gg.Sum(c => Convert.ToInt32(c.TotalPS)), gg.Sum(c => Convert.ToInt32(c.TotalCustomer))),
                   };
   RecordTotal = groupData.Count();
   topRecordTotal = (pageIndex - 1 < 0 ? 0 : pageIndex - 1) * pageSize;
   groupData = groupData.Skip(topRecordTotal).Take(pageSize).ToList();

6、分组联合查询
   from m in TblMembers
   group m by m.GroupId into VarGroup
   select new
   {
会员组ID = VarGroup.Key,
总数 = VarGroup.Count()
   } into aaa

   from g in TblGroups
   where aaa.会员组ID == g.Id
   select new
   {
aaa.会员组ID,
g.Id,
g.Name,
g.Description,
aaa.总数
   }

7、子查询
   from m in TblMembers
   select new
   {
      m.FirstName,
      m.LastName,
      m.Email,
      m.GroupId,
      GroupName = (from g in TblGroups where g.Id == m.GroupId select g.Name),
   }
   或
   from g in TblGroups
   select new
   {
      g.Id,
      g.Name,
      g.Description,
      总数 = (from m in TblMembers where g.Id == m.GroupId select m).Count(),
   }
8、Count
  (from m in TblMembers
  where m.Email == "123@qq.com"
  select m).Count()
  或
  (from m in TblMembers
  select m).Count(a=>a.Email == "123@qq.com")
  或
  (from c in Campaigns
  select c).Count(a=>a.Age > 100)

9、联合查询
   from g in TblGroups
   join m in TblMembers
   on g.Id equals m.GroupId into VarGroup

   select VarGroup

   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)
}

10、返回前N条数据：Take()
  (from c in Campaigns
  orderby c.ID descending   //ascending-descending
  select c).Take(10)

11、取出除去满足条件的剩下的记录；跳过序列中指定数量的元素，然后返回剩余的元素：skip()，相当于分页
  (from c in Campaigns
  orderby c.ID ascending         //ascending-descending
  select c).Skip(4*10).Take(10)

12、先分页再按价格排序
   (from c in Campaigns
   orderby c.ID ascending                             //ascending-descending
   select c).Skip(4*10).Take(10)
   .OrderBy(o => o.Price)                               //OrderByDescending-OrderBy
   或
   Campaigns
      .OrderBy (c => c.ID)
      .Skip (20)
      .Take (10)
      .OrderByDescending (c => c.Price)     //OrderByDescending-OrderBy

13、时间强制转换、where in 查询
   from p in Pdt_Orders
   where p.UserName == "admin" && new int[] {2,4}.Contains(p.AreaID) 
   && p.CreateDate >= Convert.ToDateTime("2012-1-1") && p.CreateDate <= Convert.ToDateTime("2013-12-31")
   select p

   ---------------------------------------------------------------------------------------------------------------------------

string trueValues = "1,2,3,4,5,6,8,9,10,".TrimEnd(',');
string[] strArray = trueValues.Split(',');
int[] intArray = trueValues.Split(',').Select(v => Convert.ToInt32(v)).ToArray();
long[] longArray = trueValues.Split(',').Select(v => Convert.ToInt64(v)).ToArray();

var query1 = dbContent.SymptomPicService.Where(w => !strArray.Contains(w.PicPath)).ToList();
var query2 = dbContent.SymptomPicService.Where(w => intArray.Contains(w.ID)).ToList();
var query3 = dbContent.SymptomPicService.Where(w => longArray.Contains(w.ID)).ToList();

===============================================

高级查询

用到分页，join联合查询，where条件追加空值判断，空值判断，自定义字段名称

public object GetPageListOrder(int pageIndex, int pageNum, string sidx, string sord, int[] areaID, string name, string beginDate, string endDate, string delivery, string review)
{
    --pageIndex;
    DateTime begin = DateTime.Now;
    DateTime end = DateTime.Now;
    if (beginDate != "" && endDate != "")
    {
        begin = Convert.ToDateTime(beginDate);
        end = Convert.ToDateTime(endDate);
    }
    int topNum = 0;
    if (pageIndex > 0)
        topNum = pageIndex * pageNum;
    else
        topNum = 0;
    var query = from op in db_C56.ProductionOrders
                 join a in db_C56.Area on new { AreaID = op.AreaID } equals new { AreaID = a.ID } into a_join
                 from a in a_join.DefaultIfEmpty()
                 join p in db_C56.Productions on op.ProductionID equals p.ProductionID into p_join
                 from p in p_join.DefaultIfEmpty()
                 where op.Status != "D" && (areaID).Contains(op.AreaID)
                 orderby op.CreateDate descending
                 select new
                 {
                     op.OrderID,
                     op.UserName,
                     Name = a.Name,
                     Column1 = (p.ProductionName ?? "无此产品"),
                     op.Qty,
                     op.DeliveryType,
                     op.CreateDate,
                     op.Review
                 };

    if (review != "")
    {
        query = query.Where(temp => temp.Review == review);
    }
    if (name != "")
    {
        query = query.Where(temp => temp.UserName.Contains(name));
    }
    if (beginDate != "" && endDate != "")
    {
        query = query.Where(temp => temp.CreateDate >= begin && temp.CreateDate <= end);
    }
    if (delivery != "")
    {
        query = query.Where(temp => temp.DeliveryType == delivery);
    }
    return query.Skip(topNum).Take(pageNum).ToList().Select(s => new
    {
        OrderID = s.OrderID,
        UserName = s.UserName,
        Status = s.Name,
        Qty = s.Qty,
        Review = (s.Review == "Y") ? "已查看" : "未查看",
        CreateBy = s.CreateDate.ToString("yyyy-MM-dd HH:mm:ss"),
        ProductionName = (s.Column1),
        ModifiedBy = s.DeliveryType,
    });
}