borisdj
Guid primary key is not created on bulkInsert
When inserting my entities with EfCore.BulkExtension using bulkInsert , instead of skipping the primary id key on insert and leaving the DB to generate the Guid id by its self, it sends an empty Guid id with the insert statement,(primary key with default newSequentialId()).
`var salaryList = new SalaryList
{
BankId = id,
Mdaf3 = salaryListsPostDto.Mdaf3,
Mtaswiya = salaryListsPostDto.Mtaswiya,
Dates = salaryListsPostDto.Dates.Select(i => new SalaryListDate { Date = i }).ToList(),
FormTypeId = typeId,
Cheques = new List<Cheque>()
};`
//generating the cheques
//salaries were retrieved from the DB, but removed for brevity
foreach (var salary in salaries)
{
var itemSalary = new ChequeSalaryItem
{
Amount = salary.Salary,
SalaryDate = date
};
//check if beneficiary has another month for salary in the cheques, then append the salary to the cheque
var cheque = salaryList.Cheques.FirstOrDefault(i => i.BeneficiaryId == salary.BeneficiaryId);
if (cheque != null)
cheque.SalaryItems.Add(itemSalary);
//else if it doesn't have, just create a new cheque
else
{
cheque = new Cheque
{
BeneficiaryId = salary.BeneficiaryId,
StatusId = MolsaConstants.ChequePaidStatus,
SalaryItems = new List<ChequeSalaryItem>
{
new ChequeSalaryItem
{
Amount = salary.Salary,
SalaryDate = date
}
}
};
salaryList.Cheques.Add(cheque);
}
}
await _context.BulkInsertAsync(new List<SalaryList> { salaryList }, c => c.IncludeGraph = true);
I want to use includeGraph to save my single entity salartList with its thousands of cheques related items.
but when saving, I get the following error:
Violation of PRIMARY KEY constraint 'PK_Cheques'. Cannot insert duplicate key in object 'dbo.Cheques'. The duplicate key value is (00000000-0000-0000-0000-000000000000).
And if I remove the includeGraph(setting back to false) then only the single salaryList gets inserted with empty Guid id. I don't want to generate the Guid id value in the code as they are default sewsequentialId() in the DB.
I have used Pure ef core and entity-framework extension(Paid) and both worked perfectly. So is this a limitation of this library or am I missing something?
Thanks for helping.
When having PKs with default values you should use it without IncludeGraph in which case you need to call BulkInsert(s) separately with lists for each table. Example of default value PK is in ComputedAndDefaultValuesTest. And as multiple tables take a look at ReadMe segment parent-child FK related tables // Option 1.
@borisdj thanks for the response, I need to reopen the issue as the problem is still not solved. Firstly, I have updated to v5.3.2 just now, and it's still the same, even when inserting only one single entity(salaryList) with no using includeGraph, but still it explicitly set the Guid PK as (00000000-0000-0000-0000-000000000000) instead of not passing the value and let the DB to generate the sequential GUID.
secondly, I have tried to use the includeGraph but I get the merge statement conflict error between child and parent RS.
the MERGE statement conflicted with the FOREIGN KEY constraint "FK_Cheques_SalaryLists_SalaryListId". The conflict occurred in database "Test", table "dbo.SalaryLists", column 'Id'
@borisdj well the project setup is quite complex and it's not easy to set up a minimum reproducible project. but I think my problem is quite straightforward, I have tested with int PK and it was inserted successfully.
So the problem is EFCore.BulkExtensions's bulkInsert method sends the actual Guid's value as the Guid PK value for the DB, I have assigned a client-generated Guid value to the Guid PK and it was inserted successfully(sends empty GUID if nothing is assigned). so the problem is that the library is not skipping the Guid PK when generating the SQL insert statement and sends the GUID's actual value from client code.
as I said, I have used Pure ef core and entity-framework extension(Paid) and both worked perfectly.so I'm sure the problem is not with me.
I think I have cleared the problem as much as possible.
I have understood your example but still am not able to reproduce the issue. I've made the following test and it passes successfully. Download the source of library and try to alter this test to make your issue reproducible.
In Context:
public DbSet<Doc> Docs { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Doc>().Property(p => p.DocId).HasDefaultValueSql("NEWSEQUENTIALID()");
}
public class Doc
{
public Guid DocId { get; set; }
public string Note { get; set; }
}
Test:
private void RunDefaultPKUpsert()
{
using (var context = new TestContext(ContextUtil.GetOptions()))
{
context.Truncate<Doc>();
context.Docs.Add(new Doc() { Note = "info" });
context.SaveChanges();
Guid firstId = context.Docs.FirstOrDefault().DocId;
var records = new List<Doc> {
new Doc() { DocId = firstId, Note = "info updated" },
new Doc() { Note = "new record" }
};
context.BulkInsertOrUpdate(records);
};
using (var context = new TestContext(ContextUtil.GetOptions()))
{
Assert.Equal("info updated", context.Docs.FirstOrDefault().Note);
};
}
@borisdj Thanks for the clear-up, but your ID PK is long, my problem was with the GUID identity that has DB default value (newsequencialId()). I'm now creating a test repo demonstrating the problem.
My mistake, was looking at another issue. I have edited previous post with correct test, still passes.
@borisdj one thing I have discovered during my testing was if parent Guid id has been assigned a client-generated value, then the child entities Guid id will be assigned from the DB, otherwise, I will get the merge conflict statement between child and parent.
@borisdj thanks for the fix, I really appreciate your great contribution! <3
the Default Guid value is now solved BUT ONLY if includeGraph is not specified, If includeGraph is specified then the merge conflict occurs,as seen below:
The MERGE statement conflicted with the FOREIGN KEY constraint "FK_Student_Department_DepartmentId". The conflict occurred in database "EFCoreBulkTest", table "dbo.Department", column 'Id'. The statement has been terminated.
in my test Repo, I wrote two insert methods in the Controller, first one with includeGraph specified, this gives the merge conflict error(pull again my my repo, then just debug the project the browsers opens and it will run that method), the second one, with no includeGraph is specified, it passes the test and the Guids are generated by DB.
For now use it separately with each list as suggested in one of previous posts.
For now use it separately with each list as suggested in one of the previous posts.
it's not possible (or not easy straightforward) to do it with each list separately, and I don't like to do it with three transactions, my hierarchy list is 3 levels. which is why I intend only to do it with a graph.
v5.3.4 should now work using Graph as well.
that is so great, I just saw your 5.3.4 commit fix, I'm waiting for the nugget update
@borisdj I can't thank you enough for this <3 really appreciate your effort and time. I'm glad that I was able to contribute to solve such a problem ^_^
This seems to have regressed, trying to use BulkDeleteAsync with a list of objects with non-nullable primary key GUID not set (database default is (newsequentialid()) it fails with primary key conflicts The duplicate key value is (00000000-0000-0000-0000-000000000000). Using MSSQL 2022.
This issue is happening again. @timmac-qmc were you able to fix it?
@borisdj were there any reversion on this fix in 6.+ version?
I'm trying to BulkInsert and it sets the guid id as 00000000-0000-0000-0000-000000000000 instead of letting database generate the guid (using postgres).
This issue is happening again. @timmac-qmc were you able to fix it?
@borisdj were there any reversion on this fix in 6.+ version?
I'm trying to BulkInsert and it sets the guid id as 00000000-0000-0000-0000-000000000000 instead of letting database generate the guid (using postgres).
Nope, I gave up and stopped using the library. The dev doesn't appears to have any interest in fixing it.
@ps91 can you or someone make a complete test for reproduction of the issue.