来给你几个层次递进的 LINQ Join 示例,从最基础的单表连接到多表、对象导航、分组连接全部覆盖。
以下示例均基于 C# + LINQ to Objects(适用于 EF Core / ABP 的仓储查询场景)。
🌱 一、基本示例:两表连接(内连接 Inner Join)
假设你有两个列表:
var users = new List<User>
{new User { Id = 1, Name = "Alice" },new User { Id = 2, Name = "Bob" },new User { Id = 3, Name = "Charlie" }
};var orders = new List<Order>
{new Order { Id = 101, UserId = 1, Product = "Laptop" },new Order { Id = 102, UserId = 1, Product = "Mouse" },new Order { Id = 103, UserId = 2, Product = "Keyboard" }
};
定义实体类:
public class User
{public int Id { get; set; }public string Name { get; set; }
}public class Order
{public int Id { get; set; }public int UserId { get; set; }public string Product { get; set; }
}
✅ 内连接写法(Join)
var query = from u in usersjoin o in orders on u.Id equals o.UserIdselect new{UserName = u.Name,o.Product};foreach (var item in query)
{Console.WriteLine($"{item.UserName} 买了 {item.Product}");
}
输出:
Alice 买了 Laptop
Alice 买了 Mouse
Bob 买了 Keyboard
🌿 二、左连接(Left Join)
如果想保留所有用户,即使他们没有下单,也要出现在结果中(类似 SQL LEFT JOIN):
var query = from u in usersjoin o in orders on u.Id equals o.UserId into userOrdersfrom o in userOrders.DefaultIfEmpty() // 左连接的关键select new{UserName = u.Name,Product = o?.Product ?? "(无订单)"};foreach (var item in query)
{Console.WriteLine($"{item.UserName} - {item.Product}");
}
输出:
Alice - Laptop
Alice - Mouse
Bob - Keyboard
Charlie - (无订单)
🌾 三、多字段连接(Composite Key Join)
有时你要根据多个字段联合匹配:
var recordsA = new[]
{new { UserId = 1, Year = 2024, Score = 90 },new { UserId = 2, Year = 2025, Score = 88 }
};var recordsB = new[]
{new { UserId = 1, Year = 2024, Rank = 1 },new { UserId = 2, Year = 2025, Rank = 2 }
};var query = from a in recordsAjoin b in recordsBon new { a.UserId, a.Year } equals new { b.UserId, b.Year }select new{a.UserId,a.Year,a.Score,b.Rank};foreach (var item in query)Console.WriteLine($"{item.UserId}-{item.Year}: Score={item.Score}, Rank={item.Rank}");
🌻 四、GroupJoin(分组连接)
如果你想把每个用户的订单放进集合中(类似 SQL 的 “Group By + Join”):
var query = from u in usersjoin o in orders on u.Id equals o.UserId into userOrdersselect new{u.Name,Orders = userOrders.ToList()};foreach (var user in query)
{Console.WriteLine($"{user.Name} 的订单:");foreach (var order in user.Orders)Console.WriteLine($" - {order.Product}");
}
输出:
Alice 的订单:- Laptop- Mouse
Bob 的订单:- Keyboard
Charlie 的订单:(无)
🌺 五、在 EF Core / ABP 仓储中使用
假设你有 ABP 实体:
public class User : Entity<Guid>
{public string Name { get; set; }
}public class Order : Entity<Guid>
{public Guid UserId { get; set; }public string Product { get; set; }
}
仓储查询:
var query =from user in await _userRepository.GetQueryableAsync()join order in await _orderRepository.GetQueryableAsync()on user.Id equals order.UserIdselect new { user.Name, order.Product };var list = await AsyncExecuter.ToListAsync(query);
ABP 的 IRepository.GetQueryableAsync()
返回 EF Core 的 IQueryable<TEntity>
,
AsyncExecuter.ToListAsync()
是 ABP 内部封装的异步执行器(相当于 await query.ToListAsync()
)。
✅ 六、总结
类型 | 描述 | 关键写法 |
---|---|---|
内连接(Inner Join) | 匹配的记录才保留 | join ... on ... equals ... |
左连接(Left Join) | 主表全保留 | join ... into ... from ... DefaultIfEmpty() |
复合键连接 | 多字段匹配 | on new { a.X, a.Y } equals new { b.X, b.Y } |
分组连接 | 一对多集合 | join ... into ... select new { key, group } |