Using Elements like "Not Exists" and Sub Query in LINQ, LAMBDA EXPRESSION and Nhibernate

Question

Good Morning, I have a query to be executed in my database, however, here in our projects we use Nhibernate as a ORM tool. I’m having difficulty passing this SQL query to a syntax used with Nhibernate.

The query is this low:

SELECT distinct a.NumContrOper FROM [tb_viw_cdc_par] a inner join tb_viw_cdc_ope b on a.NumContrOper = b.NumContrOper and a.CodCliente = b.CodCliente where [TipParc] in (1,3,8,10) and not exists (select * from [tb_viw_cdc_par] par where par.[TipParc] in (2,9) and par.NumContrOper = b.NumContrOper and par.CodCliente = b.CodCliente);

Notice that in it, I use a clause not exists and also a subquery to complement my query. I want your help to know how to mount this subquery using tbm the term 'Not Exists' in the sixtax of Nhibernate, Linq or lambda Xpression.

Here’s a part of the query I’m able to assemble:

private IQueryable<string> BuscarParcFut(ParcelaAlfandegaEntidadeFiltro aFiltros)
    {
        IQueryable<OperacaoAlfandegaEntidade> operacaoAlfandega = this.Buscar<OperacaoAlfandegaEntidade>();
        List<int?> tiposNot = new List<int?>();

        tiposNot.Add(int.Parse(TipoParcelaEnumerador.ParcelaFutura.ComoString()));
        tiposNot.Add(int.Parse(TipoParcelaEnumerador.ParcelaFuturaConsorcio.ComoString()));


        List<int?> tipos = new List<int?>();
        tipos.Add(int.Parse(TipoParcelaEnumerador.ParcelaAnterior.ComoString()));
        tipos.Add(int.Parse(TipoParcelaEnumerador.PagamentoAvulso.ComoString()));
        tipos.Add(int.Parse(TipoParcelaEnumerador.ParcelaAnteriorConsorcio.ComoString()));
        tipos.Add(int.Parse(TipoParcelaEnumerador.PagamentoAvulsoConsorcio.ComoString()));

        var query = BuscarBasico(aFiltros)
            .Join(operacaoAlfandega,
                                a => new { Contrato = a.Contrato, CpfCnpj = a.CpfCnpj },
                                b => new { Contrato = b.Contrato, CpfCnpj = b.CpfCnpj },
                                (a, b) =>
                                    new
                                    {
                                        Parcela = a,
                                        Operacao = b,
                                    })
                                    .Where(w => tipos.Contains(w.Parcela.TipoParcela)
                                    )
                                    .Select(a => a.Parcela.Contrato)
                                    .Distinct()
                                    ;




        return query;


    }

Answer 1

It would be important if you included your entity and its mapping, so that from this it is easier to identify the modeling and relationships between these two entities.

This way is generating some result? You have the SQL that generated this result?

Anyway, with the information you have made available here, my suggestion is a code more or less like this (adapt only to the methods you use):

List<int?> tipos = new List<int?>();
tipos.Add(int.Parse(TipoParcelaEnumerador.ParcelaAnterior.ComoString()));
tipos.Add(int.Parse(TipoParcelaEnumerador.PagamentoAvulso.ComoString()));
tipos.Add(int.Parse(TipoParcelaEnumerador.ParcelaAnteriorConsorcio.ComoString()));
tipos.Add(int.Parse(TipoParcelaEnumerador.PagamentoAvulsoConsorcio.ComoString()));

IQueryable<Operacao> queryOperacoes = Buscar<Operacao>();

IQueryable<Parcela> queryParcelas = BuscarBasico(aFiltros)
  .Where(w => tipos.Contains(w.Parcela.TipoParcela) && 
              !queryOperacoes.Any(o => o.Contrato == w.Contrato && o.CpfCnpj == w.CpfCnpj));

var resultado = queryParcelas.Select(s => s.Parcela.Contrato).Distinct().ToList();

I removed JOIN from the code as you are not using it in Select. Include in WHERE an Any check in the Operations Iqueryable, where you will basically do the following at the end: bring me the installments that have no given transaction a contract and Cpf/cnpj. only the contract number is subsequently obtained.