EF6 does not work with primary key from sequence

If a table with primary key from sequence instead of identity, for example:

CREATE TABLE [Application].[Countries](
    [CountryID] [int] NOT NULL
    CONSTRAINT [PK_Application_Countries] PRIMARY KEY CLUSTERED
)
GO

ALTER TABLE [Application].[Countries] ADD  CONSTRAINT [DF_Application_Countries_CountryID]  DEFAULT (NEXT VALUE FOR [Sequences].[CountryID]) FOR [CountryID]
GO

The the entity would be:

public class Country
{
    [Key]
    [DatabaseGenerated(???)]
    public int CountryID { get; set; }
}

With code first, EF6 can query, but cannot insert/update entity.

If DatabaseGeneratedOption.Identity is specified, EF6 generates scope_identity() which fails
If DatabaseGeneratedOption.Computed is specified, EF6 throws DbUpdateException: Modifications to tables where a primary key column has property 'StoreGeneratedPattern' set to 'Computed' are not supported. Use 'Identity' pattern instead. Key column: 'CountryID'. Table: 'CodeFirstDatabaseSchema.Countries'.
If DatabaseGeneratedOption.None is specified, EF6 does not refresh the entity's primary key after inserting it to table.

I searched around, and couldn't find a solution. Any workaround for this? And any plan to support sequence in EF6?

@divega

@divega may have a changeset that already addresses this, which we can look at taking.

In the meantime, one potential workaround is to map to stored procedures so that you can control the INSERT SQL.

WIP fix for #165: Add flag to control whether scope_identity() is use…

Clearing up milestone to consider enabling a workaround for this in 6.2 as per discussion in #182.

Fix for #165: Add flag to control whether scope_identity() is used to…

A workaround for this issue was added in 8e38ed8. It requires setting the following flag in the application:

SqlProviderServices.UseScopeIdentity = false;

Here is the description of the new flag (which includes some caveats worth knowing):

Gets or sets a value indicating whether to use the SCOPE_IDENTITY() function to retrieve values generated by the database for numeric columns during an INSERT operation. The default value of true is recommended and can provide better performance if all numeric values are generated using IDENTITY columns. If set to false, an OUTPUT clause will be used instead. An OUTPUT clause makes it possible to retrieve values generated by sequences or other means.

usage:

System.Data.Entity.SqlServer.SqlProviderServices.UseScopeIdentity = false;

Source: ef6.2-issue165.cs

And this is a system-wide configuration, not a per entity configuration 😢. Reference

A good sample of where this occurs/is needed is with the World Wide Importer's sample database.

First, modify the StockItemTransaction entity to use DatabaseGeneratedOption.Identity instead of DatabaseGeneratedOption.None, like so:

    [Table("Warehouse.StockItemTransactions")]
    internal partial class StockItemTransaction
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int StockItemTransactionID { get; set; }

Then, you would apply the recommended usage of .UseScopeIdentity at the start of your using block, like so:

            using (var context = new WideWorldContext())
            {
                System.Data.Entity.SqlServer.SqlProviderServices.UseScopeIdentity = false;

If someone runs into this issue with EF Core or wants an alternative workaround. Here is an approach which implements a custom ValueGenerator.

Note: This will consume a value from the sequence when an entity is added. This means if an entity is added to the context but changes are not saved then the sequence value will be consumed but not stored in the database.

Create a custom generator class (The ValueGenerator abstract class comes from Microsoft.EntityFrameworkCore.ValueGeneration)

public class SequenceValueGenerator : ValueGenerator<int>
    {
        public SequenceValueGenerator()
        {
            sequenceIdentifier = 'schema.sequence_name';
        }

        public override bool GeneratesTemporaryValues => false;

        private string sequenceIdentifier
        {
        }

        public override int Next(EntityEntry entry)
        {
            return NextValueForSequence(entry.Context);
        }

        private int NextValueForSequence(DbContext context)
        {
            SqlParameter result = new SqlParameter("@result", System.Data.SqlDbType.Int)
            {
                Direction = System.Data.ParameterDirection.Output
            };
            
            // This query as safe as the sequence identifier is statically defined
            context.Database.ExecuteSqlRaw($"SELECT @result = (NEXT VALUE FOR {sequenceIdentifier});", result);
            return (int)result.Value;
        }
    }

Add the value generator to your entity with the model builder in OnModelCreating

            modelBuilder.Entity<YourEntity>(entity =>
            {
                entity.Property(e => e.YourEntityID)
                    .HasValueGenerator<SequenceValueGenerator>();
            });

Navigation Menu

Repository navigation

EF6 does not work with primary key from sequence #165

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

EF6 does not work with primary key from sequence #165

Description

Activity

rowanmiller commented on Jan 23, 2017

divega commented on Jan 25, 2017

divega commented on Feb 16, 2017

spottedmahn commented on Sep 21, 2018

dagilleland commented on Apr 6, 2019

zakkl13 commented on Feb 5, 2020

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions