Error when calculating date difference in Entity with Linq to Entities

Question

Before I was like this my expression:

var resultado = db.T_CRM_StatusPDV
                              .Join(db.T_PDV, t1 => t1.DE_Cnpj, t2 => t2.CNPJ, (t1, t2) => new { t1, t2})
                              .Where(dt => (TimeSpan)((dt.t1.DT_TransacaoV - DateTime.Now)).TotalDays > 45
                                     && dt.t2.DataCadastro >= dataInicio && dt.t1.DT_ControleV >= dataControle)
                              .Select(i => new { i.t1.DE_Cnpj }).ToList();

It made that mistake:

DbArithmeticExpression arguments must have a numeric common type.

I changed it to that expression:

var resultado = db.T_CRM_StatusPDV
                              .Join(db.T_PDV, t1 => t1.DE_Cnpj, t2 => t2.CNPJ, (t1, t2) => new { t1, t2})
                              .Where(dt => EntityFunctions.DiffHours(dt.t1.DT_TransacaoV,DateTime.Now) > 45 
                                     && dt.t2.DataCadastro >= dataInicio && dt.t1.DT_ControleV >= dataControle)
                              .Select(i => new { i.t1.DE_Cnpj }).ToList();

And now I’ve made that mistake:

LINQ to Entities does not recognize the method 'System.Nullable`1[System.Int32] DiffHours(System.Nullable`1[System.DateTime], System.Nullable`1[System.DateTime])' method, and this method cannot be translated into a store expression.

How can I calculate hours in a lambda or Linq to entities expression?

Answer 1

For the first mistake, change:

.Where(dt => (TimeSpan)((dt.t1.DT_TransacaoV - DateTime.Now)).TotalDays > 45

For:

.Where(dt => DbFunctions.DiffDays(dt.t1.DT_TransacaoV, DateTime.Now) > 45

On the second, try changing EntityFunctions for DbFunctions.