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

[morde]

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:

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?

[charlenef]

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.