Insert if not Exist based on property compare and fetch identity if exists

[Gopichandar]

This is a question. I'm using SQLite DB and trying to achieve the following. Any idea how to achieve (or) nearly achieve such a case?

var customers = new List<Customer>();
customers.Add(new Customer(){Name = "John"})
customers.Add(new Customer(){Name = "Smith"})
customers.Add(new Customer(){Name = "Kayle"})

// Consider existing table has one data
//  |--------------------------|
//  |  Id      |      Name     |
//  | -------------------------|
//  |   1      |     Kayle     |
//  |--------------------------|

context.BulkInsertOrUpdate(customers, b => 
{
         b.SetOutputIdentity = true; 
         b.PropertiesToIncludeOnUpdate = new List<string> { "Name" }
}); 

//I'm expecting the following output as a response in customer;  How to achieve this? Any Idea

Console.WriteLine($"{customers[0].Id}, {customers[0].Name}");    //   2, John
Console.WriteLine($"{customers[1].Id}, {customers[1].Name}");    //   3, Smith
Console.WriteLine($"{customers[2].Id}, {customers[2].Name}");    //   1, Kayle

//Model
class Customer
{
   public int Id {get; set; }
   public int Name {get; set; }
}

[borisdj]

You don't need PropertiesToIncludeOnUpdate, just set UpdateByProperties var bulkConfig = new BulkConfig { UpdateByProperties = new List<string> { nameof(Customer.Name) } };

[Gopichandar]

Hi, I tried this

it is throwing exception as follows

context.BulkInsertOrUpdate(customers, b => 
{
         b.SetOutputIdentity = true; 
         b.UpdateByProperties = new List<string> {  nameof(Customer.Name) }
}); 

SQLite Error 1: 'ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint'.

Any ide why

[Gopichandar]

@borisdj can you take a look at this, looks like a bug, I have added the sample

https://github.com/Gopichandar/EFCore.BulkExtensions.Samples

[borisdj]

I can confirm the bug but don't have the solution at the moment

[Gopichandar]

Let me this with PR. Thanks alot for your efforts on this.

[borisdj]

Fixed with latest v.