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?

http://stackoverflow.com/questions/21287730/linq-to-sql-compare-time-only — Donal, Aug 18 '14 at 14:22

score 16 · Answer 1 · answered Aug 18 '14 at 14:27

16

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.

answered Aug 18 '14 at 14:27

Jon Skeet

1,421,763
867
9,128
9,194

1

Thank you very much for the answer! However, I marked Kevin's as accepted because his answer does exactly what I wanted. By the way, as your answer is also correct, I voted it up. Hope you don't get mad at me ;) Thanks again! – Yulian Aug 18 '14 at 15:04
3

@Julian: I would argue that my solution is clearer - and *may* be significantly more efficient, too... you should check the query plan. I don't mind whose answer you accept, but I think it's worth thinking about the best way of expressing the query you want... – Jon Skeet Aug 18 '14 at 15:10

Kevin · Accepted Answer · 2014-08-18T15:46:51.663

15

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

edited Aug 18 '14 at 15:46

answered Aug 18 '14 at 14:31

Kevin

4,586
23
35

I would only use `DbFunctions` when you are comparing two `datetime` columns, instead of one column and one passed in value – Aducci Aug 18 '14 at 14:35
1

Actually, I get exactly the result I wanted. I created some Publication entities the date of creation of which is 60-70 or 120-130 minutes from now and they are not part of the set. By the way, you should change the places of the two dates like so DbFunctions.DiffMinutes(p.CreationDate, DateTime.Now) – Yulian Aug 18 '14 at 15:01

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

2 Answers2