博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

EntityFrameWork使用

Posted on 2014-07-25 15:57  first_start  阅读(232)  评论(0)    收藏  举报

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

 

 

// INNER JOIN ****************************************************************************
var TelefoneInnerJoinPessoa = Db.Telefones
.AsNoTracking()
.Join(Db.Pessoas, t => t.PessoaId, p => p.PessoaId, (t, p) => new { t, p })
.Select(s => new
{
s.t.PessoaId,
s.p.Nome,
s.t.TelefoneId,
s.t.Ddd,
s.t.Numero
}).AsQueryable();
var TGeracao = TelefoneInnerJoinPessoa.ToList();
// INNER JOIN ****************************************************************************

// LEFT JOIN ****************************************************************************
var PessoaLeftJoinTelefone = Db.Pessoas
.AsNoTracking()
.GroupJoin(Db.Telefones, p => p.PessoaId, t => t.PessoaId, (p, t) => new { p, t })
.SelectMany(temp => temp.t.DefaultIfEmpty(), (p, t) => new
{
t.PessoaId,
p.p.Nome,
TelefoneId = (int?)t.TelefoneId,
t.Ddd,
t.Numero
}).AsQueryable();
var TGeracaoLeft = PessoaLeftJoinTelefone.ToList();
// LEFT JOIN ****************************************************************************


// GROUP BY *****************************************************************************
var TelefonesCountByPessoa =
Db.Telefones.AsNoTracking()
.GroupBy(x => x.PessoaId)
.Select(x => new
{
PessoaId = x.Key,
Quantidade = (int?)x.Count()
})
.Join(Db.Pessoas, a => a.PessoaId, p => p.PessoaId, (a, p) => new { a, p })
.Select(s => new
{
s.a.PessoaId,
s.p.Nome,
Quantidade = s.a.Quantidade ?? 0
}).AsQueryable();
var TGeracaoGroup1 = TelefonesCountByPessoa.ToList();

var TelefonesCountByPessoa1 =
Db.Telefones.AsNoTracking()
.GroupBy(x => x.PessoaId)
.Select(x => new
{
PessoaId = x.Key,
Quantidade = (int?)x.Count()
}).AsQueryable();

var PessoasToTelefonesCountByPessoa1 = Db.Pessoas
.AsNoTracking()
.GroupJoin(TelefonesCountByPessoa1, p => p.PessoaId, a => a.PessoaId, (p, a) => new { p, a })
.SelectMany(temp => temp.a.DefaultIfEmpty(),
(p, a) => new
{
p.p.PessoaId,
Quantidade = a.Quantidade ?? 0,
p.p.Nome
}).AsQueryable();
var TGeracaoGroup2 = PessoasToTelefonesCountByPessoa1.ToList();

var PessoasToTelefonesCountByPessoa3 = Db.Pessoas.Select(x => new
{
x.PessoaId,
x.Nome,
Quantidade = x.Telefones.LongCount()
}).AsQueryable();
var TGeracao3 = PessoasToTelefonesCountByPessoa3.ToList();
// GROUP BY *****************************************************************************

// UNION ALL ****************************************************************************
var TbAUnionAllTbB = Db.TbAs.Select(x => new
{
x.Id,
x.Descricao
}).Concat(Db.TbBs.Select(s => new
{
s.Id,
s.Descricao
})).AsQueryable();

var GeracaoUnionAll = TbAUnionAllTbB.ToList();
// UNION ALL ****************************************************************************

//IN, NOT IN, EXISTS e NOT EXISTS
//IN
ICollection<int> Lista = Db.TbBs
.AsNoTracking()
.Select(x => x.Id).ToList<int>();
ICollection<int> lista = new List<int>() { 1, 2 }; VALORES FIXOS

var SelectIn = Db.TbAs
.AsNoTracking()
.Where(x => Lista.Contains(x.Id))
.AsQueryable();

var GeracaoTIn = SelectIn.ToList();

//NOT IN
var SelectNotIn = Db.TbAs
.AsNoTracking()
.Where(x => !Lista.Contains(x.Id))
.AsQueryable();
var GeracaoTNotIn = SelectNotIn.ToList();
//IN, NOT IN, EXISTS e NOT EXISTS

EXISTS
var SelectExists = Db.TbAs
.AsNoTracking()
.Where(x => Db.TbBs.Select(a => a.Id).Contains(x.Id))
.AsQueryable();

var GeracaoTExists = SelectExists.ToList();

EXISTS
var SelectNotExists = Db.TbAs
.AsNoTracking()
.Where(x => !Db.TbBs.Select(a => a.Id).Contains(x.Id))
.AsQueryable();

var GeracaoTNotExists = SelectNotExists.ToList();

 

 

 

T-SQL的IN：

Select ProductID, ProductName, CategoryID
From dbo.Products
Where CategoryID in (1, 2)

T-SQL的NOT IN：

Select ProductID, ProductName, CategoryID
From dbo.Products
Where CategoryID not in (1, 2)

Or

Select ProductID, ProductName, CategoryID
From dbo.Products
Where not CategoryID in (1, 2)

LINQ的IN：

var queryResult = from p in db.Products
where (new int?[] {1,2}).Contains(p.CategoryID)
select p;

LINQ的IN解析成SQL：

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued] 
FROM [dbo].[Products]AS [t0]
WHERE [t0].[CategoryID] IN (@p0, @p1)

LINQ的NOT IN：

var queryResult = from p in db.Products
where ! (new int?[] {1,2}).Contains(p.CategoryID)
select p;

LINQ的NOT IN解析成SQL：

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued] 
FROM [dbo].[Products]AS [t0]
WHERE NOT [t0].[CategoryID] IN (@p0, @p1)

 

 

Entity FrameWork 取表中字段的最大值

int? max = (from t in context.Test
                        select (int?)t.ID).Max();
            int m = 0;
            if (!max.HasValue)
            {
                m = 1;
            }
            else
                m = (int)max + 1;