Help with query

Question

Hello!

I have a database with this structure:

OCORRENCIAS:

OCORRENCIA_ID
DEPARTAMENTO_ID
STATUS_ID
DATA_CRIACAO

DEPARTAMENTOS:

DEPARTAMENTO_ID
NOME

STATUS:

STATUS_ID
NOME

USUARIOS

USUARIO_ID
NOME

USUARIO_DEPARTAMENTO

USUARIO_ID
DEPARTAMENTO_ID

The Entity data model, in the Entity Departamento have a navigation property USUARIOS and in the table USUARIOS, a navigation property DEPARTAMENTOS.

I need to get only the records for the departments that the user is connected.

How can I do this?

This is my query.

var users = db.USUARIOS.FirstOfDefault();
var ocorrencias= (from r in db.OCORRENCIAS
                                 join d in db.DEPARTAMENTOS
                                 on r.DEPARTAMENTO_ID equals d.DEPARTAMENTO_ID
                                 join s in db.STATUS
                                 on r.STATUS_ID equals s.STATUS_ID
                                 select new
                                 {
                                     id = r.OCORRENCIA_ID,
                                     departamento = d,
                                     status = s.NOME,
                                     data = r.DATA_CRIACAO
                                 })
                                 .AsEnumerable();

Can someone help me?

samuel ferreira · Answer

Hello Sunny Thanks for the Reply! In my Entity data model don't have the Entity USUARIO_DEPARTAMENTOs. It created the navigation properties as mentioned... The solution I found is as follows: var users = db . USUARIOS . FirstOfDefault (); var ocorrencias = ( from r in db . OCORRENCIAS join d in db . DEPARTAMENTOS on r . DEPARTAMENTO_ID equals d . DEPARTAMENTO_ID join s in db . STATUS on r . STATUS_ID equals s . STATUS_ID where d.USUARIOS.Any(x => x.USUARIO_ID == user.USUARIO_ID) select new { id = r . OCORRENCIA_ID  departamento = d  status = s . NOME  data = r . DATA_CRIACAO }) . AsEnumerable (); The Any changed my life!

Sunny Sharma · Answer

try this: -------------------------------------------------------- var user = db.USUARIOs.AsEnumerable().FirstOrDefault(); var ocorrencias = (from r in db.OCORRENCIAs join d in db.DEPARTAMENTOs on r.DEPARTAMENTO_ID equals d.DEPARTAMENTO_ID join s in db.STATUS on r.STATUS_ID equals s.STATUS_ID join u in db.USUARIO_DEPARTAMENTOs on r.DEPARTAMENTO_ID equals u.DEPARTAMENTO_ID where u.USUARIO_ID=user.USUARIOS_ID select new { id = r.OCORRENCIA_ID departamento = d status = s.NOME data = r.DATA_CRIACAO }) .AsEnumerable(); --------------------------------------------------------- HTH :)

Sunny Sharma · Answer

I'm glad you sorted it out :)