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.

Answer 1

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) 

Answer 2

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.

Answer 3

This should work:

using System.Data.Entity.SqlServer;

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