整理一下Entity Framework的查询

2012-08-30 13:41:59

标签:Entity Framework

原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://boytnt.blog.51cto.com/966121/977382

Entity Framework是个好东西,虽然没有Hibernate功能强大,但使用更简便。今天整理一下常见SQL如何用EF来表达,Func形式和Linq形式都会列出来(本人更喜欢Func形式)。

1、简单查询:

SQL:

  1. SELECT * FROM [Clients] WHERE Type=1 AND Deleted=0 ORDER BY ID

EF:

  1. //Func形式

  2. var clients = ctx.Clients.Where(c => c.Type == 1 && c.Deleted == 0) 
  3. .OrderBy(c => c.ID) 
  4. .ToList(); 
  5. //Linq形式

  6. var clients = from c in ctx.Clients 
  7. where c.Type == 1 && c.Deleted==0 
  8. orderby c.ID 
  9. select c;


2、查询部分字段:

SQL:

  1. SELECT ID,Name FROM [Clients] WHERE Status=1

EF:

  1. //Func形式

  2. var clients = ctx.Clients.Where(c => c.Status == 1) 
  3. .Select(c => new { c.ID, Name = c.ComputerName }) 
  4. .ToList(); 
  5. //Linq形式

  6. var clients = from c in ctx.Clients 
  7. where c.Status == 1 
  8. select new { c.ID, Name = c.ComputerName };

3、查询单一记录:

SQL:

  1. SELECT * FROM [Clients] WHERE ID=100

EF:

  1. //Func形式

  2. var client = ctx.Clients.FirstOrDefault(c => c.ID == 100); 
  3. //Linq形式

  4. var client = (from c in ctx.Clients 
  5. where c.ID = 100 
  6. select c).FirstOrDefault();


4、LEFT JOIN 连接查询

SQL:

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

EF:

  1. //Func形式

  2. var clients = ctx.Clients.Where(c => c.Status == 1) 
  3. .Select(c => new

  4. c.ID, 
  5. c.ComputerName, 
  6. GroupName = ctx.Groups.FirstOrDefault(g => g.ID == c.GroupID).Name 
  7. }) 
  8. .ToList(); 
  9. //Linq形式

  10. var clients = from c in ctx.Clients 
  11. where c.Status == 1 
  12. select new

  13. c.ID, 
  14. c.ComputerName, 
  15. GroupName = (from g in ctx.Groups 
  16. where g.ID == c.GroupID 
  17. select g.Name).FirstOrDefault() 
  18. };

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. c.ID, 
  5. c.ComputerName, 
  6. GroupName = g.Name 
  7. }) 
  8. .OrderBy(item => item.GroupName) 
  9. .ToList(); 
  10. //Linq形式1

  11. var clients = from c in ctx.Clients 
  12. from g in ctx.Groups 
  13. where c.GroupID == g.ID 
  14. orderby g.Name 
  15. select new

  16. c.ID, 
  17. c.ComputerName, 
  18. GroupName = g.Name 
  19. }; 
  20. //Linq形式2

  21. var clients = from c in ctx.Clients 
  22. where c.Status == 1 
  23. join g in ctx.Group 
  24. on c.GroupID equals g.ID into result 
  25. from r in result 
  26. order by r.Name 
  27. select new

  28. c.ID, 
  29. c.ComputerName, 
  30. GroupName = r.Name 
  31. };


6、分页

SQL:

  1. -- 方案1

  2. SELECT TOP 10 * FROM [Clients] WHERE Status=1 
  3. AND ID NOT IN

  4. SELECT TOP 20 ID FROM [Clients] WHERE Status=1 
  5. ORDER BY ComputerName 
  6. ORDER BY ComputerName 
  7. --方案2

  8. SELECT * FROM
  9. SELECT *, ROW_NUMBER() OVER(ORDER BY ComputerName) AS RowNo 
  10. FROM [Clients] 
  11. WHERE Status=1 
  12. )t 
  13. 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. //Linq形式

  8. var clients = (from c in ctx.Clients 
  9. orderby c.ComputerName 
  10. 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. Status = s.Key, 
  5. Cnt = s.Count() 
  6. }) 
  7. .OrderByDescending(r => r.Cnt); 
  8. //Linq形式

  9. var result = from c in ctx.Clients 
  10. group c by c.Status into r 
  11. orderby r.Count() descending 
  12. select new

  13. Status = r.Key, 
  14. Cnt = r.Count() 
  15. };

未完待续……

04-14 12:15