Search code examples
linqentity-framework

date difference with linq

With this code:

 i.SpesaAlloggio = db.TDP_NotaSpeseSezB.Sum(p => p.Costo / (((DateTime)p.DayEnd)
                    .Subtract((DateTime)p.DayStart).Days + 1));

I receive this error:

LINQ to Entities does not recognize the method 
'System.TimeSpan Subtract(System.DateTime)' method, and this method cannot be 
translated into a store expression.

How can I do this?

Solution

  • Try (it is not very efficient, but it will work):

    i.SpesaAlloggio = db.TDP_NotaSpeseSezB.ToList()
             .Sum(p => p.Costo / (((DateTime)p.DayEnd)
             .Subtract((DateTime)p.DayStart).Days + 1));

    EDIT : This will be extremely slow for large tables, because it transfers whole table content form server

    Entity Framework tries to translate your expression to SQL, but it can't handle ((DateTime)p.DayEnd).Subtract((DateTime)p.DayStart). You have to make it simpler. ToList() gets all rows and then makes the calculation on application side, not in database.

    With EF4, you could use SqlFunctions DateDiff

    With EF1, you could create calculated field or view with this field and make calculation based on this field.