LINQ查询语法与方法的完整实践指南
1.基础数据查询
LINQ提供了三种常见的查询数据方式：查询语法、方法语法和原生SQL。
//查询语法 - 使用from...select
var users = from u in context.UserInfos
select u;
//方法语法 - 直接返回IQueryable
var users1 = context.UserInfos;
//原生SQL - 适用于复杂查询
string query = "SELECT * FROM UserInfos";
2.条件筛选
使用where子句进行数据过滤，支持多种运算符。
//查询语法方式
var filteredUsers = from u in context.UserInfos
where u.UserId > 100 && u.Status == 1
select u;
//方法语法 - Lambda表达式
var filteredUsers1 = context.UserInfos
.Where(u => u.UserId > 100 && u.Status == 1)
.ToList();
//对应的SQL语句
string sql = "SELECT * FROM UserInfos WHERE UserId > 100 AND Status = 1";
3.聚合函数应用
LINQ支持Count、Sum、Min、Max、Average等聚合操作。
//查询语法实现聚合
var maxId = (from u in context.UserInfos
select u.UserId).Max();
var minId = (from u in context.UserInfos
select u.UserId).Min();
var totalCount = (from u in context.UserInfos
select u).Count();
var sumOfIds = (from u in context.UserInfos
select u.UserId).Sum();
var avgScore = (from u in context.UserInfos
select u.Score).Average();
//方法语法实现聚合
var maxId1 = context.UserInfos.Max(u => u.UserId);
var minId1 = context.UserInfos.Min(u => u.UserId);
var count1 = context.UserInfos.Count();
var sum1 = context.UserInfos.Sum(u => u.UserId);
var avg1 = context.UserInfos.Average(u => u.Score);
//对应的SQL语句
string sqlMax = "SELECT MAX(UserId) FROM UserInfos";
string sqlMin = "SELECT MIN(UserId) FROM UserInfos";
string sqlCount = "SELECT COUNT(1) FROM UserInfos";
string sqlSum = "SELECT SUM(UserId) FROM UserInfos";
string sqlAvg = "SELECT AVG(Score) FROM UserInfos";
4.数据排序操作
使用OrderBy升序，OrderByDescending降序排序。
//查询语法 - 降序排列
var orderedList = from u in context.UserInfos
where u.UserId > 50
orderby u.CreateTime descending
select u;
//查询语法 - 升序排列
var orderedListAsc = from u in context.UserInfos
where u.UserId > 50
orderby u.CreateTime ascending
select u;
//方法语法 - 链式调用
var resultDesc = context.UserInfos
.Where(u => u.UserId > 50)
.OrderByDescending(u => u.CreateTime)
.ToList();
var resultAsc = context.UserInfos
.Where(u => u.UserId > 50)
.OrderBy(u => u.CreateTime)
.ToList();
//SQL对应写法
string sqlDesc = "SELECT * FROM UserInfos WHERE UserId > 50 ORDER BY CreateTime DESC";
string sqlAsc = "SELECT * FROM UserInfos WHERE UserId > 50 ORDER BY CreateTime ASC";
5.获取单条记录
FirstOrDefault返回序列第一个元素或默认值，LastOrDefault的行为需注意。
//获取第一条记录
var firstUser = (from u in context.UserInfos
orderby u.UserId descending
select u).FirstOrDefault();
//方法语法获取首条
var firstUser1 = context.UserInfos.FirstOrDefault();
//注意：LINQ to EF不支持Last()方法
//要获取最后一条，需先降序排列
var lastUser = context.UserInfos
.OrderByDescending(u => u.UserId)
.FirstOrDefault();
//SQL实现
string sql = "SELECT TOP(1) * FROM UserInfos";
6.跳过指定数量的记录
Skip方法用于跳过前N条记录，常用于实现分页。
//查询语法 - 跳过前20条记录
var skipResult = (from u in context.UserInfos
orderby u.UserId descending
select u).Skip(20);
//方法语法
var skipResult1 = context.UserInfos
.OrderByDescending(u => u.UserId)
.Skip(20)
.ToList();
//使用ROW_NUMBER实现相同效果
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY UserId DESC) AS RowNum, * FROM UserInfos) AS Temp WHERE RowNum > 20";
7.分页查询实现
结合Skip和Take方法实现数据分页。
//查询语法 - 查询第21-30条记录（第二页）
var pageData = (from u in context.UserInfos
where u.Status == 1
orderby u.UserId descending
select u).Skip(20).Take(10);
//方法语法 - 更加直观的链式调用
var pageData1 = context.UserInfos
.Where(u => u.Status == 1)
.OrderByDescending(u => u.UserId)
.Skip(20)
.Take(10)
.ToList();
//SQL实现方式
string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY UserId DESC) AS RowNum, * FROM UserInfos WHERE Status = 1) AS Temp WHERE RowNum > 20 AND RowNum <= 30";
8.字符串包含查询
使用Contains方法实现模糊匹配，相当于SQL的LIKE语句。
//查询语法 - 查找名称包含"张"的用户
var matchedUsers = from u in context.UserInfos
where u.UserName.Contains("张")
select u;
//方法语法
var matchedUsers1 = context.UserInfos
.Where(u => u.UserName.Contains("张"))
.ToList();
//StartsWith - 以指定字符开头
var startsWith = context.UserInfos
.Where(u => u.UserName.StartsWith("张"))
.ToList();
//EndsWith - 以指定字符结尾
var endsWith = context.UserInfos
.Where(u => u.UserName.EndsWith("张"))
.ToList();
//SQL对应写法
string sql = "SELECT * FROM UserInfos WHERE UserName LIKE '%张%'";
9.分组统计操作
GroupBy实现数据分组，可配合聚合函数进行统计分析。
//查询语法 - 按部门分组统计
var groupResult = from u in context.UserInfos
orderby u.UserId descending
group u by u.Department into deptGroup
select new
{
Department = deptGroup.Key,
TotalCount = deptGroup.Count(),
MaxId = deptGroup.Max(u => u.UserId),
MinId = deptGroup.Min(u => u.UserId),
AvgScore = deptGroup.Average(u => u.Score)
};
foreach (var group in groupResult)
{
Console.WriteLine($"部门：{group.Department}, 人数：{group.TotalCount}");
}
//方法语法 - 分组后处理
var groupResult1 = context.UserInfos
.GroupBy(u => u.Department)
.Select(g => new
{
Department = g.Key,
Count = g.Count(),
MinId = g.Min(u => u.UserId)
})
.ToList();
//多字段分组
var multiGroup = from u in context.UserInfos
group u by new { u.Department, u.Position } into g
select new
{
g.Key.Department,
g.Key.Position,
Count = g.Count()
};
var multiGroup1 = context.UserInfos
.GroupBy(u => new { u.Department, u.Position })
.Select(g => new
{
g.Key.Department,
g.Key.Position,
Count = g.Count()
})
.ToList();
//SQL对应写法
string sql = "SELECT Department, COUNT(1) AS TotalCount, MAX(UserId) AS MaxId, MIN(UserId) AS MinId FROM UserInfos GROUP BY Department";
10.多表连接查询
Join方法实现内连接，类似于SQL的INNER JOIN。
//查询语法 - 内连接查询
var joinResult = from u in context.UserInfos
join d in context.Departments on u.DeptId equals d.DeptId
orderby u.UserId descending
select new
{
u.UserName,
u.Score,
DeptName = d.DeptName
};
//方法语法
var joinResult1 = context.UserInfos
.Join(context.Departments,
u => u.DeptId,
d => d.DeptId,
(u, d) => new { u.UserName, u.Score, d.DeptName })
.OrderByDescending(x => x.Score)
.ToList();
//左外连接 - 使用GroupJoin配合DefaultIfEmpty
var leftJoin = from u in context.UserInfos
join d in context.Departments on u.DeptId equals d.DeptId into deptJoin
from d in deptJoin.DefaultIfEmpty()
select new
{
u.UserName,
DeptName = d == null ? "未分配" : d.DeptName
};
//SQL对应写法
string sql = "SELECT u.*, d.DeptName FROM UserInfos u INNER JOIN Departments d ON u.DeptId = d.DeptId ORDER BY u.UserId DESC";
11.IN子句实现
使用Contains方法模拟SQL中的IN查询。
//方法语法 - 查询指定ID集合的用户
int[] userIds = { 101, 102, 103, 104, 105 };
var inQueryResult = context.UserInfos
.Where(u => userIds.Contains(u.UserId))
.ToList();
//查询语法
var inQueryResult1 = from u in userIds
join temp in context.UserInfos on u equals temp.UserId
select temp;
//数组方式直接查询
var directInQuery = context.UserInfos
.Where(u => new[] { 101, 102, 103 }.Contains(u.UserId))
.ToList();
//SQL对应写法
string sql = "SELECT * FROM UserInfos WHERE UserId IN (101, 102, 103, 104, 105)";
