BulkInsert using TPT (table-per-type)

[patahl]

Is it possible to do a BulkInsert if using TPT?
https://docs.microsoft.com/en-us/ef/core/modeling/inheritance#table-per-type-configuration

I've tried but came up short. We have a base log-table and derived log-tables from that.
Example:

public abstract class Log
    {
        public int LogId { get; set; }
        public int PersonId { get; set; }
        public int RegBy { get; set; }
        public DateTime CreatedDate { get; set; }

        public virtual Person Person { get; set; }
    }

 public class LogPersonReport : Log
    {
        public int ReportId { get; set; }
        public int LogPersonReportTypeId { get; set; }

        public virtual Report Report { get; set; }
        public virtual LogPersonReportType LogPersonReportType { get; set; }
    }

modelBuilder.Entity<Log>().ToTable("Logs");
modelBuilder.Entity<LogPersonReport >().ToTable("LogPersonReports");

So with that class I'm able to write:

var logPersonReport = new LogPersonReport
{
  PersonId = xx,
  LogPersonReportTypeId = xx,
  RegBy = xx,
  CreatedDate = DateTime.Now
}

So, I want to do a BulkInsert with a list of LogPersonReports but I just get errors á la:

Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP 0 T.[LogId], T.[CreatedDate], T.[PersonId], T.[RegBy], T.[LogPersonReportTypeTypeId] INTO [dbo].[LogPersonReportsTempddc62ff7] FROM [dbo].[LogPersonReports] AS T LEFT JOIN [dbo].[LogPersonReports] AS Source ON 1 = 0;
Invalid column name 'CreatedDate'.

I understand why, because CreatedDate is a member of Log but I just don't know how to work around it? I've tried to first BulkInserted the base-entities which works but not when I set negative values for LogId and use SetOutputIdentity = true so that I can use LogId for LogPersonReport. When I use SetOutputIdentity I get errors like LogPersonReportTypeId is invalid even though I specify PropertiesToInclude.

So, I'm stuck and I'm not sure it's supported so I thought I should ask before I try more.

[borisdj]

Not out of box, but you could try to configure what you need.
From Bulk perspective you should look at it like they are separate entities with a relationship.
So do the BulkInsert into first table Log with OutputIdentity and then use it to set PK with other values in list of LogPersonReport, after which on second BulkInsert set BulkConfig with PropertiesToExclude { nameof(CreatedDate), ... }

[patahl]

Thanks for the reply.

_context.BulkInsert(logPersonReportLogs.Cast<Log>().ToList(), new BulkConfig
{
    SetOutputIdentity = true,
    PropertiesToExclude = logInsertExcludedProperties
});

When I use OutputIdentity on the first BulkInsert I revieve a lot of errors about Invalid column names for derieved Log-classes (a bout 5 derived classes today but will increase later). I can manage to get it to work but then I have to exlude a lot of properties and as soon as we create another derived base class the BulkInsert will fail again until I manually add it to the excluded list which makes it hard to maintain.
I think the problem is that Log is an abstract class.

[borisdj]

Pure Insert can work, but Merge is not supported.
So this is how it can be achieved:

int nextLogId = GetLastRowId(context, tableName: nameof(Log));
var entities = new List<LogPersonReport>();
// load data into list
for (int i = 1; i <= 1000; i++)
{
  nextLogId++;
  var entity = new LogPersonReport // dummy data
  {
	LogId = nextLogId,
	PersonId = (i % 22),
	RegBy = 15,
	CreatedDate = DateTime.Now,

	ReportId = (i % 22) * 10,
	LogPersonReportTypeId = 4,
  };
  entities.Add(entity);
}

var bulkConfigBase = new BulkConfig
{
  // KeepIdentity used to ensure insert order is kept the same since SqlBulkCopy does not guarantee it.
  SqlBulkCopyOptions = SqlBulkCopyOptions.KeepIdentity,
  CustomDestinationTableName = nameof(Log),
  PropertiesToInclude = new List<string>
  {
    nameof(LogPersonReport.LogId),
    nameof(LogPersonReport.PersonId),
    nameof(LogPersonReport.RegBy),
    nameof(LogPersonReport.CreatedDate)
  }
};
var bulkConfig = new BulkConfig
{
  PropertiesToInclude = new List<string> {
    nameof(LogPersonReport.LogId),
    nameof(LogPersonReport.ReportId),
    nameof(LogPersonReport.LogPersonReportTypeId)
  }
};

context.BulkInsert(entities, bulkConfigBase, type: typeof(Log)); // to base 'Log' table
context.BulkInsert(entities, bulkConfig); // to 'LogPersonReport' table

Since SetOutputIdentity can not work as it uses Merge, we need to read last from Db and set IDs in memory for what used method:

private int GetLastRowId(DbContext context, string tableName)
{
	var sqlConnection = context.Database.GetDbConnection();
	sqlConnection.Open();
	using var command = sqlConnection.CreateCommand();
	command.CommandText = $"SELECT IDENT_CURRENT('{tableName}')";
	int lastRowIdScalar = Convert.ToInt32(command.ExecuteScalar());
	return lastRowIdScalar;
}

Alternatively we could let Db sets IDs for Log table, then read the last Id, and do CountDown to set FKs on LogPersonReport.
But there could be problem with order since SqlBulkCopy does not guarantee it, so it's better with KeepIdentity insert like in the example, if you can use that.

[patahl]

Yes, that is the workaround to get it to work but like I said, at the moment we have 5 derived log-classes with different properties and they will all have to be excluded which makes maintaining the code a hazzle, especially when we'll add a new log-class.
Shouldn't PropertiesToInclude be a better option? But atm it still includes not included properties.
I think at the moment we will use EF for our maintaining jobs containing TPT-tables and hopefully this will be handled in the future! Thanks for your support and awesome library!

[borisdj]

EF 5 is now supported, and partially TPT, use v5.0.2.
Example in previous post is updated on how to make it work, and there is a test for it: TablePerTypeInsertTest.
Also config are made with PropertiesToInclude.
You are welcome, glad it's useful.

[aceniko]

public abstract class Log
{
    public int LogId { get; set; }
    public int PersonId { get; set; }
    public int RegBy { get; set; }
    public DateTime CreatedDate { get; set; }
}

public class LogPersonReport : Log
{
    public int ReportId { get; set; }
    public int LogPersonReportTypeId { get; set; }
}

public class LogAnimalReport : Log
{
    public int ReportId { get; set; }
    public int LogAnimalReportTypeId { get; set; }
}

The LogAnimalReport class also has a ReportId property. When performing a BulkInsert operation, I encounter the following error:

System.ArgumentException: An item with the same key has already been added. Key: ReportId

The problem seems to be located within the TableInfo.LoadData method. If I skip the following code block, the BulkInsert operation succeeds:

if (entityType.IsAbstract())
{
    foreach (var derivedType in entityType.GetDirectlyDerivedTypes())
    {
        foreach (var derivedProperty in derivedType.GetProperties())
        {
            if (!allProperties.Contains(derivedProperty))
            {
                allProperties.Add(derivedProperty);
            }
        }
    }
}

Do you have any ideas on how to resolve this issue?