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、查询单一记录：

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 
LEFT JOIN [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()
            };

5、INNER JOIN 连接查询：

SQL：

1. SELECT c.ID,c.ComputerName,g.Name GroupName 
2. FROM [Clients] c
3. INNER JOIN [Groups] g
4. ON c.GroupID = g.ID
5. WHERE c.Status=1
6. ORDER BY g.Name

EF：

1. //Func形式
2. var clients = ctx.Clients.Where(c => c.Status == 1)
3.             .Join(ctx.Group, c => c.GroupID, g => g.ID, (c,g) => 
4.             {
5.                 c.ID,
6.                 c.ComputerName,
7.                 GroupName = g.Name
8.             })
9.             .OrderBy(item => item.GroupName)
10.             .ToList();
11.  
12.  
13. //Linq形式1
14. var clients = from c in ctx.Clients
15.             from g in ctx.Groups
16.             where c.GroupID == g.ID
17.             orderby g.Name
18.             select new
19.             {
20.                 c.ID,
21.                 c.ComputerName,
22.                 GroupName = g.Name
23.             };
24.  
25. //Linq形式2
26. var clients = from c in ctx.Clients
27.             where c.Status == 1
28.             join g in ctx.Group
29.             on c.GroupID equals g.ID into result
30.             from r in result
31.             order by r.Name
32.             select new
33.             {
34.                 c.ID,
35.                 c.ComputerName,
36.                 GroupName = r.Name
37.             };

6、分页

SQL：

1. -- 方案1
2. SELECT TOP 10 * FROM [Clients] WHERE Status=1
3. AND ID NOT IN 
4. (
5.     SELECT TOP 20 ID FROM [Clients] WHERE Status=1
6.     ORDER BY ComputerName
7. )
8. ORDER BY ComputerName
9.  
10. --方案2
11. SELECT * FROM
12. (
13.     SELECT *, ROW_NUMBER() OVER(ORDER BY ComputerName) AS RowNo
14.     FROM [Clients]
15.     WHERE Status=1
16. )t
17. WHERE RowNo >= 20 AND RowNo < 30

EF：

1. //Func形式
2. var clients = ctx.Clients.Where(c => c.Status=1)
3.             .OrderBy(c => c.ComputerName)
4.             .Skip(20)
5.             .Take(10)
6.             .ToList();
7.  
8. //Linq形式
9. var clients = (from c in ctx.Clients
10.             orderby c.ComputerName
11.             select c).Skip(20).Take(10);

7、分组统计：

SQL：

1. SELECT Status,COUNT(*) AS Cnt FROM [Clients] 
2. GROUP BY Status
3. ORDER BY COUNT(*) DESC

EF：

1. //Func形式
2. var result = ctx.Clients.GroupBy(c => c.Status)
3.             .Select(s => new
4.             {
5.                 Status = s.Key,
6.                 Cnt = s.Count()
7.             })
8.             .OrderByDescending(r => r.Cnt);
9.  
10. //Linq形式
11. var result = from c in ctx.Clients
12.             group c by c.Status into r
13.             orderby r.Count() descending
14.             select new
15.             {
16.                 Status = r.Key,
17.                 Cnt = r.Count()
18.             };

未完待续……