Can't BulkInsert entity with computed PrimaryKey member

[npbenjohnson]

Description

When the primary key of a table contains a computed column, BulkInsert tries to insert into it and sql throws, I've tried to work around it with:

// PendingChangesCompanyKey is the computed column
// Use an alternate key that doesn't include the computed column
                    o.ColumnPrimaryKeyExpression = x => new { x.PriceCatalogId, x.Psin, x.PendingChangesCompanyId};
// Turn this off just in case outputting has something to do with it
                    o.AutoMapOutputDirection = false;
// Ignore the computed column on insert
                    o.IgnoreOnInsertExpression = x => x.PendingChangesCompanyKey;
// Tried keeping or not keeping identity, neither works
                    o.InsertKeepIdentity = true;
// Long shots...
                    o.DisableSqlBulkCopyDirect = false;
                    o.ForceTriggerResolution = false;
                    o.ValidateNoDuplicateKey = false;

// This appears to possibly work but would be a confusing maintenance headache
o.InputColumnExpression = x => new {[All properties except PendingChangesCompanyKey]};

Exception

Microsoft.Data.SqlClient.SqlException: 'The column "PendingChangesCompanyKey" cannot be modified because it is either a computed column or is the result of a UNION operator.'

Fiddle or Project (Optional)

Fiddles aren't building right now, but here's the general idea:

// Entity Framework Extensions
// Doc: https://entityframework-extensions.net/bulk-savechanges

// @nuget: Microsoft.EntityFrameworkCore.SqlServer
// @nuget: Z.EntityFramework.Extensions.EFCore

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using Microsoft.Data.SqlClient;
using System.ComponentModel.DataAnnotations.Schema;

public class Program
{
    public static void Main()
    {
        using (var context = new EntityContext())
        {
            context.Database.EnsureCreated();
        }

        var customers = GenerateCustomers(5);

        using (var context = new EntityContext())
        {
            context.BulkInsert(customers);

            FiddleHelper.WriteTable("1 - Customers", context.Customers);
        }   
    }

    public static List<Customer> GenerateCustomers(int count) 
    {
        List<Customer>  list = new List<Customer>();

        for(int i = 0; i < count; i++)
        {
            list.Add(new Customer() { Key1 = i, Key2 = null});
        }

        return list;
    }

    public class EntityContext : DbContext
    {
        public EntityContext() 
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(new SqlConnection(FiddleHelper.GetConnectionStringSqlServer()));

            base.OnConfiguring(optionsBuilder);
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Customer>().HasKey(x => new { x.Key1, x.SurrogateKey });
            modelBuilder.Entity<Customer>().Property(x => x.SurrogateKey).HasComputedColumnSql($"ISNULL({nameof(Customer.SurrogateKey)}, -1) PERSISTED").ValueGeneratedNever();
        }

        public DbSet<Customer> Customers { get; set; }
    }

    public class Customer
    {
        public int Key1 { get; set; }
        public int? Key2 { get; set; }
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public int SurrogateKey {get; set;}
    }
}

<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.10" />
  <PackageReference Include="Z.EntityFramework.Plus.EFCore" Version="3.1.10" />

[JonathanMagnan]

Hello @npbenjohnson ,

Thank you for the code, we will look at it at the beginning of next week.

Best Regards,

Jon

[JonathanMagnan]

Hello @npbenjohnson ,

The v5.1.11 has been released last week but it looks like I forget to answer you.

Could you try it and let me know if my developer successfully fixed your issue.

He is currently not sure if the issue he fixed is the same as yours. If that's not the case, could you provide a runnable project with only this issue? You can send it in private here: info@zzzprojects.com (or a Fiddle if the website work). He told me that some configuration was missed so he added some code to make it works.

Best Regards,

Jon

[JonathanMagnan]

Hello @npbenjohnson

Since our last conversation, we haven't heard from you.

Did you get the chance to try the version v5.1.11?

Let us know if it successfully fixed the issue.

Looking forward to hearing from you,

Jon

[JonathanMagnan]

Hello @npbenjohnson ,

A simple reminder that we are here to assist you!

Feel free to contact us once you try the version v5.1.11.

Have a great week,

Jon

[npbenjohnson]

We haven't migrated our projects to .Net 5 yet but I'll verify after we do in the next couple of months

[JonathanMagnan]

Hello @npbenjohnson

Thank you for the updates!

Don't hesitate to contact us for further assistance,

Best regards,

Jon

[npbenjohnson]

This is still an issue, bulkmerge and bulkinsert both try to write the computed column: https://dotnetfiddle.net/IOaSxC

[npbenjohnson]

Any word on this? It's a recurring issue for our team because we have nullable tenancy FK fields that are sometimes used for compound primary-keys via a non-nullable computed column

[JonathanMagnan]

Hello @npbenjohnson ,

The issue has 2 parts.

We already fixed the first part but unfortunately, we still have not yet fixed the second part.

We are currently too much busy so we try to do our best but time is missing ;(

I will try to push hard tomorrow to complete this one asap as this one has been opened more than 1 month ago.

[JonathanMagnan]

Hello @npbenjohnson ,

The v5.2.0 has been released.

I cannot test yet the Fiddle since the version is not yet available but the same code worked in Visual Studio.

Could you try it and let us know if everything is fixed?

Best Regards,

Jon

[JonathanMagnan]

Hello @npbenjohnson ,

Since our last conversation, we haven't heard from you!

Did you get the chance to try the v5.2.0?

Let us know if everything is fixed now.

Looking forward to hearing from you,

Jon

[JonathanMagnan]

Hello again!

A simple reminder that we are here to assist you!

Feel free to contact us once you try the v5.2.0!

Best regards,

Jon

[justinobney]

I still appear to be getting this issue using v5.2.7

[justinobney]

Additional context:

I tested this using v5.2.12 as well and it does not work using the configuration I originally had.

My original configuration involved adding the computed column to the IgnoreOnMergeInsertExpression & IgnoreOnMergeUpdateExpression options, but when I flipped it to adding all the explicit keys to the ColumnInputExpression it worked as expected.

These are the config options that allowed this to work for me.

options.ColumnInputExpression = x => new
{
    x.AccountNumber,
    x.SubAccountNumber
    // x.UniqueAccountKey OMIT Computed Column FROM INPUT EXPRESSION
};

options.IgnoreOnMergeInsertExpression = x => new
{
    x.UniqueAccountKey
};

options.IgnoreOnMergeUpdateExpression = x => new
{
    x.UniqueAccountKey,
};

options.ColumnPrimaryKeyExpression = x => new
{
    x.UniqueAccountKey
};

[JonathanMagnan]

Hello @justinobney ,

Is it possible to provide a runnable project with the minimum code to reproduce the issue? You can send it in private here if needed: info@zzzprojects.com

My developer currently has way too many questions which he is not exactly sure how he can reproduce it like you. So having a project to work on the issue instead of trying to reproducing it will for sure be easier for him.

Best Regards,

Jon