Linq to SQL：如何通过单次SQL事务获取带角色列表的Users对象

我在SQL数据库中有如下表实体类：

using System.Data.Linq.Mapping;

[Table(Name = "Users")]
class Users
{
    [Column(Name="UserKey", IsPrimaryKey=true)]
    public string UserKey { get; set; }
    [Column(Name="Username")]
    public string UserName { get; set; }

    public IList<UsersRoles> Roles = new List<UsersRoles>();
}

[Table(Name ="Roles")]
class UsersRoles
{
    [Column(Name="UserKey", IsPrimaryKey =true)]
    public string UserKey { get; set; }
    [Column(Name ="RoleKey", IsPrimaryKey =true)]
    public string RoleKey { get; set; }
}

注：Users类中的public IList<UsersRoles> Roles = new List<UsersRoles>();不属于数据库字段。

我希望仅通过单次SQL事务获取填充好对应角色列表的Users对象。目前尝试过两种方式但都有问题：

• 用匿名对象new {user, role}关联查询，不符合返回需求；
• 遍历查询（如下代码），会产生N+1次SQL事务（用户数量+1）：

var users = from user in dataContext.GetTable<Users>()               
            where user.UserKey == "1234" || user.UserKey == "5678"
            select user;

foreach (var user in users)
{
    user.Roles = (from roles in dataContext.GetTable<UsersRoles>() where roles.UserKey == user.UserKey select roles).ToList<UsersRoles>();
    
    Console.WriteLine(user.UserKey + " " + user.UserName + ":");
    foreach (var role in user.Roles)
    {
        Console.WriteLine("- " + role.RoleKey);
    }
}

请问该如何实现需求？

可以通过以下两种方式实现，均能在单个事务内完成，且避免N+1查询问题：

方法一：分组Join一次性查询组装 #

这种方式只会触发1次带LEFT JOIN的SQL查询，一次性拉取所有目标用户和对应角色数据，再在内存中完成对象组装：

using (var transaction = new TransactionScope())
{
    var userWithRoles = from user in dataContext.GetTable<Users>()
                        where user.UserKey == "1234" || user.UserKey == "5678"
                        join role in dataContext.GetTable<UsersRoles>()
                        on user.UserKey equals role.UserKey into userRolesGroup
                        select new 
                        {
                            User = user,
                            Roles = userRolesGroup.ToList()
                        };

    // 将角色列表赋值给Users对象
    var users = userWithRoles.Select(u => 
    {
        u.User.Roles = u.Roles;
        return u.User;
    }).ToList();

    // 后续处理逻辑
    foreach (var user in users)
    {
        Console.WriteLine($"{user.UserKey} {user.UserName}:");
        foreach (var role in user.Roles)
        {
            Console.WriteLine($"- {role.RoleKey}");
        }
    }

    transaction.Complete();
}

方法二：批量查询+内存关联 #

如果分组Join的写法不适合复杂业务场景，可先批量查询用户和角色，再在内存中关联，全程仅触发2次SQL查询：

using (var transaction = new TransactionScope())
{
    var targetUserKeys = new List<string> { "1234", "5678" };
    
    // 批量查询目标用户
    var users = dataContext.GetTable<Users>()
                           .Where(u => targetUserKeys.Contains(u.UserKey))
                           .ToList();

    // 批量查询这些用户的所有角色
    var userRoles = dataContext.GetTable<UsersRoles>()
                               .Where(r => targetUserKeys.Contains(r.UserKey))
                               .ToList();

    // 内存中关联角色到用户
    foreach (var user in users)
    {
        user.Roles = userRoles.Where(r => r.UserKey == user.UserKey).ToList();
    }

    // 后续处理逻辑
    foreach (var user in users)
    {
        Console.WriteLine($"{user.UserKey} {user.UserName}:");
        foreach (var role in user.Roles)
        {
            Console.WriteLine($"- {role.RoleKey}");
        }
    }

    transaction.Complete();
}

补充说明 #

• 修正了原代码中的笔误：原代码中user.Id和roles.Id应为user.UserKey和roles.UserKey，与实体类主键字段对应；
• TransactionScope确保所有查询操作都在同一个事务内执行，满足"单次SQL事务"的要求；
• 两种方式均不会产生N+1查询，性能远优于遍历查询的实现。

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