borisdj
Q: SetOutPutIdentity does not seem to work
Hiya,
I'm currently trying to insert or update an object which contains 1:1 relations and 1:m relationships. Looking though the docs SetOutputIdentity should help me here - but it seems like it doesn't work. If anyone could do a quick scan on the code it would be appreciated.
My dto is setup like so (this is an excerpt for the question):
public class Order
{
public Guid Id { get; set; }
public long WpOrderId { get; set; }
public Domain Domain { get; set; }
public Guid DomainId { get; set; }
public List<OrderLine> OrderLines { get; set; } = new();
...
}
public class OrderLine
{
public Guid Id { get; set; }
public Guid OrderId { get; set; }
public Order Order { get; set; }
public long WpProductId { get; set; }
public long WpLineId { get; set; }
...
}
Now as far as i see i should InserOrUpdate my Order first, and then InsertOrUpdate (or in my case InsertOrUpdateOrDelete since i want orderlines that no longer exist to be removed aswell) all OrderLines.
This requires me to update the ids's and i thought of doing so via SetOutputIdentity. I'm using Guid ids so these are generated client-side - but AFAIK from reading the setting should also reload ids from the database.
I tried doing this like so (incomingOrders are order object all filled with data, incl all orderlines etc):
private async Task SyncOrders(List<Order> incomingOrders, CancellationToken cancellationToken = default)
{
// Abuse the changetracker to generate client-side ids for us.
using (var orderRepository = _orderRepository.NewScope())
{
await orderRepository.AddRangeAsync(incomingOrders, cancellationToken);
}
await UpsertEntities(incomingOrders,
_bulkRepositoryFactory,
new List<string>
{
nameof(Order.Id),
},
new List<string>
{
nameof(Order.WpOrderId),
nameof(Order.DomainId),
},
setOutputIdentity: true,
cancellationToken: cancellationToken);
}
private async Task UpsertEntities<TEntity>(List<TEntity> incomingEntities,
IBulkRepositoryFactory bulkRepositoryFactory, List<string> excludeOnUpdate, List<string> updateBy,
bool setOutputIdentity = false,
bool includeGraph = false, CancellationToken cancellationToken = default)
where TEntity : class
{
var bulkRepository = bulkRepositoryFactory.CreateRepository<TEntity>();
var bulkConfig = new BulkConfig
{
// Should help cleaning up temp tables - see https://github.com/borisdj/EFCore.BulkExtensions/issues/202
UseTempDB = true,
PropertiesToExcludeOnUpdate = excludeOnUpdate,
UpdateByProperties = updateBy,
// Sets the ids after updating in the database.
SetOutputIdentity = setOutputIdentity,
PreserveInsertOrder = true, // TODO this could help?
// Cause its cool.
CalculateStats = true,
// Run the entire chain
IncludeGraph = includeGraph
};
var bulkProgress = Logger.WriteProgressBar($"Bulk {incomingEntities.Count}");
await using var tran = await bulkRepository.StartTransactionAsync(cancellationToken);
await bulkRepository.BulkInsertOrUpdateAsync(incomingEntities, bulkConfig,
a => bulkProgress.SetValue((double)a * 100.0d), cancellationToken: cancellationToken);
await tran.CommitAsync(cancellationToken);
Logger.LogInformation("Bulk {Inserted} inserted, {Updated} updated to {EntityType}", bulkConfig.StatsInfo.StatsNumberInserted, bulkConfig.StatsInfo.StatsNumberUpdated, typeof(TEntity).Name);
}
This gives me the logging output of:
[20:34:56 INF] Bulk 0 inserted, 241 updated to Order
Meaning all entities are updated (not inserted).
I'd then expect the incomingOrders list to be filled with the entities in the databse, but these are the generated ids, and not the ones in the database.
Am i doing something wrong? Does SetOutputIdentity not work in this setup?
For complex use case like this one it's better to call BulkOps separately for each table, instead of using Graph.
For this complex use case like this one it's better to call BulkOps separately for each table, instead of using Graph.
Apologies, includeGraph is turned off. I'm trying to call seperate bulkops,
But i'm now running into an issue where SetOutputIdentity does not set the ids of the orders.
To confirm, my setup should work? Abbreviated code:
var bulkConfig = new BulkConfig
{
// Should help cleaning up temp tables - see https://github.com/borisdj/EFCore.BulkExtensions/issues/202
UseTempDB = true,
PropertiesToExcludeOnUpdate = new List<string>
{
nameof(Order.Id),
},
UpdateByProperties = new List<string>
{
nameof(Order.WpOrderId),
nameof(Order.DomainId),
},
// Sets the ids after updating in the database.
SetOutputIdentity = true,
PreserveInsertOrder = true, // TODO this could help?
// Cause its cool.
CalculateStats = true,
};
await using var tran = await bulkRepository.StartTransactionAsync(cancellationToken);
await bulkRepository.BulkInsertOrUpdateAsync(incomingEntities, bulkConfig,
null, cancellationToken: cancellationToken);
await tran.CommitAsync(cancellationToken);
This does not update the Ids in incomingEntities. I can't really figure out why - but i need the id to be able to insert the sub entities.
The ids are generated client-side, however there may already be existing orders in the databse - so i need those ids in the client - for which i was hoping SetOutputIdentity could be used for.
Could it be becaues the Id is a guid and not a database generated value or?
I'm using MSSQL btw.
This combination currently not supported directly. Workaround, would be to call BulkRead after BulkInsertOrUpdate.
//...
await bulkRepository.BulkInsertOrUpdateAsync(incomingEntities, bulkConfig);
bulkConfig.PropertiesToExclude = null;
await context.BulkReadAsync(incomingEntities, bulkConfig);
await tran.CommitAsync(cancellationToken);
This combination currently not supported directly. Workaround, would be to call BulkRead after BulkInsertOrUpdate.
//... await bulkRepository.BulkInsertOrUpdateAsync(incomingEntities, bulkConfig); bulkConfig.PropertiesToExclude = null; await context.BulkReadAsync(incomingEntities, bulkConfig); await tran.CommitAsync(cancellationToken);
Ah okay thanks for the confirmation. I'll try your workaround and i look forward to the time this would be possible :). and of course the best wishes for the new year!
I've just tried described workaround and BukReadAsync fails with:
System.InvalidOperationException
An exception was thrown while attempting to evaluate the LINQ query parameter expression 'value().Set().FromSqlRaw(value(EFCore.BulkExtensions.TableInfo+<>c__DisplayClass188_01[System.Object]).sqlQuery, new [] {}).AsNoTracking()'. See the inner exception for more information. at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.GetValue(Expression expression, String& parameterName) at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Evaluate(Expression expression, Boolean generateParameter) at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.ExtractParameters(Expression expression, Boolean clearEvaluatedValues) at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.ExtractParameters(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExtractParameters(Expression query, IParameterValues parameterValues, IDiagnosticsLogger1 logger, Boolean parameterize, Boolean generateContextAccessors)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CreateCompiledQuery[TResult](Expression query)
....
Is there any specific config that can help to resolve this?
I've just tried described workaround and BukReadAsync fails with:
System.InvalidOperationException An exception was thrown while attempting to evaluate the LINQ query parameter expression 'value().Set().FromSqlRaw(value(EFCore.BulkExtensions.TableInfo+<>c__DisplayClass188_0
1[System.Object]).sqlQuery, new [] {}).AsNoTracking()'. See the inner exception for more information. at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.GetValue(Expression expression, String& parameterName) at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Evaluate(Expression expression, Boolean generateParameter) at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.Visit(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.ExtractParameters(Expression expression, Boolean clearEvaluatedValues) at Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.ExtractParameters(Expression expression) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExtractParameters(Expression query, IParameterValues parameterValues, IDiagnosticsLogger1 logger, Boolean parameterize, Boolean generateContextAccessors) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CreateCompiledQuery[TResult](Expression query) ....Is there any specific config that can help to resolve this?
You'd need to post some more info and some more code
I do a BulkInsertOrUpdate with SetOutputIdentity = true, after the call on BulkInsertOrUpdate i see my new entity getting a Id = -12 but the actual value is Id 40 in the database. If i check the bulkConfig.TimeStampInfo.EntitiesOutput it does contain the correct entity with Id=40. Why doesnt it get set on the original object? I expected the orginal to get Id=40 instead of -12. This causes issues when i want to save the child objects because i take the id of the original object which is -12 and set that on the childs which causes a merge conflict because its a non existing Id in the Db
splitted it in a seperate BulkInsert en BulkUpdate for now as that seems to work as expected.