如何通过单LINQ查询关联表并分组获取用户及对应卡号列表？

数据库模型类 #

application_user.cs: #

public partial class application_user
{
    public int Id { get; set; }
    public string Name { get; set; }
}

application_user_card.cs: #

public partial class application_user_card
{
    public int Id { get; set; }
    public int User_Id { get; set; }
    public string Card_Id { get; set; }
}

业务逻辑数据模型 #

User.cs: #

public class User
{
    public int Id { get; set; }

    public string Name { get; set; }

    public List<string> CardIds { get; set; }
}

现有代码（存在疑问） #

List<UserGroup> userGroups = from user in pigDbAccountEntities.application_user
                             join cardIds in pigDbAccountEntities.application_user_card on user.Id equals cardIds.User_Id
                             where user.Delete_Time == DateTime.MinValue
                             group user by user.Id into gUser
                             select new UserGroup
                             {
                                 Id = gUser.Key,
                                 Name = gUser.FirstOrDefault().Name,
                                 CardIds = new List<string>() { cardIds.Card_Id.ToList() }
                             };

需求：获取所有符合条件的用户列表，每个用户需包含对应的卡号列表，能否通过单个查询实现？

解决方案 #

可以通过单个LINQ查询实现，你的原有代码存在两个核心问题：分组后无法访问原cardIds变量、普通join会过滤掉无卡号的用户。以下是正确实现方式：

方法一：左连接+直接聚合卡号列表 #

这是最优方案，直接在数据库层面完成关联和聚合，同时保留无卡号的用户：

List<User> users = (from user in pigDbAccountEntities.application_user
                    where user.Delete_Time == DateTime.MinValue
                    // 使用into实现左连接，将用户对应的所有卡数据归入userCards集合
                    join card in pigDbAccountEntities.application_user_card 
                    on user.Id equals card.User_Id into userCards
                    select new User
                    {
                        Id = user.Id,
                        Name = user.Name,
                        // 从userCards中提取所有卡号并转为列表
                        CardIds = userCards.Select(c => c.Card_Id).ToList()
                    }).ToList();

方法二：分组卡表后关联用户（备选） #

如果更习惯分组语法，也可以先对卡表按用户ID分组，再关联用户表：

// 先把卡数据按用户ID分组，生成用户ID到卡号列表的字典
var cardDict = pigDbAccountEntities.application_user_card
    .GroupBy(c => c.User_Id)
    .ToDictionary(g => g.Key, g => g.Select(c => c.Card_Id).ToList());

// 查询用户并匹配卡号列表
List<User> users = pigDbAccountEntities.application_user
    .Where(u => u.Delete_Time == DateTime.MinValue)
    .Select(u => new User
    {
        Id = u.Id,
        Name = u.Name,
        // 存在匹配则取卡号列表，否则返回空列表
        CardIds = cardDict.TryGetValue(u.Id, out var cards) ? cards : new List<string>()
    }).ToList();

原有代码问题说明 #

1. 分组后gUser是分组后的用户集合，原cardIds变量不在当前作用域，无法直接访问；
2. 普通join会自动过滤掉没有对应卡号的用户，而join ... into的左连接可以保留所有符合条件的用户；
3. CardIds = new List<string>() { cardIds.Card_Id.ToList() }逻辑错误，cardIds是单个卡对象，不存在ToList()方法，且写法不符合列表赋值逻辑。

内容的提问来源于stack exchange，提问作者lenny