Using NpgsqlParameter in SqlQuery can generate invalid query if parameter is referenced multiple times in v10

[BladeWise]

Attempting to use NpgsqlParameter with SqlQuery explicitly in v10 can generate invalid queries if the same parameter instance is referenced multiple times.
The same issue does not occur using v9.

Consider the following example:

var v = new NpgsqlParameter<int>("value", 1);
var values = await dbContext.Database.SqlQuery<int>($"""
                                                     SELECT 1
                                                     WHERE {v} >= 0 OR {v} <= 0
                                                     """).ToListAsync();

where we want to ensure parameter value is provided once and used two times in the query.

Using Npgsql.EntityFrameworkCore.PostgreSQL 10.0.0 the generated log is the following

Microsoft.EntityFrameworkCore.Database.Command: Error: Failed executing DbCommand (628ms) [Parameters=[value0='1'], CommandType='Text', CommandTimeout='30']
SELECT 1
WHERE @value >= 0 OR @value0 <= 0

as you can see, a single parameter is emitted for the command (value0), but produced SQL still references two different parameters.

The same code using Npgsql.EntityFrameworkCore.PostgreSQL 9.0.4 generates the following log

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (22ms) [Parameters=[value='1'], CommandType='Text', CommandTimeout='30']
SELECT 1
WHERE @value >= 0 OR @value <= 0

where a single parameter is provided and the same placeholder is used twice.

It seems both v9 and v10 recognize the parameter instance as unique and provide a single parameter to the SQL command, but v10 does not use the same placeholder for each usage.

A full repro is available to verify the above behavior.

[BladeWise]

Upon further inspection and debugging, the issue is not specifically related to Npgsql and likely addressed by dotnet/efcore#37430.
@roji are there any plans to include the fix in a servicing release for .NET 10? I tend to use a parameter instance explicitly to avoid to provide the same value multiple times (expecially in case it is provided as json) or wrap a value type to make explicit that a certain value is the same in multiple places of a query.

[roji]

@BladeWise can you please give this a try with the recently-released 10.0.2, which contains many fixes around duplicate parameter handling? When I looked at dotnet/efcore#37430, it seemed to be triggered specifically by using GroupBy in a very specific configuration, which your repro does not do.

[dmancini-lmt]

Thanks for the reply @roji.
The repro already uses 10.0.2, it is imported as a transient dependency through Microsoft.EntityFrameworkCore.Design 10.0.2, but to be sure I repeated the test with an explicit dependency on Microsoft.EntityFrameworkCore.Relational 10.0.2 and the issue presists.

I did notice your comment regarding the specific scenario, but as you can see my repro is quite basic, and seems to point out that explicit usage of the same parameter multiple times triggers the problem even in 10.0.2.

[roji]

Thanks, I can indeed see this happening - see minimal repro below. As this indeed probably represents a much more common scenario, I'll look at backporting dotnet/efcore#37430 to 10.0.x.

Minimal repro produced with AI

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

var v = new NpgsqlParameter<int>("value", 1);
var values = await context.Database.SqlQuery<int>($"""
    SELECT 1
    WHERE {v} >= 0 OR {v} <= 0
    """).ToListAsync();

Console.WriteLine($"Result count: {values.Count}");

public class BlogContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseNpgsql(Environment.GetEnvironmentVariable("Test__Npgsql__DefaultConnection"))
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}