How to deal with dates in Linq Query?

I'm getting a NotSupportedException:

Method 'System.TimeSpan FromMinutes(Double)' has no supported translation to SQL.

When running this query:

var searchSchedules =
    from searchSchedule in db.SearchSchedules
    where searchSchedule.Active.Value &&
          searchSchedule.LastChecked.Value <
            DateTime.UtcNow.Subtract(TimeSpan.FromMinutes(
              searchSched开发者_StackOverflowule.CheckFrequencyMinutes)) &&
          searchSchedule.PublishingConfigurationId == pubConfig.Id
          select searchSchedule;

Obviously the problem is with the TimeSpan part of the query. How can I make this work?

If you are just trying to check whether a specified number of minutes has passed since the last check you can rewrite like this:

var searchSchedules =
    from searchSchedule in db.SearchSchedules
    where searchSchedule.Active.Value &&
            ((DateTime.UtcNow - searchSchedule.LastChecked.Value).Minutes >= searchSchedule.CheckFrequencyMinutes) &&
          searchSchedule.PublishingConfigurationId == pubConfig.Id
          select searchSchedule;

One way to do this is to break this query up into two pieces. One piece executes against the database and the second piece executes on the client, as follows:

// This executes against the database
var query1 = from searchSchedule in db.SearchSchedules
             where searchSchedule.Active.Value
             && searchSchedule.PublishingConfigurationId == pubConfig.Id
             select searchSchedule;

// This executes on the client
var query2 = from query1 as IEnumerable()
             where query1.LastChecked.Value < DateTime.UtcNow.Subtract(TimeSpan.FromMinutes(query1.CheckFrequencyMinutes))

I often do queries like this, when I want to include a function in a query that SQL Server wouldn't know how to handle. In the second query, converting query1 to IEnumerable will force it to execute on the client, against the .NET framework, instead of your database.

You will need to determine how many rows might need to be processed on the client to determine how efficient this might be.