See by Linq and Lambda getting range of options by sub-query. How to get a result closer to simple?



  • Given the following classes:

    MenuRaiz:

    public class MenuRaiz 
    {
        public MenuRaiz() {
            Menus = new List<Menu>();
        }
    
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    
    [StringLength(50)]
    [Required(AllowEmptyStrings = false)]
    public string Descricao { get; set; }
    
    [Required]
    public int Ordem { get; set; }
    
    [InverseProperty("MenuRaiz")]
    public virtual List&lt;Menu&gt; Menus { get; set; }
    

    }

    Menu:

    public class Menu
    {
    public Menu() {
    GruposAcessoPermissao = new List<GrupoAcessoPermissao>();
    }
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    [Required, ForeignKey("MenuRaiz")]
    public int MenuRaizId { get; set; }
    public virtual MenuRaiz MenuRaiz { get; set; }
    
    public int Ordem { get; set; }
    
    [StringLength(250)]
    [Required(AllowEmptyStrings = false)]
    public string Url { get; set; }
    
    [InverseProperty("Menu")]
    public virtual List&lt;GrupoAcessoPermissao&gt; GruposAcessoPermissao { get; set; }
    

    }

    GroupAccess Permissible:

    public class GrupoAcessoPermissao
    {
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    [Required]
    public GrupoAcesso GrupoAcesso { get; set; }
    
    [Required, ForeignKey("Menu")]
    public int MenuId { get; set; }
    public virtual Menu Menu { get; set; }
    

    }

    Managing the following scheme:

    Esquema das tabelas de permissão

    I want to create a sql query that, I believe, would be this way, and that brings the results I hope to get:

    select MR., M. from MenuRaiz MR
    left join Menu M on M.MenuRaizId = MR.Id
    where M.Id in (
    select GAP.MenuId from GrupoAcessoPermissoes GAP
    where GAP.GrupoAcesso = @GrupoAcesso
    )
    order by MR.Ordem, M.Ordem;

    What was I trying to do?

    var menusRaiz = _context.MenuRaiz
    .Include(x => x.Menus)
    .Include(x => x.Menus.Select(m => m.GruposAcessoPermissao))
    .Where(x => x.Menus.Any(m => m.GruposAcessoPermissao
    .Any(g => g.GrupoAcesso == user.GrupoAcesso)))
    .OrderBy(x => x.Ordem)
    .ThenBy(x => x.Menus.Min(m => m.Ordem));

    And I also tried:

    var menusRaiz = _context.MenuRaiz
    .Include(x => x.Menus)
    .Include(x => x.Menus.Select(m => m.GruposAcessoPermissao))
    .Where(x => _context.GrupoAcessoPermissoes
    .Where(gap => gap.GrupoAcesso == user.GrupoAcesso)
    .Any(gap => x.Menus
    .Any(m => m.Id == gap.Id)));

    However, they do not work as expected and still very prolix.

    How to get a result closer to the mentioned sql, which brings the results as expected?



  • It's not how I expected it.

    Because I had to turn into a list and, from the format group, assemble the result I need:

    First I list the Id from the menus I'll need

    var menusId = _context.GrupoAcessoPermissoes
        .Where(x => x.GrupoAcesso == CRM.Domain.GrupoAcesso.Empresa)
        .Select(x => x.MenuId)
        .Distinct();
    

    Then I make the grouping. Here, even without using Include() I can get the data _context.Menu thanks to GroupJoin:

    var menuRaiz = _context.MenuRaiz
                .GroupJoin(
                    _context.Menus,
                    raiz => raiz.Id,
                    menu => menu.MenuRaizId,
                    (raiz, menus) => new
                    {
                        raiz = raiz,
                        menus = menus.Where(x => menusId.Contains(x.Id))
                    })
                    .ToList()  // <-- primeiro transformo em lista (carrega para a memória)
                    .Select(x => new Domain.MenuRaiz
                    {
                        Id = x.raiz.Id,
                        Descricao = x.raiz.Descricao,
                        Ordem = x.raiz.Ordem,
                        Menus = x.menus.Select(m => new Domain.Menu
                        {
                            Id = m.Id,
                            GruposAcessoPermissao = m.GruposAcessoPermissao,
                            MenuRaiz = m.MenuRaiz,
                            MenuRaizId = m.MenuRaizId,
                            Ordem = m.Ordem,
                            Url = m.Url
                        }).ToList()
                    }); // <-- depois monto o resultado da forma como esperava que fosse
    

    Finally the result is mounted.

    SELECT 
        [Project4].[Id] AS [Id], [Project4].[C1] AS [C1], [Project4].[Id1] AS [Id1], 
        [Project4].[MenuRaizId] AS [MenuRaizId], [Project4].[Ordem] AS [Ordem], 
        [Project4].[Url] AS [Url]
        FROM ( SELECT 
            [Extent1].[Id] AS [Id], [Filter4].[Id] AS [Id1], [Filter4].[MenuRaizId] AS [MenuRaizId],
            [Filter4].[Ordem] AS [Ordem], [Filter4].[Url] AS [Url],
            CASE WHEN ([Filter4].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
            FROM  [dbo].[Auth_MenuRaiz] AS [Extent1]
            OUTER APPLY  (SELECT [Project1].[Id] AS [Id], [Project1].[MenuRaizId] AS [MenuRaizId],
                                 [Project1].[Ordem] AS [Ordem], [Project1].[Url] AS [Url]
                FROM ( SELECT 
                    [Extent2].[Id] AS [Id], [Extent2].[MenuRaizId] AS [MenuRaizId], 
                    [Extent2].[Ordem] AS [Ordem], [Extent2].[Url] AS [Url]
                    FROM [dbo].[Auth_Menu] AS [Extent2]
                    WHERE [Extent1].[Id] = [Extent2].[MenuRaizId]
                )  AS [Project1]
                WHERE  EXISTS (SELECT 
                    1 AS [C1]
                    FROM ( SELECT DISTINCT 
                        [Extent3].[MenuId] AS [MenuId]
                        FROM [dbo].[Auth_GrupoAcessoPermissoes] AS [Extent3]
                        WHERE 2 =  CAST( [Extent3].[GrupoAcesso] AS int)
                    )  AS [Distinct1]
                    WHERE [Distinct1].[MenuId] = [Project1].[Id]
                ) ) AS [Filter4]
        )  AS [Project4]
        ORDER BY [Project4].[Id] ASC, [Project4].[C1] ASC
    

    I'll still be researching a better way to do it.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2