EF translation error when ordering by DateTime after projecting DateTimeOffset #8905

Activity

1. NielsAudoor

   added the

   Bug

   issue type on Nov 14, 2025
2. github-actions

   added

   🌶️ hot chocolate

   on Nov 14, 2025
3. NielsAudoor

   changed the title [-]EF translation error when using DateTime sorting when projecting an entity DateTimeOffset to a DateTime output type[/-] [+]EF translation error when ordering by DateTime after projecting DateTimeOffset[/+] on Nov 14, 2025
4. drebouta commented on Dec 9, 2025

   

   drebouta

   on Dec 9, 2025

   Contributor

   More actions

   Your db entity DateTimeReproduction.Data.Event uses DateTimeOffset together with sqllite, that will not work: dotnet/efcore#14082

   If you apply these adjustments:

   // OutputTypes/Event.cs
   namespace DateTimeReproduction.Api.OutputTypes;
   
   public sealed record Event
   {
       public DateTime TimestampAsDateTime { get; set; }
       public DateTimeOffset Timestamp { get; set; }
   }
   
   // Query.cs
   using DateTimeReproduction.Data;
   using HotChocolate.Data.Sorting;
   
   namespace DateTimeReproduction.Api;
   
   public class Query
   {
       [UseSorting<EventSortType>]
       public IExecutable<OutputTypes.Event> GetEvents(AppDbContext context)
       {
           var results = context.Events
               .Select(x => new OutputTypes.Event
               {
                   Timestamp = x.Timestamp,
                   TimestampAsDateTime = x.Timestamp.DateTime
               });
   
           return results.AsExecutable();
       }
   }
   
   
   public class EventSortType : SortInputType<OutputTypes.Event>
   {
       protected override void Configure(ISortInputTypeDescriptor<OutputTypes.Event> descriptor)
       {
           descriptor.BindFieldsExplicitly();
           descriptor.Field(f => f.Timestamp);
           descriptor.Field(f => f.TimestampAsDateTime);
       }
   }

   And execute these queries:

   query eventsSortedByTimestampAsDateTime { events(order: [ { timestampAsDateTime: DESC }]){ timestamp timestampAsDateTime } }
   
   query eventsSortedByTimestamp { events(order: [ { timestamp: DESC }]){ timestamp timestampAsDateTime } }
   

   You will receive a more descriptive error message for the second query eventsSortedByTimestamp instead:
   SQLite does not support expressions of type 'DateTimeOffset' in ORDER BY clauses. Convert the values to a supported type, or use LINQ to Objects to order the results on the client side.

   When unsing SqlServer as dbContext provider, the query eventsSortedByTimestamp would work as expected.

   You can also test this independently of graph-ql:

   // Program.cs
   app.MapGraphQL();
   
   // Works with SQL Server, fails with SQLite
   app.MapGet("/timestamp", (AppDbContext db) => db.Events.OrderByDescending(e => e.Timestamp));
   // Fails with both SQLite and SQL Server
   app.MapGet("/timestamp-as-datetime", (AppDbContext db) => db.Events.OrderByDescending(e => e.Timestamp.DateTime));
   
   app.Run();
5. michaelstaib

   added

   Area: EntityFrameworkIssue is related to the EntityFramework integrationIssue is related to the EntityFramework integration

   on Feb 19, 2026
6. michaelstaib

   closed this as completedin #9191on Feb 24, 2026
7. glen-84 commented on Sep 23, 2026

   

   glen-84

   on Sep 23, 2026

   Member

   More actions

   @NielsAudoor @ryanbacon @VDBBjorn @huysentruitw @drebouta

   Heads-up for anyone relying on the fix from #9191: it is being reverted in #10422, planned for Hot Chocolate 16.6.7.

   With #9191, Hot Chocolate moved the sort in front of the resolver's Select projection and removed .DateTime from DateTimeOffset members. That broke other sorting scenarios: an OnAfterSortingApplied callback can no longer append ThenBy to a projected query (#10419), and some multi-field sorts fail. Where the rewrite did work, it sorted by the UTC instant of the DateTimeOffset rather than by the local date and time that the projection returns, so the order could disagree with the projected values when rows have different offsets.

   From 16.6.7, sorting is applied after the projection again, as in Hot Chocolate 15. Whether you can sort on a field projected from DateTimeOffset.DateTime then depends on whether your EF Core provider can translate that member:

   Provider	Sorting on a projected DateTimeOffset.DateTime
   SQL Server with EF Core 11	Translated to CONVERT(datetime2, ...)
   SQL Server with EF Core 8, 9, or 10	Fails with "could not be translated"
   PostgreSQL (Npgsql)	Translated
   SQLite	Fails, because EF Core cannot sort DateTimeOffset values on SQLite at all

   The workarounds are in a new troubleshooting section of the sorting docs, which goes live once #10422 is merged: https://chillicream.com/docs/hotchocolate/fetching-data/sorting#the-linq-expression-could-not-be-translated

   In short:

   • On SQL Server, upgrade to EF Core 11.
   • Project the DateTimeOffset itself instead of .DateTime.
   • On SQLite, store a DateTime instead of a DateTimeOffset, as the EF Core SQLite limitations page recommends.
   • On SQL Server with EF Core 8, 9, or 10, register the member translator from the docs. It translates DateTimeOffset.DateTime the same way EF Core 11 does.

   Drafted by Claude (Anthropic AI assistant).