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

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

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.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() 
            }; 
Last modification:December 12th, 2019 at 03:10 pm
如果觉得我的文章对你有用,请随意赞赏