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.

Answer 1

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

Answer 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