Help with query

samuel ferreira

13y1.4kLINQ

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?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sunny Sharma13y ago
I'm glad you sorted it out :)
samuel ferreira13y ago
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 Sharma13y ago
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 :)