DbArithmeticExpression arguments must have a numeric common type

Question

TimeSpan time24 = new TimeSpan(24, 0, 0);
TimeSpan time18 = new TimeSpan(18, 0, 0);    

// first get today's sleeping hours
List<Model.Sleep> sleeps = context.Sleeps.Where(
    o => (clientDateTime - o.ClientDateTimeStamp < time24) && 
          o.ClientDateTimeStamp.TimeOfDay > time18 && 
          clientDateTime.TimeOfDay < time18 && 
          o.UserID == userid).ToList();

This Linq expression throws this exception:

DbArithmeticExpression arguments must have a numeric common type.

Please Help!

What is the results of `clientDateTime - o.ClientDateTimeStamp`? — shahkalpesh, Mar 22 '12 at 10:30
noramlly that should be an object of the TimeSpan, in EF exception is thrown. — Nawaz Dhandala, Mar 22 '12 at 10:58

Gert Arnold · Accepted Answer · 2017-12-14T11:03:38.313

Arithmetic with DateTime is not supported in Entity Framework 6 and earlier. You have to use DbFunctions*. So, for the first part of your statement, something like:

var sleeps = context.Sleeps(o =>
    DbFunctions.DiffHours(o.ClientDateTimeStamp, clientDateTime) < 24);

Note that the DiffHours method accepts Nullable<DateTime>.

Entity Framwork core (when used with Sql Server, maybe other db providers) supports the DateTime AddXxx functions (like AddHours). They're translated into DATEADD in SQL.

_{*EntityFunctions prior to Entity Framework version 6.}

score 2 · Answer 2 · answered Jul 09 '15 at 13:41

I know that this is an old question but in your specific case instead of using DBFunctions as suggested by @GertArnold , couldn't you just invert the operation move out the arithmetic in question from the Lambda?

After all clientDateTime and time24 are fix values so their difference does not need to be recalculated in every iteration.

Like:

TimeSpan time24 = new TimeSpan(24, 0, 0);
TimeSpan time18 = new TimeSpan(18, 0, 0);    

var clientdtminus24 = clientDateTime - time24;

// first get today's sleeping hours
List<Model.Sleep> sleeps = context.Sleeps.Where(
    o => (clientdtminus24 < o.ClientDateTimeStamp) && 
          o.ClientDateTimeStamp.TimeOfDay > time18 && 
          clientDateTime.TimeOfDay < time18 && 
          o.UserID == userid).ToList();

This refactor is usually possible if you are trying to compare the stored datetime shifted by a fix timestamp with an other datetime.

I had this exact situation, and this helped. However, the scope of this solution is very limited to a specific kind of problem. — Zimano, Mar 19 '19 at 14:42
@Zimano It solves OPs problem without requiring him to change technologies or resort to hacks. If it can be refactored like this then do it, if not then do it like in the accepted answer. — vinczemarton, Mar 25 '19 at 15:10

score 1 · Answer 3 · answered Jul 11 '18 at 12:54

The other way, if performance is not the true goal, you can try using AsEnumerable(). So, it would be like

List<Model.Sleep> sleeps = context.Sleeps.AsEnumerable().Where(....

Adding AsEnumerable() will convert the SQL query to entity and allows to run .Net functions on them. For more info, check here about AsEnumerable

DbArithmeticExpression arguments must have a numeric common type

3 Answers3

Linked

Related