LINQ DateTimeOffset expression could not be translated

Question

I have Order.cs class that contains a property:

public string OrderDate { get; set; } = DateTimeOffset.Now.ToString();

I have Order dbset. Using EF I want to get all the Orders from last 30 days. I wrote this code:

var now = DateTimeOffset.Now;
 var orders = _context.Orders.Where(o => ((now - DateTimeOffset.Parse(o.OrderDate)).TotalDays < 30)).ToList();

I get following error:

System.InvalidOperationException: The LINQ expression 'DbSet<Order>
.Where(o => (__now_0 - DateTimeOffset.Parse(o.OrderDate)).TotalDays < 30)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Any suggestions how to fix this?

EDIT

I changed the OrderDate property to

public DateTime OrderDate { get; set; } = DateTime.Now;

And the LINQ query to following:

var atm = DateTime.Now;
 var orders = _context.Orders.Where(o => (o.OrderDate - atm).TotalDays < 30);

I still get the same error.

Answer 1

First, be sure to understand how date and time handling works in PostgreSQL. You will get different results depending on which data type you use in the database, which data type you use in .NET, and whether you populate it from UTC or local time.

To reduce risk of error and ambiguity, I recommend using a DateTimeOffset and populating it from UtcNow. I also recommend picking a better name for the property (unless you really want to store just a date)

public DateTimeOffset OrderTimestamp { get; set; } = DateTimeOffset.UtcNow;

For the query, you should invert the operation. Choose the cut-off timestamp and query for orders after that.

var earliestTimestamp = DateTimeOffset.UtcNow.AddDays(-30);
var orders _context.Orders.Where(o => o.OrderTimestamp >= earliestTimestamp);

This will also have a performance benefit in that the query becomes sargable so the database can use an index.