首页 > 代码库 > EntityFramework
EntityFramework
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() }; |
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。