`ExecuteSql*` methods should allow nullable `object?` parameters

[jekingry]

Bug description

Example:

int? value = null;

// this will not compile
dbContext.Database.ExecuteSqlRaw("UPDATE SomeTable SET Value = {0}", value);

// This will throw The current provider doesn't have a store type mapping for properties of type 'DBNull'. 
dbContext.Database.ExecuteSqlRaw("UPDATE SomeTable SET Value = {0}", (object?)value ?? DBNull.Value);

// This is the only option to get the intended behavior
dbContext.Database.ExecuteSqlRaw("UPDATE SomeTable SET Value = {0}", value!);

So there is handling for converting null values to DBNull.Value, so shouldn't parameters allow null values?

Your code

int? value = null;

// this will not compile
dbContext.Database.ExecuteSqlRaw("UPDATE SomeTable SET Value = {0}", value);

// This will throw The current provider doesn't have a store type mapping for properties of type 'DBNull'. 
dbContext.Database.ExecuteSqlRaw("UPDATE SomeTable SET Value = {0}", (object?)value ?? DBNull.Value);

// This is the only option to get the intended behavior
dbContext.Database.ExecuteSqlRaw("UPDATE SomeTable SET Value = {0}", value!);

Stack traces

Error Message: System.InvalidOperationException : The current provider doesn't have a store type mapping for properties of type 'DBN
      ull'.
      Stack Trace:
         at Microsoft.EntityFrameworkCore.Storage.RelationalTypeMappingSourceExtensions.GetMapping(IRelationalTypeMappingSource typeMappingSource, Type clrType, IModel model)
         at Microsoft.EntityFrameworkCore.Storage.Internal.RawSqlCommandBuilder.Build(String sql, IEnumerable`1 parameters, IModel model)
         at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRawAsync(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters, CancellationToken cancellationToken)   
         at IntegrationTests.ExampleTest.TestQuery() in C:\IntegrationTests\ExampleTest.cs:line 30

Verbose output

EF Core version

9.0.1

Database provider

MSSQL

Target framework

9.0.201

Operating system

Windows 11

IDE

VS Code

[roji]

Sorry it took so long to answer this.

// this will not compile
dbContext.Database.ExecuteSqlRaw("UPDATE SomeTable SET Value = {0}", value);

How so? ExecuteSqlRaw has the following signature (code):

public static int ExecuteSqlRaw(
    this DatabaseFacade databaseFacade,
    string sql,
    params object?[] parameters)

In other words, the parameters is already an object?, allowing nullable types as well. I did a quick test below and everything seems to be working.

Attempted repro

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

int? value = null;

context.Database.ExecuteSqlRaw("UPDATE Blogs SET Name = {0}", value);

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
}

As this issue has been open for a while and I can't see a problem, I'll go ahead and close it; but if you think there's still something that isn't working properly, post the relevant details back here and I'll reopen.

[Martin12350]

How about ExecuteSqlRawAsync (code)?

It compiles and works as expected, but we get the ugly warning CS8604 with "Possible null reference argument for parameter".