CODEWITHSUNDEEP
c#sqllinqentity-frameworktime
Yulian
Yulian

Reputation: 6769

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

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?

Upvotes: 7

Views: 5082

Answers (2)

Kevin
Kevin

Reputation: 4636

OK I got Skeeted but to add to the conversation and a bit that may be useful to others...

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

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

Upvotes: 15

Jon Skeet
Jon Skeet

Reputation: 1501656

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.

Upvotes: 16

Related Questions