-
-
Notifications
You must be signed in to change notification settings - Fork 594
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
BulkInsert using TPT (table-per-type) #493
Comments
Not out of box, but you could try to configure what you need. |
Thanks for the reply.
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. |
Pure Insert can work, but Merge is not supported. 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. |
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. |
EF 5 is now supported, and partially TPT, use v5.0.2. |
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:
So with that class I'm able to write:
So, I want to do a BulkInsert with a list of LogPersonReports but I just get errors á la:
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.
The text was updated successfully, but these errors were encountered: