This is my query in direct sql.
select c.de_cnpj, c.DT_TransacaoV from T_CRM_StatusPDV c
join T_PDV p on c.DE_CNPJ = p.CNPJ
where DATEDIFF(DAY,c.DT_TransacaoV,GETDATE()) > 45
This is my lambda expression, which in my view corresponds to this query.
var resultado = db.T_CRM_StatusPDV
.Join(db.T_PDV, t1 => t1.DE_Cnpj, t2 => t2.CNPJ, (t1, t2) => new { t1, t2})
.Where(dt => DbFunctions.DiffDays(dt.t1.DT_TransacaoV, DateTime.Now) > 45
&& dt.t2.DataCadastro >= dataInicio
&& dt.t1.DT_ControleV >= dataControle)
.Select(i => new { i.t1.DE_Cnpj }).ToList();
It happens that the query brings me more than 100 records, and with lambda, Count
brings zero.