整理一下Entity Framework的查询

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 2 3 4 5 6 7 8 9 10

//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：

?

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

EF：

?

1 2 3 4 5 6 7 8 9

//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:

?

1	SELECT * FROM [Clients] WHERE ID=100

EF：

?

1 2 3 4 5 6 7

//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：

?

1 2 3 4 5 6

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：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

//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 2 3 4 5 6 7

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：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

//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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

-- 方案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：

?

1 2 3 4 5 6 7 8 9 10 11

//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：

?

1 2 3 4 5

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

EF：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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