4
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; }
}
Group activity:
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:
I want to create an sql query that, I believe, would be that 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 I was trying to?
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 function as expected and yet very long.
How to get a more approximate result to the mentioned sql, which brings the results as expected?