c# – Error calculating difference between dates in Entity with Linq to Entities

Question:

Before, my expression was like this:

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();

This generated this error:

DbArithmeticExpression arguments must have a numeric common type.

I changed it to this 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 have this error:

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 expression or linq to entities?

Answer:

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

About the second one, try changing EntityFunctions to DbFunctions .

Scroll to Top
AllEscort