Entity Framework是个好东西，虽然没有Hibernate功能强大，但使用更简便。今天整理一下常见SQL如何用EF来表达，Func形式和Linq形式都会列出来（本人更喜欢Func形式）。
1、简单查询：
SQL：
SELECT * FROM [Clients] WHERE Type=1 AND Deleted=0 ORDERBY 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,NameFROM [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、查询单一记录：
SQL：
SELECT * FROM [Clients] WHERE ID=100
EF：
//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 连接查询
SQL：
SELECT c.ID,c.ComputerName,g.Name GroupName
FROM [Clients] c
LEFTJOIN [Groups] g
ON c.GroupID = g.ID
WHERE c.Status=1
EF：
//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()
};
SQL：
SELECT c.ID,c.ComputerName,g.Name GroupName
FROM [Clients] c
INNERJOIN [Groups] g
ON c.GroupID = g.ID
WHERE c.Status=1
ORDERBY 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
SELECTTOP 10 * FROM [Clients] WHERE Status=1
AND ID NOTIN
(
SELECTTOP 20 ID FROM [Clients] WHERE Status=1
ORDERBY ComputerName
)
ORDERBY ComputerName
--方案2
SELECT * FROM
(
SELECT *, ROW_NUMBER() OVER(ORDERBY 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]
GROUPBY Status
ORDERBYCOUNT(*) 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()
};
未完待续……
本文出自 “” 博客，请务必保留此出处