Good morning, I have a query to run in my database, but here in our projects we use Nhibernate as an ORM tool. I'm having difficulty passing this SQL query to a syntax used with NHibernate.
The query is down:
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 not exists clause and also a subquery to complement my query. I want your help to know how I can mount this subquery using the term 'Not Exists' in NHibernate, Linq or lambda expression sixtaxes.
Here's part of the query I'm getting:
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;
}