Bulk Insert not respecting Id column with Linq2Db and Entity Framework Core

Question

I am using LINQ to DB with the linq2db.EntityFrameworkCore for bulk-entering of some records.

With EF, I just save the records with a 0 in the Id property and they use the sequence I set up on the table for the identifier. It appears that Linq2DB doesn't know that the Id property is an identifier.

Other than assigning Ids to all the records before insert (which might require N round-trips to the DB or calling sp_sequence_get_range), is there somewhere specific to look to ensure Linq2DB knows not to try and insert the Id?

This uses Linq2Db along with the EFC plugin

Database insert:

public async Task AddAsync(IList<Item> items)
{
    await AppContext.BulkCopyAsync(new BulkCopyOptions(), items);
}

Table configuration:

public class ItemConfiguration : IEntityTypeConfiguration<Item>
{
    public void Configure(EntityTypeBuilder<Item> builder)
    {
        // Primary Key
        builder.HasKey(x => x.Id);
        builder.Property(x => x.Id)
            .HasDefaultValueSql($"NEXT VALUE FOR {BillOfLadingDbContext.ItemIdSequence}")
            .HasColumnOrder(0);
        
        // ... snip ....
    }

Item class:

public class Item : SoftDeleteEntityBase
{
    // ... snip ...
}

public class SoftDeleteEntityBase : EntityBase, ISoftDeleteEntityBase
{
    public bool IsDeleted { get; set; }
}

public class EntityBase : IEntityBase
{
    public int Id { get; set; }

    // ... snip ...
}

Table schema:

This is the exception I get:

An unhandled exception has occurred while executing the request.

Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_Items'. Cannot insert duplicate key in object 'dbo.Items'. The duplicate key value is (0).

Answer 1

I was able to create a sample project and get it working. When I compared my production project to the sample, I realized that I had built a sample with only one entity. When I added a second entity, it became clear what the issue was:

public static class Linq2DbExtensions
    {
        public static DbContextOptionsBuilder ConfigureLinq2Db(this DbContextOptionsBuilder builder)
        {
            LinqToDBForEFTools.Initialize();

            builder.UseLinqToDB(bld =>
            {
                var mappingSchema = new MappingSchema();
                var mapBuilder = new FluentMappingBuilder(mappingSchema);

                mapBuilder.Entity<Contact>()
                    .Property(p => p.Id).IsIdentity()
                    .Build();

                mapBuilder.Entity<Item>()
                    .Property(i => i.Id).IsIdentity()
                    .Build();

                mapBuilder.Entity<Bill>()
                    .Property(b => b.Id).IsIdentity()
                    .Property(b => b.OrderNumbers)
                    .HasConversion(
                        list => JsonSerializer.Serialize(list, JsonSerializerOptions.Default),
                        s => JsonSerializer.Deserialize<IList<string>>(s, JsonSerializerOptions.Default) ??
                             new List<string>())
                    .Property(b => b.CustomerOrderNumbers)
                    .HasConversion(
                        list => JsonSerializer.Serialize(list, JsonSerializerOptions.Default),
                        s => JsonSerializer.Deserialize<IList<string>>(s, JsonSerializerOptions.Default) ??
                             new List<string>())
                    .Property(b => b.Carrier!.Name)
                    .HasColumnName("CarrierName")
                    .Property(b => b.Carrier!.Address1)
                    .HasColumnName("CarrierAddress1")
                    .Property(b => b.Carrier!.Address2)
                    .HasColumnName("CarrierAddress2")
                    .Property(b => b.Carrier!.Address3)
                    .HasColumnName("CarrierAddress3")
                    .Property(b => b.Carrier!.City)
                    .HasColumnName("CarrierCity")
                    .Property(b => b.Carrier!.State)
                    .HasColumnName("CarrierState")
                    .Property(b => b.Carrier!.PostalCode)
                    .HasColumnName("CarrierPostalCode")
                    .Property(b => b.Carrier!.Phone)
                    .Build();                   

                mapBuilder.Entity<Line>()
                    .Property(l => l.Id).IsIdentity()
                    .Build();

                mapBuilder.Entity<LineItem>()
                    .Property(li => li.Id).IsIdentity()
                    .Property(li => li.Lots)
                    .HasConversion(
                        list => JsonSerializer.Serialize(list, JsonSerializerOptions.Default),
                        s => JsonSerializer.Deserialize<IList<string>>(s, JsonSerializerOptions.Default) ??
                             new List<string>());
                mapBuilder.Build();

                bld.AddMappingSchema(mappingSchema);
            });

            return builder;
        }
    }

Note that I was calling .Build() after every entity! Instead, I should have only called once, after all the entity mappings were registered.

So, yes, I needed to register the identity, but I also needed to correctly build the mapping class as well. It was probably a copy-paste issue ;)

Answer 2

EF can default recognize Id columns by convention to treat them as Identity, which must be set up in the database. With Code-First EF will mark these as Identity in supporting databases, since you are using Linq2Db you will likely need to configure this explicitly in Linq2Db via its [Identity] attribute or IsIdentity. (see Linq2db insertion with identity not working as expected) If you have a schema-first scenario where that table creation script is manually made then you will need to set the Id column up as an Identity/auto-increment. EF may complain if the provider is not automatically recognizing the ID as an Identity column so you may also need EF's [DatabaseGenerated(DatabaseGeneratedOption.Identity] attribute as well.