Following this question: Query by Linq and Lambda getting range of options per sub-query. How do I get a closer result than simple? Where, however, my template grew to one more class (resulting in another table), the class Tela
, I'm trying to select some records following the following rules of this query SQL
:
select MR.*, M.*, T.* from Auth_MenuRaiz MR
left join Auth_Menu M on M.MenuRaizId = MR.Id
left join Auth_Tela T on T.Id = M.TelaId
where T.Id in (
select GAP.TelaId from Auth_GrupoAcessoPermissoes GAP
where GAP.GrupoAcesso = 2
)
order by MR.Ordem, M.Orde
I'm testing the Linqer program to help me understand the migration of SQL in Linq / Lambda and when I use it to convert my SQL , it returns me the following Linq command: >
from MR in CRMContext.Auth_MenuRaiz
join M in CRMContext.Auth_Menu on MR.Id equals M.MenuRaizId into M_join
from M in M_join.DefaultIfEmpty()
join T in CRMContext.Auth_Tela on M.TelaId equals Id = T.Id into T_join
from T in T_join.DefaultIfEmpty()
where
(from GAP in CRMContext.Auth_GrupoAcessoPermissoes
where
GAP.GrupoAcesso == 2
select GAP.TelaId).Contains(T.Id)
orderby
MR.Ordem,
M.Ordem
select new {
Id = MR.Id,
Descricao = MR.Descricao,
Ordem = MR.Ordem,
Column1 = (int?)M.Id,
MenuRaizId = (int?)M.MenuRaizId,
Column2 = M.Descricao,
TelaId = (int?)M.TelaId,
Column3 = (int?)M.Ordem,
Column4 = (int?)T.Id,
Url = T.Url
}
The result of this Linq test is correct, but I am now facing a problem. At first, with the EntityFramework through Lambda queries, you can load the properties of the classes using the Include command:
_context.MenuRaiz
.Include(x => x.Menus)
.Include(x => x.Menus.Select(m => m.Tela))
I can not do this with Linq, or I'm doing it wrong.
And, of course, the result obtained by the Linq query is not exactly an instance of MenuRaiz
, with its Menus
navigation property filled, and, as a result, the Tela
property is populated. Actually the Linq query returns me an anonymous object (model dynamic
) with some properties.
My question is, how do you get the same kind of result? That is, real classes built as if you were doing the query using Lambda ?