SqlQuery throws 'non-composable SQL' for update statements that returns a scalar value

[KernelCrap]

The SqlQuery method throws when executing update statements that return a scalar value. This happens on both SQL Server and PostgreSQL with the following SQL:

-- SQL Server
UPDATE Example SET Count = Count + 1 OUTPUT INSERTED.Count AS Value WHERE Id = 1

-- PostgreSQL
UPDATE "Example" SET "Count" = "Count" + 1 WHERE "Id" = 1 RETURNING "Count" AS "Value"

The above statements work when calling ToListAsync() but throws with SingleAsync(). Examples:

// This works
await context.Database
    .SqlQueryRaw<long>("UPDATE Example SET Count = Count + 1 OUTPUT INSERTED.Count AS Value WHERE Id = 1")
    .ToListAsync();

// This throws InvalidOperationException
await context.Database
    .SqlQueryRaw<long>("UPDATE Example SET Count = Count + 1 OUTPUT INSERTED.Count AS Value WHERE Id = 1")
    .SingleAsync();

Stack trace:

System.InvalidOperationException: 'FromSql' or 'SqlQuery' was called with non-composable SQL and with a query composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.
    at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.CheckComposableSqlTrimmed(ReadOnlySpan`1 sql)
    at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQuerySqlGenerator.CheckComposableSqlTrimmed(ReadOnlySpan`1 sql)
    at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.CheckComposableSql(String sql)
    at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitFromSql(FromSqlExpression fromSqlExpression)
    at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
    at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQuerySqlGenerator.VisitExtension(Expression extensionExpression)
    at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.<VisitSelect>b__21_1(TableExpressionBase e)
    at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateList[T](IReadOnlyList`1 items, Action`1 generationAction, Action`1 joinAction)
    at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
    at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateRootCommand(Expression queryExpression)
    at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(Expression queryExpression)
    at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommandTemplate(IReadOnlyDictionary`2 parameters)
    at Microsoft.EntityFrameworkCore.Internal.RelationCommandCacheExtensions.RentAndPopulateRelationalCommand(RelationalCommandCache relationalCommandCache, RelationalQueryContext queryContext)
    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

Runnable example:

using System.ComponentModel.DataAnnotations.Schema;

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

using (var context = new ExampleDbContext())
{
    await context.Database.EnsureDeletedAsync();
    await context.Database.EnsureCreatedAsync();

    context.Example.Add(new Example
    {
        Id = 1,
        Count = 0
    });

    await context.SaveChangesAsync();

    var result1 = await context.Database
        .SqlQueryRaw<long>("UPDATE Example SET Count = Count + 1 OUTPUT INSERTED.Count AS Value WHERE Id = 1")
        .ToListAsync();

    Console.WriteLine("Result = " + result1.Single());

    // InvalidOperationException: 'FromSql' or 'SqlQuery' was called with non-composable SQL ...
    var result2 = await context.Database
        .SqlQueryRaw<long>("UPDATE Example SET Count = Count + 1 OUTPUT INSERTED.Count AS Value WHERE Id = 1")
        .SingleAsync();

    Console.WriteLine("Result = " + result2);
}

public class ExampleDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=test")
            .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging();

    public DbSet<Example> Example => Set<Example>();
}

public class Example
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public long Count { get; set; }
}

Output for the example code:

...
info: 06/08/2024 10.35.55.818 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      UPDATE Example SET Count = Count + 1 OUTPUT INSERTED.Count AS Value WHERE Id = 1
Result = 1
fail: 06/08/2024 10.35.55.890 CoreEventId.QueryIterationFailed[10100] (Microsoft.EntityFrameworkCore.Query)
      An exception occurred while iterating over the results of a query for context type 'ExampleDbContext'.
      System.InvalidOperationException: 'FromSql' or 'SqlQuery' was called with non-composable SQL and with a query composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.
         at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.CheckComposableSqlTrimmed(ReadOnlySpan`1 sql)
         at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQuerySqlGenerator.CheckComposableSqlTrimmed(ReadOnlySpan`1 sql)
         at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.CheckComposableSql(String sql)
         at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitFromSql(FromSqlExpression fromSqlExpression)
         at Microsoft.EntityFrameworkCore.Query.SqlExpressionVisitor.VisitExtension(Expression extensionExpression)
         at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerQuerySqlGenerator.VisitExtension(Expression extensionExpression)
         at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.<VisitSelect>b__21_1(TableExpressionBase e)
         at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateList[T](IReadOnlyList`1 items, Action`1 generationAction, Action`1 joinAction)
         at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.VisitSelect(SelectExpression selectExpression)
         at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GenerateRootCommand(Expression queryExpression)
         at Microsoft.EntityFrameworkCore.Query.QuerySqlGenerator.GetCommand(Expression queryExpression)
         at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommandTemplate(IReadOnlyDictionary`2 parameters)
         at Microsoft.EntityFrameworkCore.Internal.RelationCommandCacheExtensions.RentAndPopulateRelationalCommand(RelationalCommandCache relationalCommandCache, RelationalQueryContext queryContext)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

EF Core version: 8.0.7 Database provider: Microsoft.EntityFrameworkCore.SqlServer (8.0.7) Target framework: NET 8.0 Operating system: Windows 11 23H2 IDE: Visual Studio 2022 17.10.5

[ErikEJ]

@KernelCrap SqlQuery is not for updates, you can use ExecuteSql or the new ExecuteUpdate.

[KernelCrap]

@ErikEJ ExecuteUpdate does not support returning a value and would require another round-trip to the database. Also, ExecuteUpdate is limited to tables defined in the DbContext.

[roji]

When you terminate your query with SingleAsync (as opposed to ToListAsync), you're asking EF to generate SQL like the following:

SELECT * FROM (INSERT INTO data (name) VALUES ('foo') RETURNING id) LIMIT 1;

But neither PostgreSQL nor SQL Server allow placing INSERT/UPDATE/DELETE statements in the subquery of a SELECT statement - hence the error message produced by EF. You can perform the Single client-side if you wish, by placing the operator after AsEnumerableAsync() or after ToListAsync().

[KernelCrap]

@roji Thanks. Now I see the underlying problem (and understand the exception). Handling it client-side is fine for our use case.

For it to work in this specific case (with Single directly), EF would have had to wrap the provided SQL in a CTE and produce something like this:

WITH x AS (
  UPDATE "Example" SET "Count" = "Count" + 1 WHERE "Id" = 1 RETURNING "Count" AS "Value"
) 
SELECT x."Value" FROM x LIMIT 1

[roji]

For it to work in this specific case (with Single directly), EF would have had to wrap the provided SQL in a CTE and produce something like this

Yep, AFAIK that does work on PostgreSQL, but not on SQL Server (am not sure about other databases). If you want, feel free to open an issue about it (though it will likely take quite a while for that to get worked on, and depends on CTE which EF does not yet support).