I need to import some data (tenants, users, organzation etc) from legacy system. and i want keep the id column with the same value. so i need to set the id value explictly.
await dbContext.Database.ExecuteSqlCommandAsync("SET IDENTITY_INSERT dbo.AbpTenants ON");
using (var uow = _unitOfWorkManager.Begin(TransactionScopeOption.RequiresNew))
{
var legacyTenants = await _legacySysTenantRepository.GetAll().ToListAsync();
foreach (var legacy in legacyTenants)
{
var tenantId = await _tenantManager.CreateWithAdminUserAsync(
tenancyName: legacy.BusinessCode,
name: legacy.TenantName,
adminEmailAddress: $"admin@{legacy.TenantDomain}",
adminPassword: $"xxxxxxxxxx",
connectionString: null,
isActive: legacy.Enabled == 0,
editionId: 1,
shouldChangePasswordOnNextLogin: false,
sendActivationEmail: false,
subscriptionEndDate: null,
isInTrialPeriod: false,
emailActivationLink: null,
specifiedId: legacy.Id // i changed this methods to assing Id to AbpTenants`s Id
);
}
await uow.CompleteAsync();`
await dbContext.Database.ExecuteSqlCommandAsync("SET IDENTITY_INSERT dbo.AbpTenants OFF");
but it throw exception
'
{System.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'AbpTenants' when IDENTITY_INSERT is set to OFF.
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__122_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
ClientConnectionId:30b0f3f6-84ff-4a73-afd1-0579643388c6
Error Number:544,State:1,Class:16}
'
7 Answer(s)
-
0
You can try to execute
context.Database.ExecuteSqlRaw (" SET IDENTITY_INSERT dbo.Employees ON ");in the unit of work;Restore it after the unit of work is executed.
context.Database.ExecuteSqlRaw ("SET IDENTITY_INSERT dbo.Employees OFF");see https://docs.microsoft.com/en-us/ef/core/saving/explicit-values-generated-properties#explicit-values-into-sql-server-identity-columns
-
0
@maliming it can works if separately run. but i need call aspnetzero`s method of TenanetManager.CreateWithAdminUserAsync. Becasuse some logic inside.
-
0
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.
I think you may need to modify some methods in Zero, such as
CreateWithAdminUserAsync.Ensure that
Set IDENTITY_INSERT ...is called on only one table per transaction, I haven't tried a similar situation. You can try it. If you encounter other problems, please feel free to feedback. -
0
@maliming
I modifyied some code of CreateWithAdminUserAsync
using (var uow = _unitOfWorkManager.Begin(TransactionScopeOption.RequiredNew)) changed to using (var uow = _unitOfWorkManager.Begin(TransactionScopeOption.Required))It need to reuse uow.
It works now thanks.
-
0
OK. If you encounter other problems, please feel free to feedback. : )
-
0
@maliming
is there any negative effects if i change CreateWithAdminUserAsync ouw TransactionScopeOption.RequiredNew to Required ???
-
0
I don't think it will be affected by anything, you can continue to try it.
