tursodatabase/turso · error · NotSupportedException

SQLite does not support expressions of type '{type}' in ORDE

Error message

SQLite does not support expressions of type '{type}' in ORDER BY clauses. Convert the values to a supported type, or use LINQ to Objects to order the results on the client side.

What it means

The Turso EF Core SQLite provider overrides TranslateOrderBy and TranslateThenBy: after the base translator plans the ordering, it inspects the last ordering expression's provider type and throws NotSupportedException for DateTimeOffset, TimeSpan, and ulong keys (via SqliteStrings.OrderByNotSupported). SQLite has no total ordering for those storage encodings, matching the behavior of Microsoft's Sqlite provider.

Source

Thrown at bindings/dotnet/src/Turso.EntityFrameworkCore.Sqlite/Query/Internal/TursoSqliteQueryableMethodTranslatingExpressionVisitor.cs:187

    /// </summary>
    protected override ShapedQueryExpression? TranslateThenBy(
        ShapedQueryExpression source,
        LambdaExpression keySelector,
        bool ascending)
    {
        var translation = base.TranslateThenBy(source, keySelector, ascending);
        if (translation == null)
        {
            return null;
        }

        var orderingExpression = ((SelectExpression)translation.QueryExpression).Orderings.Last();
        var orderingExpressionType = GetProviderType(orderingExpression.Expression);
        if (orderingExpressionType == typeof(DateTimeOffset)
            || orderingExpressionType == typeof(TimeSpan)
            || orderingExpressionType == typeof(ulong))
        {
            throw new NotSupportedException(
                SqliteStrings.OrderByNotSupported(orderingExpressionType.ShortDisplayName()));
        }

        return translation;
    }

    /// <summary>
    ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to
    ///     the same compatibility standards as public APIs. It may be changed or removed without notice in
    ///     any release. You should only use it directly in your code with extreme caution and knowing that
    ///     doing so can result in application failures when updating to a new Entity Framework Core release.
    /// </summary>
    protected override ShapedQueryExpression? TranslateCount(ShapedQueryExpression source, LambdaExpression? predicate)
    {
        // Simplify x.Array.Count() => json_array_length(x.Array) instead of SELECT COUNT(*) FROM json_each(x.Array)
        if (predicate is null
            && source.QueryExpression is SelectExpression
            {

View on GitHub (pinned to 244cde92a7)

Solutions

  1. Map ulong keys to long in the model (.HasConversion<long>()) since SQLite stores them as 8-byte integers anyway.
  2. Replace DateTimeOffset ordering with an orderable representation: order by the DateTime component, or store the instant as TEXT/long via a value converter and order on that column.
  3. Convert TimeSpan to a long (ticks) with a value converter so ordering happens server-side.
  4. Fall back to client-side ordering: bring data in with .AsEnumerable() (or .ToListAsync()) and apply .OrderBy(...) in memory, per the guidance in the message itself.

Example fix

// before
var recent = db.Orders.OrderByDescending(o => o.CreatedAtOffset).ToList(); // DateTimeOffset key -> NotSupportedException

// after (server-side: order on converted long ticks)
modelBuilder.Entity<Order>().Property(o => o.CreatedAtOffset)
    .HasConversion(d => d.UtcTicks, ticks => new DateTimeOffset(ticks, TimeSpan.Zero));
var recent = db.Orders.OrderByDescending(o => o.CreatedAtOffset).ToList();

// after (client-side fallback)
var recent = db.Orders.AsEnumerable().OrderByDescending(o => o.CreatedAtOffset).ToList();
Defensive patterns

Strategy: fallback

Validate before calling

// audit the model up front for unorderable key types
var unorderable = model.GetEntityTypes()
    .SelectMany(e => e.GetProperties())
    .Where(p => p.ClrType is Type t && (t == typeof(DateTimeOffset) || t == typeof(TimeSpan) || t == typeof(ulong)));
foreach (var p in unorderable) Console.WriteLine($"{p.DeclaringType.DisplayName()}.{p.Name} cannot be used in OrderBy");

Type guard

static bool IsServerOrderable(Type t) => t != typeof(DateTimeOffset) && t != typeof(TimeSpan) && t != typeof(ulong);

Try / catch

try { var page = query.OrderBy(k => k.Key).ToList(); }
catch (NotSupportedException ex) when (ex.Message.Contains("ORDER BY")) { var page = query.ToList().OrderBy(k => k.Key).ToList(); }

Prevention

When it happens

Trigger: Any LINQ query that reaches SQL translation with OrderBy/OrderByDescending/ThenBy on a property mapped to DateTimeOffset, TimeSpan, or ulong — including nested ThenBy chains, which hit the TranslateThenBy copy of the check.

Common situations: Models ported from the SQL Server or PostgreSQL provider where ulong keys or DateTimeOffset columns are common; entities with TimeSpan properties used for sorting; switching a working EF model to the Turso/SQLite provider and hitting the limitation for the first time.

Related errors

AI-assisted analysis of tursodatabase/turso@244cde92a7 (2026-08-20). Data as JSON: /api/errors/260e040e0d13a441. Report an issue: GitHub.