How to use BulkDelete with auto generated key as a primary key (using Entity Framework Plus library) in c#

Question

I am working on a project where I have an excel sheet with some data. I am converting it into list and mapping it into another list which is derived from table in a database using entity framework core.

Now I want to insert,update and delete the database with the list from the excel sheet. So I am trying to use bulk operation from Entity Framework Extension library but if I have proper primary key it works fine but if I have auto generated key as primary key bulk delete does not work. I don't know the reason for that.It does not show any errors and it does not delete any records as well. I don't understand the problem here.

This is what I tried.

context.BulkDelete(mappedvalue, options =>
{
    options.UseAudit = true;
    options.AuditEntries = auditEntries;
});

This is my class.

public class ScTable
 {
    public string? UserName { get; set; }
    public string? EmailId { get; set; }
    public string? AppsInstalledInSystem { get; set; }
    public Int64 Autokey { get; set; }
 }

The below code is the sample data from my excel sheet.Here Autokey is my auto-generated primary key in my table.

| UserName | EmailId             | AppsInstalledInSystem |Autokey |
| -------- | ------------------- | --------------------- | ------ |
| SS009876 | [email protected] |   MSOffice            | 0      |
| SS009876 | [email protected] |   VisualStudio        | 0      |
| SS009876 | [email protected] |    SqlServer          | 0      |
| SR009754 | [email protected]  |     VisualStudio      | 0      |
| SC009345 | [email protected] |    SqlServer          | 0      |
| SC009345 | [email protected] |     VisualStudio      | 0      |                         

Answer 1

So I am trying to use bulk operation from Entity Framework Extension library but if I have proper primary key it works fine but if I have auto generated key as primary key bulk delete does not work

My understanding is that when you use your proper key, the Autokey column has a value for every row. So there is no problem with using Bulk Delete

However, when you use an auto-generated key, the column Autokey has all the value to 0 must is probably still the key you are using to match.

You need, in this case, use a custom key (might be the Username if unique or maybe a combination of Username and Email) with the ColumnPrimaryKeyExpression option. The custom key must make the row unique.

context.BulkDelete(mappedvalue, options =>
{
    options.ColumnPrimaryKeyExpression = x => new { x.UserName };   
    //options.ColumnPrimaryKeyExpression = x => new { x.UserName, x.EmailId };
    options.UseAudit = true;
    options.AuditEntries = auditEntries;
});