Help with LINQ, query in .Net

Question

Help with LINQ, query in .Net

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

Good morning, guys, I'm in need of help with LINQ. I'm trying to perform the following query through linq:

SELECT NFE_SAIDA.NFESAI_SEQUENCIA   
       FROM CAD_NFE_SAIDA NFE_SAIDA  
LEFT JOIN CAD_NFE_SAIDA_ESTACOES NFE_SAIDA_ESTACOES ON NFE_SAIDA.NFESAI_SEQUENCIA = NFE_SAIDA_ESTACOES.NFESAI_SEQUENCIA AND 
NFE_SAIDA_ESTACOES.EST_SEQUENCIA = 2 AND
NFE_SAIDA_ESTACOES.NFESAIEST_SINCRONIZADO = 'S'
WHERE NFE_SAIDA.EMP_SEQUENCIA = 1
AND NFE_SAIDA_ESTACOES.NFESAIEST_SEQUENCIA IS NULL

In summary, I need all items in the A(CAD_NFE_SAIDA) table when its LEFT with the B(CAD_NFE_SAIDA_ESTACOES) (Da EST_SEQUENCIA = 2 , e SINCRONIZADOS = 'S') not satisfied. So I'll get the ID of all the notes that have not yet been synced to that station.

I tried the following:

var query = (from _NFESAIDA in _contexto.NfeSaida.Where(_C => _C.EmpSequencia == empSequencia)
join _NFESAIDA_ESTACAO in _contexto.NfeSaidaEstacao on _NFESAIDA.NfesaiSequencia equals _NFESAIDA_ESTACAO.NfesaiSequencia into _a
from _NFESAIDA_ESTACAO in _a.DefaultIfEmpty()
.Where(_A => _A.NfesaiSincronizado != "S").DefaultIfEmpty().Where(_A => _A.EstSequencia == estSequencia).DefaultIfEmpty()
select new ObjSequenciaNota {
Sequencia = _NFESAIDA.NfesaiSequencia}
);  
return query;

However, linq does an INNER join with table 'B' which invalidates my query.
Any help?

c# entity-framework linq linq-to-entities linq-to-sql

asked by anonymous 29.12.2017 / 11:37

2 answers

1

I have decided as follows:

var queryLeft = from x in _contexto.NfeSaidaEstacao.Where(_C => _C.EstSequencia == estSequencia && _C.NfesaiSincronizada == "S") select x;

var query = from x in _contexto.NfeSaida.Where(_C => _C.EmpSequencia == empSequencia)
                        join z in queryLeft on x.NfesaiSequencia equals z.NfesaiSequencia into j1
                        from j2 in j1.DefaultIfEmpty()
                        where j2 == null
                        select (new ObjSequenciaNota { Sequencia = x.NfesaiSequencia});

return query;

21.02.2018 / 12:58

Label concatenating subsequent results, how to clear the previous result? Can you select multiple tables with the beginning of the name the same?

score 2 · Accepted Answer

Try using linq as follows.

var query = (from _NFESAIDA in _contexto.NfeSaida
             join _NFESAIDA_ESTACAO in _contexto.NfeSaidaEstacao on _NFESAIDA.NfesaiSequencia equals _NFESAIDA_ESTACAO.NfesaiSequencia into _a from _NFESAIDA_ESTACAO in _a.DefaultIfEmpty()

             where _NFESAIDA.EmpSequencia == empSequencia &&
             _NFESAIDA_ESTACAO.NfesaiSincronizado == "S" &&
             _NFESAIDA_ESTACAO.EstSequencia == estSequencia &&
             _NFESAIDA_ESTACAO.NFESAIEST_SEQUENCIA  == null /// AND NFE_SAIDA_ESTACOES.NFESAIEST_SEQUENCIA IS NULL

             select new ObjSequenciaNota
             {
                 Sequencia = _NFESAIDA.NfesaiSequencia
             }
);

First do your Left Join with your table NfeSaidaEstacao DefaultIfEmpty is what makes the query become Left, and only then do your Where ... and Ands.

See the NFESAIEST_SEQUENCY column like this in your template.

You can change the query to first get the data from the NfeSaida table to filter it and then make a NOT EXISTS with its NfeSaidaStack table, see'example here