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

Question

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?

Answer 1

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, therefore, 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 make in it the assignment.

About the 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 does not have all the implemented functionalities.

The join LINQ is not equivalent to Include. Although the result may be quite similar the approach is different.