LINQ to Entities does not recognize the method 'System.TimeSpan Subtract(System.DateTime)' method

Question

I try to select records in database in 60 days 30 days 20 days differents in current date.

Please see this query in below.

 var uploads = (
                from files in _fileuploadRepository.Table
                join product in _productRepository.Table on files.Event equals product.Id
                where
                    (
                product.EventDate != null &&
                    (product.EventDate.Subtract(DateTime.Now).Days <= 60 && product.EventDate.Subtract(DateTime.Now).Days >= 60) ||
                    (product.EventDate.Subtract(DateTime.Now).Days <= 30 && product.EventDate.Subtract(DateTime.Now).Days >= 30) ||
                    (product.EventDate.Subtract(DateTime.Now).Days <= 20 && product.EventDate.Subtract(DateTime.Now).Days >= 20))
                    &&
                files.IsSkiped == false
                select files;
            ).ToList();

But a error occurred this query.

I am clueless. Please Help.

Solution 1

The simplest approach is to work out the bounds before you perform the query:

// Only evaluate DateTime.Now once for consistency. You might want DateTime.Today instead.
DateTime now = DateTime.Now;
DateTime nowPlus60Days = now.AddDays(60);
DateTime nowPlus30Days = now.AddDays(30);
DateTime nowPlus20Days = now.AddDays(20);

var query = ...
            where product.EventDate <= nowPlus60Days
            ...

Note that your current query doesn't even really make sense, as each "or"'d clause is stating that the given computation is both less than or equal to a value and greater than or equal to the same value. If you want simple "equal to" then use that. If not, it's not clear what you are trying to do.

If you're trying to bucket the values into "less than 20", "20-30", "30-60", "more than 60" you'll need to use grouping of some form.

Solution 2

You could use the EntityFunctions.DiffDays method

EntityFunctions.DiffDays(product.EventDate, DateTime.Now) //this will return the difference in days

UPDATE

EntityFunctions is now obsolete so you should use DBFunctions instead.

System.Data.Entity.DbFunctions.DiffDays(product.EventDate, DateTime.Now)

Solution 3

This should work:

using System.Data.Entity.SqlServer;

where (int)SqlFunctions.DateDiff("day", product.EventDate, DateTime.Now) <= 60

Solution 4

To add on to scartag's answer,

The MSDN documentation for DbFunctions.DiffDays doesn't directly mention whether and when the value returned by DiffDays() will be negative, so I thought I'd provide that information here:

The result will be negative when the argument 1 date is larger than (i.e. in the future relative to) the argument 2 date.

For example, given a table Deliveries with a non-null field ScheduledDeliveryDate that can have values both in the past and in the future relative to the current date, this query will get all records with a delivery date/time within 2 days of the current date/time (both past and future):

DateTime now = DateTime.Now;
var results = from d in Deliveries
    where (DbFunctions.DiffDays(d.ScheduledDeliveryDate, now) < 2
        && DbFunctions.DiffDays(d.ScheduledDeliveryDate, now) > -2)
    select d;

View more solutions

Comments

[celerno]

EntityFunctions is now Obsolete: use System.Data.Entity.DbFunctions instead.