How to return concrete classes, with filled navigation properties, through Linq queries?

4

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 ?

    
asked by anonymous 23.10.2014 / 18:44

1 answer

2

Doing this:

select MR.*, M.*, T.* from Auth_MenuRaiz MR

Or this:

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
}

You are creating an anonymous object, so dynamic .

If it is your wish to return a typed object, the correct one would be to create this object (in my view, a ViewModel ) and then do the assignment.

About Include , is a method implemented only in the Entity Framework for the syntax called Extension Methods . LINQ is not equivalent to this approach: it can even act as an envelope for queries, but it does not have all the functionality implemented.

The% of LINQ% is not equivalent to join . Although the result may be very similar the approach is different.

    
23.10.2014 / 18:58