CODEWITHSUNDEEP
c#linqdatetime
Cameron Raymond
Cameron Raymond

Reputation: 33

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

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.

Upvotes: 0

Views: 1311

Answers (3)

Ryan C
Ryan C

Reputation: 582

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:

  1. Change the property LastUsed in your class to a non-nullable DateTime by removing the ?.
  2. 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.

Upvotes: 0

Dave
Dave

Reputation: 4412

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.

Upvotes: 0

Josue Martinez
Josue Martinez

Reputation: 436

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.

Upvotes: 3

Related Questions