Linq基本查询

来源：互联网发布：php pack("h*") 编辑：程序博客网时间：2024/05/17 21:50

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

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