How to compare minutes portion only of a TimeSpan in LINQ to Entities?

Question

I have a Publication entity in my model. I want to retrieve all publications that are created less than 10 minutes from now.

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

Trying to execute the above statement, I get the following exception: "DbArithmeticExpression arguments must have a numeric common type.". I tried to look for an appropriate function from the DbFunctions class, but without a success. Can anybody come up with a solution to this?

Answer 1

Don't do the arithmetic in the query - do it before the query, so that you're basically specifying an "earliest publication 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);

Note that even if your original query did work, it wouldn't do what you wanted - it would return publications created 0-10 minutes ago, 60-70 minutes ago, 120-130 minutes ago etc. You wanted TotalMinutes instead.