Support Identity Column Creation on Value Mapped Key Columns

[ardalis]

I'm trying to use a value mapped key column which has a POCO class on the C# side but maps to/from a simple int column on the SQL side. I'd like EF Migrations to support this and let me set this column up as an Identity column. Here's the configuration for the class:

            builder
                .Entity<Customer>()
                .Property(c => c.Id)
                .HasConversion(
                    v => v.Value,
                    v => new CustomerKey(v))
                .UseSqlServerIdentityColumn() // this causes exception
                .ValueGeneratedOnAdd();
            builder.Entity<Customer>()
                .HasKey(c => c.Id);

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message:

PM> dotnet ef migrations add 'autogen key'
System.ArgumentException: Identity value generation cannot be used for the property 'Id' on entity type 'Customer' because the property type is 'CustomerKey'. Identity value generation can only be used with signed integer properties.
   at Microsoft.EntityFrameworkCore.Metadata.SqlServerPropertyAnnotations.SetValueGenerationStrategy(Nullable`1 value)
   at Microsoft.EntityFrameworkCore.SqlServer.Metadata.Internal.SqlServerPropertyBuilderAnnotations.ValueGenerationStrategy(Nullable`1 value)
   at Microsoft.EntityFrameworkCore.SqlServerPropertyBuilderExtensions.UseSqlServerIdentityColumn(PropertyBuilder propertyBuilder)
   at Microsoft.EntityFrameworkCore.SqlServerPropertyBuilderExtensions.UseSqlServerIdentityColumn[TProperty](PropertyBuilder`1 propertyBuilder)
   at WebApplication21.Data.ApplicationDbContext.OnModelCreating(ModelBuilder builder) in C:\Users\stevesmith\Documents\Visual Studio 2017\Projects\WebApplication21\Data\ApplicationDbContext.cs:line 18
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelCustomizer.Customize(ModelBuilder modelBuilder, DbContext context)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelCustomizer.Customize(ModelBuilder modelBuilder, DbContext context)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.<>c__DisplayClass5_0.<GetModel>b__1()
   at System.Lazy`1.ViaFactory(LazyThreadSafetyMode mode)
   at System.Lazy`1.ExecutionAndPublication(LazyHelper executionAndPublication, Boolean useDefaultConstructor)
   at System.Lazy`1.CreateValue()
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__7_1(IServiceProvider p)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
   at Microsoft.EntityFrameworkCore.Internal.InternalAccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(Func`1 factory)
   at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(String contextType)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Identity value generation cannot be used for the property 'Id' on entity type 'Customer' because the property type is 'CustomerKey'. Identity value generation can only be used with signed integer properties.

Stack trace:

Steps to reproduce

Here's my Entity and its Key class:

    public class Customer
    {
        public CustomerKey Id { get; set; } = new CustomerKey();
        public string Name { get; set; }
    }

    public class CustomerKey : Integer
    {
        public CustomerKey() : base(0)
        {
        }

        public CustomerKey(int value) : base(value)
        {
        }
    }

    public class Integer
    {
        public int Value { get; set; }


        public Integer() { }
        public Integer(int value) { Value = value; }


        // Custom cast from "int":
        public static implicit operator Integer(Int32 x) { return new Integer(x); }

        // Custom cast to "int":
        public static implicit operator Int32(Integer x) { return x.Value; }


        public override string ToString()
        {
            return string.Format("Integer({0})", Value);
        }
    }

Further technical details

EF Core version:

PackageReference Include="Microsoft.AspNetCore.App" 
PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Design" Version="2.1.0-rc1-final" PrivateAssets="All" 

Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win10
IDE: 15.7.1

[ardalis]

Update. Modifying migrations manually configures the table correctly:

            migrationBuilder.CreateTable(
                name: "Customers",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy",
                    SqlServerValueGenerationStrategy.IdentityColumn),
                    Name = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Customers", x => x.Id);
                });

Now I still can't insert a new record without getting this:

SqlException: Cannot insert explicit value for identity column in table 'Customers' when IDENTITY_INSERT is set to OFF.

I've tried adjusting the value mapping to pass a null and/or setting the CustomerKey property to null, etc. but so far can't convince EF not to pass that column into the INSERT statement. What's the trick to let it know that (I'm guessing it's marking it as an IDENTITY column which is the root problem of this issue)?

[ardalis]

According to the docs for ValueGeneratedOnAdd, if the value is the .NET default, it won't be passed on an INSERT. So, I changed my Customer entity to not automatically instantiate the key class:

    public class Customer
    {
        public CustomerKey Id { get; }
        public string Name { get; set; }
    }

but now I get this NotSupportedExeption:

NotSupportedException: Value generation is not supported for property 'Customer.Id' because it has a 'ValueConverter<CustomerKey, Nullable<int>>' converter configured. Configure the property to not use value generation using 'ValueGenerated.Never' or 'DatabaseGeneratedOption.None' and specify explict values instead.

I don't want to specify explicit values - I want them generated on add. My value is the default (null). Just perform the insert without that column and set the value to the value in the column. Why isn't this supported?

Thanks. Still trying to get this scenario to work one way or another.

[ajcvickers]

@ardalis This is a current limitation of value converters tracked by #11597

[ardalis]

Thanks. Do you think your workaround from here: #11970 (comment)

would work in this case (difference being a class, not an enum, as key type)?

[ajcvickers]

@ardalis I suspect not (I didn't expect it to work for enums) but if you try it and it works, or you find a different workaround, then please let us know.