Naming conventions get applied to unmapped types when composing over SqlQuery(Raw)

[georg-jung]

If I materialize SqlQueryRaw with an unmapped type directly, naming conventions are not applied, but if I add a projection before materializing, they are. I'm not sure if the issue is with my expectations and this behaviour is by design, but lets look at an example:

Repro

using Microsoft.EntityFrameworkCore;
using System.Text.Json;

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

context.Blogs.Add(new() { AFooWithSomeName = 1 });
await context.SaveChangesAsync();

var q = context.Database.SqlQueryRaw<BlogQueryType>(@"SELECT a_foo_with_some_name as ""AnotherName"" FROM blogs");

// Use this line instead if disabling naming conventions
// var q = context.Database.SqlQueryRaw<BlogQueryType>(@"SELECT ""AFooWithSomeName"" as ""AnotherName"" FROM ""Blogs""");

var result = await q.ToListAsync();
Console.WriteLine(JsonSerializer.Serialize(result));

var q2 = q.Select(x => new BlogQueryType { AnotherName = x.AnotherName });

var result2 = await q2.ToListAsync(); // throws
Console.WriteLine(JsonSerializer.Serialize(result2));

public class ReproContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql("Host=localhost;Username=npgsql_tests;Password=npgsql_tests")
            .UseSnakeCaseNamingConvention()
            .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information, Microsoft.EntityFrameworkCore.Diagnostics.DbContextLoggerOptions.None);
}

public class Blog
{
    public int Id { get; set; }
    public int AFooWithSomeName { get; set; }
}

public class BlogQueryType
{
    public int AnotherName { get; set; }
}

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="EFCore.NamingConventions" Version="8.0.3" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="8.0.10" />
  </ItemGroup>

</Project>

Output

Executed DbCommand (130ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DROP DATABASE npgsql_tests WITH (FORCE);
Executed DbCommand (568ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE DATABASE npgsql_tests;
Executed DbCommand (19ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE blogs (
    id integer GENERATED BY DEFAULT AS IDENTITY,
    a_foo_with_some_name integer NOT NULL,
    CONSTRAINT pk_blogs PRIMARY KEY (id)
);
Executed DbCommand (47ms) [Parameters=[@p0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
INSERT INTO blogs (a_foo_with_some_name)
VALUES (@p0)
RETURNING id;
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT a_foo_with_some_name as "AnotherName" FROM blogs
[{"AnotherName":1}]
Failed executing DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT b.another_name AS "AnotherName"
FROM (
    SELECT a_foo_with_some_name as "AnotherName" FROM blogs
) AS b
An exception occurred while iterating over the results of a query for context type 'ReproContext'.
Npgsql.PostgresException (0x80004005): 42703: Spalte b.another_name existiert nicht

POSITION: 8
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
  Exception data:
    Severity: FEHLER
    SqlState: 42703
    MessageText: Spalte b.another_name existiert nicht
    Hint: Vielleicht wurde beabsichtigt, auf die Spalte »b.AnotherName« zu verweisen.
    Position: 8
    File: parse_relation.c
    Line: 3729
    Routine: errorMissingColumn

What I think is wrong with it

From an API perspective, I think a method could return IQueryable<BlogQueryEntity>. If the caller directly materializes the queryable, the implementation/raw sql would need to be different than if the caller composes over the queryable - but the implementation can not easily know which is the case.

Possible workaround?

An implementation could ensure to always itself compose over the result of SqlQueryRaw before returning it by applying something like .Select(x => new BlogQueryEntity { AnotherName = x.AnotherName }). The implementation correctness is then somewhat additionally dependend on the configured naming convention though.

Expected behaviour

Probably: Naming conventions are not applied to unmapped types at all, even if composed over.

[ajcvickers]

Note for team: this is an interesting consequence of composing over a query that uses unmapped types. I will investigate and potentially file something on the EF repo.