转:整理一下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：

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

未完待续……

转载于:https://www.cnblogs.com/joeylee/p/3543324.html