4
Following that question: Query by Linq and Lambda getting range of options by sub-query. How to get a result closer to simple? where, however, my model grew to one more class (resulting in another table), the class Tela
, I am trying to select some records by 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 program Linqer to help me understand the migration of consultations SQL in Linq/Lambda and by using it to convert my SQL, it returns me the following command Linq:
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 test result of this Linq is correct, but I now face a problem.
At first, with Entityframework via Lambda queries, it is possible to load class properties using the Include command:
_context.MenuRaiz
.Include(x => x.Menus)
.Include(x => x.Menus.Select(m => m.Tela))
I can’t 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 your navigation property Menus
completed and, of the same, the ownership Tela
completed. Actually the consultation Linq returns an anonymous object (template dynamic
) in some ways.
My question is, how do I get the same kind of result? That is, real classes built as if I was doing the query through Lambda?
@Gypsy omorrisonmendez, so for the Entityframework, Linq does not include such as Lambda and the Lambda doesn’t make
left join
like the Linq (I ask, in case, about loading the navigation properties)? Can I then conclude with this that for the Entityframework, at least, there is no direct way to filter the records of the browsing properties (as with aleft join
) and get concrete classes?– James
About the first question, Linq does not actually make additions, but by Expression methods (correct name for what you call lambda) it is possible to do
left join
. On the second, yes, you can filter using special clauses to filter, such asWhere()
,Any()
,All()
, and so on.– Leonel Sanches da Silva