"Where" clause in linq to sql query using DateTime?

Question

I'm trying to run a query of a table with the columns Domain, LastUsed, and FreqInHours In c#.

I just want to return all the Domains that I need to crawl.I find this out by checking the datetime that they were last Crawled (LastUsed) and how frequently they should be crawled (ex. every 6 hours). If the current date/time - the time it was last crawled is greater than the frequency I add want to return that domain.

Here is the current query I've written:

var query = (from c in context.SitemapFreqs
             where (DateTime.Now - c.LastUsed).TotalHours > c.Freq
             select c.domain);

Here is the exception I'm being given:

LINQ to Entities does not recognize the method 'System.DateTime ? ToDateTime(System.Object)' method, and this method cannot be translated into a store expression.

Any help would be really appreciated.

juharr · Accepted Answer · 2017-07-24 20:24:31Z

3

You can use DbFunctions class and method DiffHours.

Here is an example:

var query = (from c in context.SitemapFreqs
             where DbFunctions.DiffHours(DateTime.Now,c.LastUsed) > c.Freq
             select c.domain);

Here is the documentation. Hope it helps.

edited Jul 24, 2017 at 20:24

juharr

32.4k4 gold badges60 silver badges95 bronze badges

answered Jul 24, 2017 at 20:23

Josue Martinez

4365 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cameron Raymond Over a year ago

Switch the DateTime.Now and the c.LastUsed and that's exactly it. Thanks a lot!

Josue Martinez Over a year ago

you are right @CameronRaymond. You are always welcome

Dave · Accepted Answer · 2017-07-24 20:29:50Z

0

Complex DateTime stuff is a bit much for Linq2SQL to handle.

If it's a relatively small amount of data, load it all into memory first:

var query = (from c in context.SitemapFreqs.ToList()
             where (DateTime.Now - c.LastUsed).TotalHours > c.Freq
             select c.domain);

If it's a larger amount of data, you can use DbFunctions, or provide the query yourself.

context.SitemapFreqs.SqlQuery("SELECT * from SitemapFreqs WHERE DATEDIFF('hour', GETDATE(), LastUsed) > Freq")

If you make sure the query returns the columns the SitemapFreqs object expects, it will map the objects just like it would anything else.

answered Jul 24, 2017 at 20:29

Dave

4,4223 gold badges26 silver badges30 bronze badges

Comments

Ryan C · Accepted Answer · 2017-07-24 20:29:55Z

0

Looks like your c.LastUsed property is nullable. You can subtract nullable DateTimes using the c.LastUsed.Value property, but you should know that if it is null, this will throw an exception as you can't subtract a DateTime - null. I believe you have two options:

Change the property LastUsed in your class to a non-nullable DateTime by removing the ?.
Create a method inside of your class that determines if the DateTime? LastUsed is equal to null. If it is, return something where your LINQ query will ignore that value. (I.E: Set the value of LastUsed = DateTime.Now so that your LINQ query comes back as 0).

Hope this helps.

answered Jul 24, 2017 at 20:29

Ryan C

5825 silver badges18 bronze badges

Collectives™ on Stack Overflow

"Where" clause in linq to sql query using DateTime?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related