date difference with linq

Question

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?

thanks

Answer 1

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

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 calculation on application side, not in database.

With EF4 you could use SqlFunctions (DateDiff):

http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions_members%28VS.100%29.aspx

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

Answer 2

Use a calculated DB field and map that. Or use SqlFunctions with EF 4 as LukLed suggested (+1).