Concatenated string sequence as primary key - Insertion fail via EF code

[urielb92]

Describe what is not working as expected:
I want to use a concatenated string to a sequence as a primary key of a table.
I have created the table using Code-First migration successfully, and have set the default value of the column to be the desired concat.

When I insert rows to the table manually using the INSERT command, it works as expected. If I choose to omit the value of the column, it is generated from the sequence, otherwise - it uses the value provided.

However, when I try to insert entities by code - an exception is being thrown.
The relevant field of the entity is a simple string property. I tried adding [DatabaseGenerated(DatabaseGeneratedOption.Computed)] (also identity, and none) annotation, but that didn't do the trick. When I set the property to Identity (I called directly the ValueGeneratedOnAdd through the fluent api) it just generated a GUID, not taking into consideration the default value provided.

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

Exception message: `System.InvalidOperationException: 'Unable to create or track an entity of type 'EntityName' because it has a null primary or alternate key value.'`
Stack trace: `at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.NullableKeyIdentityMap`1.Add(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.StartTracking(InternalEntityEntry entry)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry.SetEntityState(EntityState oldState, EntityState newState, Boolean acceptChanges)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityGraphAttacher.PaintAction(EntityEntryGraphNode node)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityEntryGraphIterator.TraverseGraph(EntityEntryGraphNode node, Func`2 handleNode)
   at Microsoft.EntityFrameworkCore.DbContext.SetEntityStates(IEnumerable`1 entities, EntityState entityState)`

I can think of workarounds such as first generating the value manually and only then updating the entity and saving it, or having a column for the sequence generated value, and a trigger update that column, but I'd like to avoid them if possible.

Further technical details

EF Core version: (found in project.json or packages.config) - 1.1.1
Database Provider - Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows
IDE: Visual Studio 2017

(Also opened on StackOverflow)

[ajcvickers]

@urielb92 You likely need a call to HasDefaultValueSql as explained in #4189. See also #7872.

[urielb92]

I have called 'HasDefaultValueSql'. That's how I set the default value, which works on manual insert

[ajcvickers]

@urielb92 Can you post some code that reproduces the issue? A full project or code listing is preferable, but just your entity types and OnModelCreating may be sufficient.

[urielb92]

Sure, I'll post the the relevant parts later when I have access to the code

[urielb92]

@ajcvickers Sorry for the delay, posting the relevant parts of the code.
This was scaffolded from the DB so the context is a bit verbose, so i'll just post the relevant parts.
Also - I've added the functionality through a migration, since a sequence had to be created.
Btw - I'm sorry the code isn't structured well, I can't figure out the markdown syntax :(

The entity :

    public partial class TheEntity    
    {
        [Column("EntityColumnNameInDb")]
        public string Id { get; set; }
        // Some other properties
    }

DbContext :

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // ...
            modelBuilder.Entity<TheEntity>(entity =>
            {
                entity.HasKey(e => e.Id)
                    .HasName("PK_EntityTable");

                entity.Property(e => e.Id).ValueGeneratedNever();

                 // ...
               }
               // ...
        }

The migration :

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"CREATE SEQUENCE EntitySequence START WITH 1 INCREMENT BY 1;");

            migrationBuilder.AlterColumn<string>(
                name: "EntityColumnNameInDb",
                table: "EntityTable",
                defaultValueSql: "'i' + CAST((NEXT VALUE FOR EntitySequence) AS VARCHAR(20))",
                nullable: false,
                oldClrType: typeof(int),
                oldDefaultValueSql: null);
        }

I think that's all the relevant parts. Thanks for the help.

[ajcvickers]

@urielb92 This line tells EF that you never need any kind of store-generated value:

entity.Property(e => e.Id).ValueGeneratedNever();

so you need to remove that,

Then, as explained in #4189 and #7872, you need to put this in your OnModelCreating:

entity.Property(b => b.Id)
    .ValueGeneratedOnAdd()
    .HasDefaultValueSql("'i' + CAST((NEXT VALUE FOR EntitySequence) AS VARCHAR(20))");

This tells EF to generate values and that the column has SQL to do this.

[urielb92]

@ajcvickers Hi, I've tried your suggestion before (as described in the main post), and it resulted with the Id being generated as a GUID, totally ignoring the default value. Just to make sure, tried it again - same results.