{"record":{"id":"260e040e0d13a441","repo":"tursodatabase/turso","slug":"sqlite-does-not-support-expressions-of-type-type","errorCode":null,"errorMessage":"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.","messagePattern":"SQLite does not support expressions of 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\\.","errorType":"exception","errorClass":"NotSupportedException","httpStatus":null,"severity":"error","filePath":"bindings/dotnet/src/Turso.EntityFrameworkCore.Sqlite/Query/Internal/TursoSqliteQueryableMethodTranslatingExpressionVisitor.cs","lineNumber":187,"sourceCode":"    /// </summary>\n    protected override ShapedQueryExpression? TranslateThenBy(\n        ShapedQueryExpression source,\n        LambdaExpression keySelector,\n        bool ascending)\n    {\n        var translation = base.TranslateThenBy(source, keySelector, ascending);\n        if (translation == null)\n        {\n            return null;\n        }\n\n        var orderingExpression = ((SelectExpression)translation.QueryExpression).Orderings.Last();\n        var orderingExpressionType = GetProviderType(orderingExpression.Expression);\n        if (orderingExpressionType == typeof(DateTimeOffset)\n            || orderingExpressionType == typeof(TimeSpan)\n            || orderingExpressionType == typeof(ulong))\n        {\n            throw new NotSupportedException(\n                SqliteStrings.OrderByNotSupported(orderingExpressionType.ShortDisplayName()));\n        }\n\n        return translation;\n    }\n\n    /// <summary>\n    ///     This is an internal API that supports the Entity Framework Core infrastructure and not subject to\n    ///     the same compatibility standards as public APIs. It may be changed or removed without notice in\n    ///     any release. You should only use it directly in your code with extreme caution and knowing that\n    ///     doing so can result in application failures when updating to a new Entity Framework Core release.\n    /// </summary>\n    protected override ShapedQueryExpression? TranslateCount(ShapedQueryExpression source, LambdaExpression? predicate)\n    {\n        // Simplify x.Array.Count() => json_array_length(x.Array) instead of SELECT COUNT(*) FROM json_each(x.Array)\n        if (predicate is null\n            && source.QueryExpression is SelectExpression\n            {","sourceCodeStart":169,"sourceCodeEnd":205,"githubUrl":"https://github.com/tursodatabase/turso/blob/244cde92a7df7f9b8b8b7a4075c35a12977e303e/bindings/dotnet/src/Turso.EntityFrameworkCore.Sqlite/Query/Internal/TursoSqliteQueryableMethodTranslatingExpressionVisitor.cs#L169-L205","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Map ulong keys to long in the model (.HasConversion<long>()) since SQLite stores them as 8-byte integers anyway.","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.","Convert TimeSpan to a long (ticks) with a value converter so ordering happens server-side.","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."],"exampleFix":"// before\nvar recent = db.Orders.OrderByDescending(o => o.CreatedAtOffset).ToList(); // DateTimeOffset key -> NotSupportedException\n\n// after (server-side: order on converted long ticks)\nmodelBuilder.Entity<Order>().Property(o => o.CreatedAtOffset)\n    .HasConversion(d => d.UtcTicks, ticks => new DateTimeOffset(ticks, TimeSpan.Zero));\nvar recent = db.Orders.OrderByDescending(o => o.CreatedAtOffset).ToList();\n\n// after (client-side fallback)\nvar recent = db.Orders.AsEnumerable().OrderByDescending(o => o.CreatedAtOffset).ToList();","handlingStrategy":"fallback","validationCode":"// audit the model up front for unorderable key types\nvar unorderable = model.GetEntityTypes()\n    .SelectMany(e => e.GetProperties())\n    .Where(p => p.ClrType is Type t && (t == typeof(DateTimeOffset) || t == typeof(TimeSpan) || t == typeof(ulong)));\nforeach (var p in unorderable) Console.WriteLine($\"{p.DeclaringType.DisplayName()}.{p.Name} cannot be used in OrderBy\");","typeGuard":"static bool IsServerOrderable(Type t) => t != typeof(DateTimeOffset) && t != typeof(TimeSpan) && t != typeof(ulong);","tryCatchPattern":"try { var page = query.OrderBy(k => k.Key).ToList(); }\ncatch (NotSupportedException ex) when (ex.Message.Contains(\"ORDER BY\")) { var page = query.ToList().OrderBy(k => k.Key).ToList(); }","preventionTips":["Map ulong keys with HasConversion<long> at model build time.","Convert DateTimeOffset/TimeSpan to ticks or ISO text for server-side ordering.","Keep a checklist of SQLite-unsupported types when porting models from SQL Server or PostgreSQL."],"tags":["dotnet","ef-core","linq-translation","orderby","sqlite-compat"],"backgroundTag":"efcore-query-translation-not-supported","analyzedSha":"244cde92a7df7f9b8b8b7a4075c35a12977e303e","analyzedAt":"2026-08-20T07:02:18.389Z","contentChangedAt":"2026-08-20T07:02:18.389Z","schemaVersion":2},"datasetVersion":"2026-09-14T00:17:10.932Z"}