首页 > 代码库 > 整理一下Entity Framework的查询

整理一下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()

};

整理一下Entity Framework的查询