Query by Linq and Lambda getting range of options by sub-query. How to get a result closer to the 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<Menu> 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<GrupoAcessoPermissao> GruposAcessoPermissao { get; set; }
}

Permission Login Group :

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; }
}

Generating the following scheme:

Iwanttocreateansqlquerythat,Ibelieve,wouldbethatway,andthatbringstheresultsIhopetoget:

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?

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 wordy.

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

    

Still not what I expected.

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

First of all, I’ve made the Id of the menus that I’m going to need

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

Soon after, I do the grouping. Here, even without using Include() I can get data from _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 on a better way to do it.

    

Dude, you can start your search from the Permission Group. For example:

var menuRaiz = _context.GruposAcessoPermissao
    .Where(g => g.GrupoAcesso == user.GrupoAcesso)
    .Select(x => x.Menu.MenuRaiz)