Help with LINQ, query no . Net

Question

Good morning, guys, I could use a little help with LINQ. I am trying to perform the following query through the:

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 table items A(CAD_NFE_SAIDA) when your LEFT with the table B(CAD_NFE_SAIDA_ESTACOES) (Da EST_SEQUENCIA = 2 , e SINCRONIZADOS = 'S') do not be satisfied. So I’ll get the ID of all the notes that haven’t been synchronized on that station.

---

I tried it this way :

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, LIN does an INNER Join with table 'B' which invalidates my query.
Some help?

Answer 1

Try using the Line 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 make your Left Join with your Nfesaestacao table Defaultifempty is what makes the query make Left, and only then make your Where ... and Ands.

See the column NFESAIEST_SEQUENCIA as this in your model.

You can change the query to first take the data from the Nfesaida table to filter it and then make a NOT EXISTS with your Nfesaestacao table, see example here

Answer 2

I 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;