Entity Framework是个好东西,虽然没有Hibernate功能强大,但使用更简便。今天整理一下常见SQL如何用EF来表达,Func形式和Linq形式都会列出来(本人更喜欢Func形式)。
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=1EF:
//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=100EF:
//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:
SELECT c.ID,c.ComputerName,g.Name GroupName FROM [Clients] c LEFT JOIN [Groups] g ON c.GroupID = g.ID WHERE c.Status=1EF:
//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:
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.NameEF:
//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 < 30EF:
//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(*) DESCEF:
//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() };未完待续……