Batch insert with SQL_Variant fails when different variant types are present

[koenbeuk]

Batch inserts seem to fail when SQL_Variant is being used and within the batch, different types are being used as params for sql_variant.

e.g. EF core generates something like the following:

CREATE TABLE [dbo].[Foos](Value SQL_VARIANT) 

DECLARE @p1 INT = 1;
DECLARE @p2 VARCHAR  = 'two';

INSERT INTO Foos
VALUES (@p2), (@p1)

This will raise an error: Conversion failed when converting the varchar value 't' to data type int..
When P2 and P1 are reversed, things will work but only because INT is convertible to VARCHAR. Otherwise declaring both parameters as SQL_VARIANT will also work; Casting like VALUES (CAST(@p2 AS SQL_VARIANT)....) also works.

Steps to reproduce

// Given a model
public class Foo
{
    public int Id { get; set; }

    [Column(TypeName = "sql_variant")]
    public object Value { get; set; }
}

// the following will throw:
context.Foos.Add(new Foo { Id = 1, Value = "one" });
context.Foos.Add(new Foo { Id = 2, Value = 2 });
   

causes:

Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.'
>> SqlException: Error converting data type nvarchar to int.

Stack:
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(DbContext _, ValueTuple`2 parameters, 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.ChangeTracking.Internal.StateManager.SaveChangesAsync(IReadOnlyList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

Given the above code, I wonder why the insert statement does not respect the explicit TypeName when declaring the SQL parameters and instead seems to use the implicit CLR <-> SQL mapping?

Further technical details

EF Core version: 2.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 X64
IDE: (e.g. Visual Studio 2017 15.7)

[ajcvickers]

@kbekkenutte I have not been able to reproduce this. Could you take a look at my repro code below and figure out what is missing to make it demonstrate the behavior you are seeing?

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

    [Column(TypeName = "sql_variant")]
    public object Name { get; set; }
}

public class BloggingContext : DbContext
{
    private static readonly LoggerFactory _logger
        = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(_logger)
            .EnableSensitiveDataLogging()
            .UseSqlServer(
                @"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0",
                a => a.MinBatchSize(2));
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>();
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.AddRange(
                new Blog { Id = 1, Name = "Olive" },
                new Blog { Id = 2, Name = "Toast" });

            context.SaveChanges();
        }
    }
}

Relavent output:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [Blog] (
          [Id] int NOT NULL,
          [Name] sql_variant NULL,
          CONSTRAINT [PK_Blog] PRIMARY KEY ([Id])
      );

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (66ms) [Parameters=[@p0='1', @p1='Olive' (Size = 5), @p2='2', @p3='Toast' (Size = 5)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Blog] ([Id], [Name])
      VALUES (@p0, @p1),
      (@p2, @p3);

[koenbeuk]

@ajcvickers You can reproduce the issue by providing different types for different names, e.g.

 context.AddRange(
                    new Blog { Id = 1, Name = 1 },
                    new Blog { Id = 2, Name = "test" });

This generates the following SQL:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Blog] ([Id], [Name])
VALUES (@p0, @p1),
(@p2, @p3);
',N'@p0 int,@p1 int,@p2 int,@p3 nvarchar(4)',@p0=1,@p1=1,@p2=2,@p3=N'test'

p1 and p3 (both representing the name column in the example) and are of a different type which SQL Server doesn't seem to like. Declaring these parameters as sql_variant or explicitly converting them as such solves this issue.

[ajcvickers]

@kbekkenutte Thanks!