CODEWITHSUNDEEP
c#entity-frameworklinq
Paweł Manastyrski
Paweł Manastyrski

Reputation: 43

LINQ DateTimeOffset expression could not be translated

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.

Upvotes: 3

Views: 2708

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241868

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.

Upvotes: 3

Related Questions