整理一下Entity Framework的查询
Entity Framework是个好东西，虽然没有Hibernate功能强大，但使用更简便。今天整理一下常见SQL如何用EF来表达，Func形式和Linq形式都会列出来（本人更喜欢Func形式）。
1、简单查询：
SQL：
SELECT * FROM [Clients] WHERE Type=1 AND Deleted=0 ORDER BY ID
EF：
//Func形式
var clients = ctx.Clients.Where(c => c.Type == 1 && c.Deleted == 0)
.OrderBy(c => c.ID)
.ToList();
//Linq形式
var clients = from c in ctx.Clients
where c.Type == 1 && c.Deleted==0
orderby c.ID
select c;
2、查询部分字段：
SQL：
SELECT ID,Name FROM [Clients] WHERE Status=1
EF：
//Func形式
var clients = ctx.Clients.Where(c => c.Status == 1)
.Select(c => new { c.ID, Name = c.ComputerName })
.ToList();
//Linq形式
var clients = from c in ctx.Clients
where c.Status == 1
select new { c.ID, Name = c.ComputerName }; ：
3、查询单一记录：
SELECT * FROM [Clients] WHERE ID=100
//Func形式
var client = ctx.Clients.FirstOrDefault(c => c.ID == 100);
//Linq形式
var client = (from c in ctx.Clients
where c.ID = 100
select c).FirstOrDefault();
4、LEFT JOIN 连接查询
SELECT c.ID ,
c.ComputerName ,
g.Name GroupName
FROM [Clients] c
LEFT JOIN [Groups] g ON c.GroupID = g.ID
WHERE c.Status = 1
//Func形式
var clients = ctx.Clients.Where(c => c.Status == 1)
.Select(c => new
{
c.ID,
c.ComputerName,
GroupName = ctx.Groups.FirstOrDefault(g => g.ID == c.GroupID).Name
})
.ToList();
//Linq形式
var clients = from c in ctx.Clients
where c.Status == 1
select new
{
c.ID,
c.ComputerName,
GroupName = (from g in ctx.Groups
where g.ID == c.GroupID
select g.Name).FirstOrDefault()
};
5、INNER JOIN 连接查询：
SQL：
SELECT c.ID ,
c.ComputerName ,
g.Name GroupName
FROM [Clients] c
INNER JOIN [Groups] g ON c.GroupID = g.ID
WHERE c.Status = 1
ORDER BY g.Name
EF：
//Func形式
var clients = ctx.Clients.Where(c => c.Status == 1)
.Join(ctx.Group, c => c.GroupID, g => g.ID, (c,g) =>
{
c.ID,
c.ComputerName,
GroupName = g.Name
})
.OrderBy(item => item.GroupName)
.ToList();
//Linq形式1
var clients = from c in ctx.Clients
from g in ctx.Groups
where c.GroupID == g.ID
orderby g.Name
select new
{
c.ID,
c.ComputerName,
GroupName = g.Name
};
//Linq形式2
var clients = from c in ctx.Clients
where c.Status == 1
join g in ctx.Group
on c.GroupID equals g.ID into result
from r in result
order by r.Name
select new
{
c.ID,
c.ComputerName,
GroupName = r.Name
};
6、分页
SQL：
-- 方案1
SELECT TOP 10
*
FROM [Clients]
WHERE Status = 1
AND ID NOT IN ( SELECT TOP 20
ID
FROM [Clients]
WHERE Status = 1
ORDER BY ComputerName )
ORDER BY ComputerName
--方案2
SELECT *
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY ComputerName ) AS RowNo
FROM [Clients]
WHERE Status = 1
) t
WHERE RowNo >= 20
AND RowNo < 30
EF：
//Func形式
var clients = ctx.Clients.Where(c => c.Status=1)
.OrderBy(c => c.ComputerName)
.Skip(20)
.Take(10)
.ToList();
//Linq形式
var clients = (from c in ctx.Clients
orderby c.ComputerName
select c).Skip(20).Take(10);
7、分组统计：
SQL：
SELECT Status ,
COUNT(*) AS Cnt
FROM [Clients]
GROUP BY Status
ORDER BY COUNT(*) DESC
EF：
//Func形式
var result = ctx.Clients.GroupBy(c => c.Status)
.Select(s => new
{
Status = s.Key,
Cnt = s.Count()
})
.OrderByDescending(r => r.Cnt);
//Linq形式
var result = from c in ctx.Clients
group c by c.Status into r
orderby r.Count() descending
select new
{
Status = r.Key,
Cnt = r.Count()
};
