How to compare the minute part of only TimeSpan in LINQ with objects?

Question

I have a Publication object in my model. I want to get all publications created in less than 10 minutes.

var publications = myEntities.Publications. .Where(p => p.CreationUserId == exampleId && (DateTime.Now - p.CreationDate).Minutes < 10); 

When trying to execute the above statement, I get the following exception: "The arguments to DbArithmeticExpression must be of a numeric common type." I tried to find a suitable function from the DbFunctions class, but without success. Can anyone come up with a solution?

Answer 1

OK I have Skeeted, but to add to the conversation and a little, which may be useful to others ...

The method you are looking for is DbFunctions.DiffMinutes . It gives the total number of minutes between these two values.

 var publications = myEntities.Publications. .Where(p => p.CreationUserId == exampleId && DbFunctions.DiffMinutes(p.CreationDate, DateTime.Now) < 10); 

Answer 2

Do not do arithmetic in the request - do it before the request, so you basically specify "the earliest post creation time":

 // Deliberate use of UtcNow - you should almost certainly be storing UTC, not // local time... var cutoff = DateTime.UtcNow.AddMinutes(-10); var publications = myEntities.Publications .Where(p => p.CreationUserId == exampleId && p.CreationDate >= cutoff); 

Please note that even if your original request really worked, it will not do what you wanted - it will return publications created 0-10 minutes ago, 60-70 minutes ago, 120-130 minutes ago, etc. You wanted TotalMinutes instead.