Query Linq C#

Question

Good afternoon

I have the following database structure:

Tables:

-User

-Group

-Permission

And all relations N to N:

• Grupousuario

• Permissaousuario

• Permissaogrupo

I set up a query to return the user, independent of Groups and Permissions (LEFT JOIN) with Linq in C#.

Follows:

tbUsuario = (from _u in _authEntities.tb_usuario
                          join _gu in _authEntities.tb_grupo_usuario on _u.id_tb_usuario equals _gu.id_tb_usuario
                          into u
                          from usuario in u.DefaultIfEmpty()
                          join _pu in _authEntities.tb_permissao_usuario on _u.id_tb_usuario equals _pu.id_tb_usuario
                          into p
                          from permissao in p.DefaultIfEmpty()
                          where _u.login == login && _u.senha == senha
                          select _u).SingleOrDefault();

What I need is to include in this query that I already have, another LEFT JOIN, for the table of Permissaogrupo.

If it were by query SQL would be simple, but with LINQ I still can’t find the solution. I am waiting.

Answer 1

Try to do the relationships this way.

tbUsuario = (from _u in _authEntities.tb_usuario
                          join _gu in _authEntities.tb_grupo_usuario on _u.id_tb_usuario equals _gu.id_tb_usuario
                          into u
                          from usuario in u.DefaultIfEmpty()
                          join _pu in _authEntities.tb_permissao_usuario on _u.id_tb_usuario equals _pu.id_tb_usuario
                          into p
                          from permissao in p.DefaultIfEmpty()

                          join g in _authEntities.tb_grupo on u.id_tb_grupo equals g.id_tb_grupo
                          into g
                          from Grupo in g.DefaultIfEmpty()

                          join per in _authEntities.tb_permissao on per.id_tb_permissao equals p.id_tb_permissao
                          into per
                          from permi in per.DefaultIfEmpty()

                          join pergru in _authEntities.tb_Permissao_Grupo on new { Grupo.id_tb_grupo, permi.id_tb_permissao } equals new { pergru.id_tb_grupo, pergru.id_tb_permissao }
                          into pergru
                          from Permissao_Grupo in pergru.DefaultIfEmpty()


                          where _u.login == login && _u.senha == senha
                          select _u).SingleOrDefault();

I did not test to see if the syntax is correct, take this as an example of how to do.