Error when using BulkInsert/BulkUpdate on a list of TPC/TPH entities with SQLServer

[SandraS41]

Hello

We encountered an issue when using TPC inheritance with entities (with SQLServer)

We have entities like this

public class Vehicle 
{
  public string Name {get; set;}
}

public class Car : Vehicle  
{
  public double Horsepower {get; set;}
}

public class Bus: Vehicle 
{
  public int NumberOfSeats {get; set;}
  public int Capacity {get; set;}
}

public class ApplicationDContext: DbContext
{

  public DbSet<Vehicle> Vehicles => Set<Vehicle>();
  public DbSet<Car> Cars => Set<Car>();
  public DbSet<Bus> Buses => Set<Bus>();


  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Vehicle>().ToTable("Vehicles");
	modelBuilder.Entity<Car>().ToTable("Cars");
	modelBuilder.Entity<Bus>().ToTable("Buses");  
  }
}

In our case, we want to insert a list of Vehicle objects, where the elements can be either Vehicle, Car or Bus
However, when attempting to use applicationDbContext.BulkInsertAsync(elements), we encountered the following error :

Using 'FromSqlRaw' on DbSet of 'Vehicle' is not supported since 'Vehicle' is part of hierarchy and does not contain a discriminator property.

To resolve this, we added a shadow property Discriminator to all entities in ApplicationDContext

public class ApplicationDContext 
{

  public DbSet<Vehicle> Vehicles => Set<Vehicle>();
  public DbSet<Car> Cars => Set<Car>();
  public DbSet<Bus> Buses => Set<Bus>();


  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
   modelBuilder.Entity<Vehicle>(entity =>
   {
     entity.HasDiscriminator<string>("Discriminator")
      .HasValue<Vehicle>("Vehicle")
      .HasValue<Car>("Car")
      .HasValue<Bus>("Bus");
   });
  
  
    modelBuilder.Entity<Vehicle>().ToTable("Vehicles");
	modelBuilder.Entity<Car>().ToTable("Cars");
	modelBuilder.Entity<Bus>().ToTable("Buses");  
  }
}

However, this resulted in a new error:

System.AggregateException: One or more errors occurred. (Invalid column name 'Capacity'.
Invalid column name 'NumberOfSeats'. Invalid column name 'Horsepower'.) ---> Microsoft.Data.SqlClient.SqlException: Invalid column name 'Capacity'.
Invalid column name 'NumberOfSeats'. Invalid column name 'Horsepower'

The same error occurs when using TPH (e.g. removing the .ToTable lines from ApplicationDContext)

Is there any way to successfully use BulkInsert on a list in this case?

Thanks in advance for your assistance.

[SandraS41]

Hello @borisdj,
We are really stuck with this issue.
Is there a way, even locally, to work around this issue and insert a list containing elements from different hierarchy levels ?

To complete our error case, here is the specific use that is failing.

static async Task Main(string[] args)
    {
      var dbContext = new ApplicationDContext();
      await dbContext.Database.MigrateAsync();

      var vehicules = new List<Vehicle>()
      {
        new Vehicle(){ Name = "Vehicule1" },
        new Vehicle(){ Name = "Vehicule2" },
        new Car(){ Name = "Car1", Horsepower = 12 },
        new Car(){ Name = "Car2", Horsepower = 15 },
        new Bus(){ Name = "Bus1", Capacity = 10, NumberOfSeats = 20 },
        new Bus(){ Name = "Bus2", Capacity = 5, NumberOfSeats = 25 },
      };
      var bulkConfig = new BulkConfig { TrackingEntities = true, SetOutputIdentity = true, PreserveInsertOrder = true, WithHoldlock = false };

      await dbContext.BulkInsertAsync(vehicules, bulkConfig);

      await dbContext.SaveChangesAsync();
    }

[borisdj]

First you do should not call SaveChangesAsync when using BulkMethods.

Secondly you need to have 'Discriminator' column set.
One way would be to expose that property and set it to appropriate table name for each object before calling BulkMethod.
And another option, if you want it to remain Shadow property, than you need first to add list into DbSet with AddRange, which will set Discr.col. automatically, and after that call BulkInsertAsync only (do Not call SaveChangesAsync at the end)

You can take at DiscriminatorShadowTest for sample:

EFCore.BulkExtensions/EFCore.BulkExtensions.Tests/EFCoreBulkTestAtypical.cs

Lines 469 to 470 in 770f46f

	context.Students.AddRange(entitiesToInsert); // adding to Context so that Shadow property 'Discriminator' gets set
	context.BulkInsert(entitiesToInsert);