EntityFramwork 查询

EntityFramwork 查询

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

 

4LEFT 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()

            };

 

5INNER 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/liyanwei/p/4e2dec72c55d719f05a6c7af054fc297.html

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值