Insert using a Sequence as generator for a Primary Key value in Entity Framework

Question

I have a sequence that looks like this:

CREATE SEQUENCE dbo.NextWidgetId
 AS [bigint]
 START WITH 100
 INCREMENT BY 2
 NO CACHE 
GO

And a table that looks like this:

CREATE TABLE [dbo].[Widget_Sequenced]
(
    [WidgetId] [int] NOT NULL DEFAULT(NEXT VALUE FOR dbo.NextWidgetId),
    [WidgetCost] [money] NOT NULL,
    [WidgetName] [varchar](50) NOT NULL,
    [WidgetCode] [int] NOT NULL,
    [LastChangedBy] [int] NOT NULL,
    [RowVersionId] [timestamp] NOT NULL,

    CONSTRAINT [PK_Widget_Sequenced] 
    PRIMARY KEY CLUSTERED ([WidgetId] ASC) ON [PRIMARY]
) ON [PRIMARY]

Is there a way to add a new record to this table structure using Entity Framework?

I tried setting StoreGeneratedPattern for WidgetId to computed and I tried it with Identity. Both gave me errors.

I tried this with EF 5. But I could move to EF 6 if it fixes this.

score 2 · Answer 1 · edited Sep 21 '18 at 16:30

2

It's possible from version 6.2, using this code:

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

More information on EF6 does not work with primary key from sequence

edited Sep 21 '18 at 16:30

spottedmahn

14,823
13
108
178

answered Feb 09 '18 at 09:47

JotaBe

38,030
8
98
117

Where does one set this value? and what attribute is needed on the field that represents the SEQUENCE column? – jkerak Apr 25 '18 at 16:30
@jkerak [ef6.2-issue165.cs](https://gist.github.com/divega/31a15d492283ab34e6631ab6686f6705#file-ef6-2-issue165-cs) – spottedmahn Sep 21 '18 at 16:31

score 0 · Answer 2 · answered Jun 23 '15 at 04:23

0

You can replace your sequence with IDENTITY(100, 2) and everything will work out of the box.

answered Jun 23 '15 at 04:23

ranquild

1,799
1
16
25

Well, it is avoiding the problem, rather than solving it. Yes, `IDENTITY(100, 2)` will work similar to this particular `SEQUENCE`, except there is no `NO CACHE` option for `IDENTITY`, as far as I know. Besides, this `SEQUENCE` object may be used in other tables to generate single sequence across several tables - you can't easily replicate this behavior using `IDENTITY`. – Vladimir Baranov Jun 23 '15 at 05:47
As far as I know, `NOCACHE` option is used to mitigate gaps in indexes. However [documentation](https://msdn.microsoft.com/en-us/library/ff878091.aspx) says "Gaps can still occur if numbers are requested using the NEXT VALUE FOR". – ranquild Jun 23 '15 at 07:24
Thanks. I had an identity and am moving to a sequence. I need to be able to update the value (to make it move partitions). – Vaccano Jun 23 '15 at 13:36

Insert using a Sequence as generator for a Primary Key value in Entity Framework

2 Answers2

Linked