c# - Force EntityFramework to use OUTPUT not SCOPE_IDENTITY to retrieve key value

Question

I'm using Entity Framework code first to access a set of tables whose key will be set by an int based SEQUENCE in a default constraint. EF seems to have trouble handling this, it insists on using SCOPE_IDENTITY after an insert to populate integer key fields.

Digging into the code, it looks kind of hard-coded:

http://entityframework.codeplex.com/SourceControl/latest#src/EntityFramework.SqlServer/SqlGen/DmlSqlGenerator.cs

See the IsValidScopeIdentityColumnType method a little over halfway down the page. If this method returns true the inserted Key value is retrieved with SCOPE_IDENTITY(), otherwise an OUTPUT clause is generated. (Guid/uniqueidentifier is the typical use case there).

        // make sure it's a primitive type
        if (typeUsage.EdmType.BuiltInTypeKind != BuiltInTypeKind.PrimitiveType)
        {
            return false;
        }

        // check if this is a supported primitive type (compare by name)
        var typeName = typeUsage.EdmType.Name;

        // integer types
        if (typeName == "tinyint"
            || typeName == "smallint"
            ||
            typeName == "int"
            || typeName == "bigint")
        {
            return true;
        }

Is there any way to fool this method into returning false for an integral field? Once I start seeing things like 'EDMType' I'm beyond what I really understand about how the EF mapping really works. Maybe there's some way to use a user defined type to fool it? But it's really the configuration on the .NET side that needs some sort of update.

See also the UseGeneratedValuesVariable method in that same file for where this is used...

It's not clear to me why OUTPUT isn't just used across the board here -- maybe performance?

Answer 1

UPDATE - ONLY IDENTITY SUPPORTED FOR DB GENERATED PK

You can create a key column marked as computed, which has a DataseGeneratedOption.Computed. (See DataseGeneratedOption enum).

To indicate this you can decorate the column with DatabaseGeneratedAttribute, or use the fluent API, in the OnModelCreating method of your DbContext, like so:

        modelBuilder.Entity<EntityType>()
            .Property(c => c.KeyColumn)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)

This sample code works perfectly with EF6.1

public class MyDbContext : DbContext
{
    public IDbSet<ComputedKey> ComputedKeys { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        // Computed Key:
        modelBuilder.Entity<ComputedKey>()
            .HasKey(c => c.Id)
            .Property(c => c.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
    }
}

public class ComputedKey
{
    public int Id { get; set; }
}

When you run the app, the table is created correctly.

The problem arises when you try to add the first entity to the entity collection and save changes. You get: Modifications to tables where a primary key column has property 'StoreGeneratedPattern' set to 'Computed' are not supported. Use 'Identity' pattern instead. Key column: 'Id'. Table: 'CodeFirstDatabaseSchema.ComputedKey'.

This is a limitation of EF (until 6.1) that only allows to have integer type or GUID as DB generated value for a PK.

WORKAROUNDS

First: One would be to use the column generated on the DB as an alternative key.

From EF 6.1 on you can create the AK, decorating the AK column with an attribute like this:

[Index("MyIndex", unique: true)]

Second: The reason for using a sequence is defining the seed and the increment. If this is what you need you can modify the identity like this:

DBCC CHECKIDENT ('SchemaName.TableName', RESEED, 10);

This means that the next generated identity value will be 11, and the increment will be 1.

If you need to use a different increment you'd need to drop and re-create the identity column, indicating the seed and increment. But for doing this you also need to drop and create the associated foreingk keys, so that's too hard to implement.

Third: You could use a trigger. Inside the trigger you can use SET IDENTITY_INSERT tableName ON/OFF, but then again you can have problems because the @@identity will mismatch.

NOTE: if you need to run custom SQL commands to apply this customizations, you need to implement the Seed method of your db initializer

CONCLUSION

This scenario is only partially supported, so you'd rather find an alternative solution, unless one of the previous work arounds work for you.

REQUEST THIS FUNCTIONALITY

if you're interested in this functionality, go to the Entity Framework Feature Suggestions, and vote for this: Allow using SQL Server 2012 sequence for generating primary key