Entity Framework 查询

来源：互联网发布：js post调用java方法编辑：程序博客网时间：2024/05/18 04:00

1、简单查询：

SQL：

?
1
SELECT*FROM[Clients]WHEREType=1ANDDeleted=0ORDERBYID

EF：

?
1
2
3
4
5
6
7
8
9
10
//Func形式
    varclients = ctx.Clients.Where(c => c.Type == 1 && c.Deleted == 0)
                .OrderBy(c => c.ID) 
                .ToList();
      
    //Linq形式
    varclients =fromcinctx.Clients
                  wherec.Type == 1 && c.Deleted==0
                  orderbyc.ID
                  selectc;

2、查询部分字段：

SQL：

?
1
SELECTID,NameFROM[Clients]WHEREStatus=1

EF：

?
1
2
3
4
5
6
7
8
9
//Func形式
    varclients = ctx.Clients.Where(c => c.Status == 1)
                .Select(c => new{ c.ID, Name = c.ComputerName })
                .ToList();
      
    //Linq形式
    varclients =fromcinctx.Clients
                  wherec.Status == 1
                  selectnew{ c.ID, Name = c.ComputerName }; ：

3、查询单一记录：

SQL:

?
1
SELECT*FROM[Clients]WHEREID=100

EF：

?
1
2
3
4
5
6
7
//Func形式
    varclient = ctx.Clients.FirstOrDefault(c => c.ID == 100);
      
    //Linq形式
    varclient = (fromcinctx.Clients
                wherec.ID = 100
                selectc).FirstOrDefault();

4、LEFT JOIN 连接查询

SQL：

?
1
2
3
4
5
6
SELECT c.ID ,
        c.ComputerName ,
        g.NameGroupName
FROM   [Clients] c
        LEFTJOIN[Groups] gONc.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形式
    varclients = 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形式
    varclients =fromcinctx.Clients
                wherec.Status == 1
                selectnew
                {
                    c.ID,
                    c.ComputerName,
                    GroupName = (fromginctx.Groups
                                whereg.ID == c.GroupID
                                selectg.Name).FirstOrDefault()
                };

5、INNER JOIN 连接查询：

SQL：

?
1
2
3
4
5
6
7
SELECT c.ID ,
        c.ComputerName ,
        g.NameGroupName
FROM   [Clients] c
        INNERJOIN[Groups] gONc.GroupID = g.ID
WHERE  c.Status = 1
ORDERBYg.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形式
    varclients = 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
    varclients =fromcinctx.Clients
                fromginctx.Groups
                wherec.GroupID == g.ID
                orderbyg.Name
                selectnew
                {
                    c.ID,
                    c.ComputerName,
                    GroupName = g.Name 
                };
      
    //Linq形式2
    varclients =fromcinctx.Clients
                wherec.Status == 1
                joinginctx.Group
                onc.GroupIDequalsg.IDintoresult
                fromrinresult
                orderbyr.Name
                selectnew
                {
                    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
SELECTTOP10
        *
FROM   [Clients]
WHERE  Status = 1
        ANDIDNOTIN(SELECTTOP20
                                ID
                        FROM   [Clients]
                        WHERE  Status = 1
                        ORDERBYComputerName )
ORDERBYComputerName
      
--方案2
SELECT *
FROM   (SELECT   * ,
                    ROW_NUMBER() OVER ( ORDERBYComputerName )ASRowNo
          FROM     [Clients]
          WHERE    Status = 1
        ) t
WHERE  RowNo >= 20
        ANDRowNo < 30

EF：

?
1
2
3
4
5
6
7
8
9
10
11
//Func形式
    varclients = ctx.Clients.Where(c => c.Status=1)
                .OrderBy(c => c.ComputerName) 
                .Skip(20)
                .Take(10)
                .ToList();
      
    //Linq形式
    varclients = (fromcinctx.Clients
                orderbyc.ComputerName
                selectc).Skip(20).Take(10);

7、分组统计：

SQL：

?
1
2
3
4
5
SELECT Status ,
        COUNT(*)ASCnt
FROM   [Clients]
GROUPBYStatus
ORDERBYCOUNT(*)DESC

EF：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
//Func形式
    varresult = ctx.Clients.GroupBy(c => c.Status)
                .Select(s => new
                {
                    Status = s.Key, 
                    Cnt = s.Count() 
                })
                .OrderByDescending(r => r.Cnt); 
      
    //Linq形式
    varresult =fromcinctx.Clients
                groupcbyc.Statusintor
                orderbyr.Count()descending
                selectnew
                {
                    Status = r.Key, 
                    Cnt = r.Count() 
                };

0 0