如何用LINQ联合查询优化EF6中用户菜单权限的三次查询？

原实现逻辑与代码 #

当前通过三次数据库查询获取用户菜单权限，使用EF6框架，原代码如下：

var user = await _userManager.FindByEmailAsync(request.Email);
var Menuss = await _context.MenuPermissions
      .Where(x => x.UserId == user.Id && x.IsHide==false)
      .Select(x => x.MenuId)
      .ToListAsync();
var getMenus = await _context.Menus
      .Where(x => Menuss.Contains(x.Id))
      .Include(x => x.MenuPermissions)
      .ToListAsync();

逻辑步骤：

• 根据邮箱获取当前用户
• 通过MenuPermission表（以用户ID为外键）筛选出未隐藏的权限对应的MenuId列表
• 利用MenuId列表查询对应的菜单信息，同时关联菜单权限

对应DTO结构 #

public class GetMenuForUserDto
{
      public Guid Id { get; set; }
      public string EngName { get; set; }
      public string Title { get; set; }
      public int Rank { get; set; }
      public Guid? ParentId { get; set; }
      public string RoutePath { get; set; }
      public bool IsHide { get; set; }
      public string Icon { get; set; }
      public string Type { get; set; }
      public GetMenuPermissionsDto MenuPermissions { get; set; }
}

public class GetMenuPermissionsDto
{
      public Guid Id { get; set; }
      public string UserId { get; set; }
      public Guid MenuId { get; set; }
      public bool Add { get; set; }
      public bool Edit { get; set; }
      public bool View { get; set; }
      public bool Delete { get; set; }
      public bool IsHide { get; set; }
}

优化方案：单联合查询实现 #

可以通过LINQ的join操作直接关联三张表，将三次查询合并为一次数据库请求，同时直接映射到目标DTO，减少数据库访问次数与内存开销。

方式1：显式Join写法 #

var userMenus = await (from u in _userManager.Users
                       join mp in _context.MenuPermissions on u.Id equals mp.UserId
                       join m in _context.Menus on mp.MenuId equals m.Id
                       where u.Email == request.Email && mp.IsHide == false
                       select new GetMenuForUserDto
                       {
                           Id = m.Id,
                           EngName = m.EngName,
                           Title = m.Title,
                           Rank = m.Rank,
                           ParentId = m.ParentId,
                           RoutePath = m.RoutePath,
                           IsHide = m.IsHide,
                           Icon = m.Icon,
                           Type = m.Type,
                           MenuPermissions = new GetMenuPermissionsDto
                           {
                               Id = mp.Id,
                               UserId = mp.UserId,
                               MenuId = mp.MenuId,
                               Add = mp.Add,
                               Edit = mp.Edit,
                               View = mp.View,
                               Delete = mp.Delete,
                               IsHide = mp.IsHide
                           }
                       }).ToListAsync();

方式2：利用导航属性简化写法（需实体配置关联） #

如果你的实体类已经配置了导航属性（比如User包含ICollection<MenuPermission> MenuPermissions，MenuPermission包含Menu Menu），可以用更简洁的写法：

var userMenus = await _userManager.Users
    .Where(u => u.Email == request.Email)
    .SelectMany(u => u.MenuPermissions.Where(mp => !mp.IsHide))
    .Select(mp => new GetMenuForUserDto
    {
        Id = mp.Menu.Id,
        EngName = mp.Menu.EngName,
        Title = mp.Menu.Title,
        Rank = mp.Menu.Rank,
        ParentId = mp.Menu.ParentId,
        RoutePath = mp.Menu.RoutePath,
        IsHide = mp.Menu.IsHide,
        Icon = mp.Menu.Icon,
        Type = mp.Menu.Type,
        MenuPermissions = new GetMenuPermissionsDto
        {
            Id = mp.Id,
            UserId = mp.UserId,
            MenuId = mp.MenuId,
            Add = mp.Add,
            Edit = mp.Edit,
            View = mp.View,
            Delete = mp.Delete,
            IsHide = mp.IsHide
        }
    }).ToListAsync();

优化说明 #

• 两种写法最终都会被EF6转换为单条SQL联合查询，仅访问数据库一次
• 直接在查询中完成DTO映射，避免中间集合的内存存储与后续转换
• 条件过滤直接嵌入查询，无需先查询用户再传递ID，逻辑更紧凑

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