CODEWITHSUNDEEP
entity-frameworklinqasp.net-corenodatime
Simon Aronson
Simon Aronson

Reputation: 19

Comparing OffSetDateTime's Date part with Today in EF/Linq with NodaTime

I am using ASP.NET core 3.1. I am trying to retrieve a list of sessions today that have finished.

I can retrieve all sessions that have finished like this:

var zonedClock = SystemClock.Instance.InTzdbSystemDefaultZone();
OffsetDateTime now = zonedClock.GetCurrentOffsetDateTime();

return _context.Session
.Where(x => (Local.Compare(x.EndDateTime, now) <0))
.Include(o => o.Tournaments)
.ToList<Session>();

where EndDateTime is a NodaTime OffsetDateTime and Local is an OffsetDateTime.Comparer. However when I attempt to also match the date like this:

return _context.Session
.Where(x => (Local.Compare(x.EndDateTime, now) <0)
 && x.StartDateTime.Date.Equals(now.Date))
.Include(o => o.Tournaments)
.ToList<Session>();

I get the following error:

InvalidOperationException: The LINQ expression 'DbSet<Session>()
.LeftJoin(
inner: DbSet<Venue>(),
outerKeySelector: s => EF.Property<Nullable<int>>(s, "VenueId"),
innerKeySelector: v => EF.Property<Nullable<int>>(v, "VenueId"),
resultSelector: (o, i) => new TransparentIdentifier<Session, Venue>(
Outer = o,
Inner = i
))
.Where(s => __Local_0.Compare(
x: s.Outer.EndDateTime,
y: __now_1) >= 0 && s.Outer.StartDateTime.Date.Equals(__now_Date_2) && s.Inner == __Venue_3 && s.Outer.Lane == __Lane_4)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, ref <>c__DisplayClass15_0 )

adding .AsEnumerable() doesn't appear to help.

Any ideas?

Upvotes: 1

Views: 304

Answers (1)

Simon Aronson
Simon Aronson

Reputation: 19

I solved this. Needed to include the tournaments table at the start, then use .AsEnumerable before the .Where then generate instances of the comparer from the static like so:

return  _context.Session
            .Include(o => o.Tournaments)
            .AsEnumerable()
            .Where(x => (OffsetDateTime.Comparer.Local.Compare(x.EndDateTime, now) <0)
             && x.StartDateTime.Date.Equals(now.Date))
            .ToList<Session>();

Upvotes: 0

Related Questions