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
14、StartsWith、EndWith、Contains
UserName.StartsWith("x") => select * from table where c.UserName like 'x%'
UserName.EndWith("x") => select * from table where c.UserName like '%x'
UserName.Contains("x") => select * from table where c.UserName like '%x%'
---------------------------------------------------------------------------------------------------------------------------
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,
});
}