Using LINQ to insert data into a table that uses a sequence as primary key generator

Question

I have a table which generates its primary key from a sequence (that just counts up from 0):

CREATE TABLE [dbo].[testTable](
    [id] [int] NOT NULL,
    [a] [int] NOT NULL,
 CONSTRAINT [PK_testTable] PRIMARY KEY CLUSTERED ([id] ASC))

ALTER TABLE [dbo].[tblTestTable] ADD  CONSTRAINT [DF_tblTestTable_id]  DEFAULT (NEXT VALUE FOR [seq_PK_tblTestTable]) FOR [id]

I've used Visual Studio's O/R Designer to create the mapping files for the table; the id field is defined as:

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_id", DbType="Int NOT NULL", IsPrimaryKey=true)]
public int id {…}

and now I'm trying to insert data via LINQ.

var testTableRecord = new testTable()
{
    a = 1,
};
db.Connection.Open();
db.testTables.InsertOnSubmit(testTableRecord);
db.SubmitChanges();
Console.WriteLine($"id: {testTableRecord.id}");

The problem I'm encountering is, that LINQ seems unable to handle the id generation via sequence as it sets the id implicitly to 0 when inserting.

• When I set the id to CanBeNull, the insert fails because it tries to insert NULL into a non-nullable field.
• When I set the id to IsDbGenerated, the insert works but it expects an IDENTITY field and tries to load the generated id with SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 int',@p0=1 and than sets the id in the object to null because SCOPE_IDENTITY() returns null…
• I've been thinking about just using IsDbGenerated, destroying the LINQ object and querying the DB for the id, but I don't have anything unique to search for.

Unfortunately changing the id creation mechanism to IDENTITY is not an option.

Do I have to explicitly query the DB for the next sequence value and set the id manually?

Whats the best way to handle these inserts via LINQ?

PS: I need the id because I have to insert more data that uses the id as FK.

Answer 1

Looking at solutions from the raw sql perspective:

1.

INSERT INTO [dbo].[testTable] VALUES (NEXT VALUE FOR [dbo].[seq_PK_tblTestTable], 1)

Simply can't be done in LINQ to SQL as far as I can tell

2.

INSERT INTO [dbo].[testTable] (a) VALUES (1)

This can be achieved in LINQ to SQL by excluding the id property from the testTable entity.

If you need to retrieve ids from the table, you could create separate entities for inserting and querying:

public class testTableInsert {
    [ColumnAttribute(...)]
    public int a
}

public class testTableResult {
    [ColumnAttribute(...)]
    public int id

    [ColumnAttribute(...)]
    public int a
}

3.

DECLARE @nextId INT;
SELECT @nextId = NEXT VALUE FOR [dbo].[seq_PK_tblTestTable];  
INSERT INTO [dbo].[testTable] VALUES (@nextId, 1)

As you mentioned, this can be essentially achieved by manually requesting the next id before each insert. If you go this route there are multiple ways to achieve it in your code, you can consider stored procedures or use the LINQ data context to manually execute the sql to retrieve the next sequence value.

Here's a code sample demonstrating how to extend the generated DataContext using partial methods.

public partial class MyDataContext : System.Data.Linq.DataContext
{
    partial void InsertTestTable(TestTable instance)
    {

        using (var cmd = Connection.CreateCommand()) 
        {
            cmd.CommandText = "SELECT NEXT VALUE FOR [dbo].[seq_PK_TestTable] as NextId";
            cmd.Transaction = Transaction;
            int nextId = (int) cmd.ExecuteScalar();
            instance.id = nextId;

            ExecuteDynamicInsert(instance);
        }         
    }
}

Once the above is implemented, you can safely insert entities like this, and they will generate the correct sequence id.

TestTable entity = new TestTable { a = 2 };
dataContext.TestTables.InsertOnSubmit(entity);
dataContext.SubmitChanges();

Answer 2

Your only hope is a pretty profound refactoring and use a stored procedure to insert records. The stored procedure can be mapped to the class's Insert method in the data context designer.

Using your table definition, the stored is nothing but this:

CREATE PROCEDURE InsertTestTable
(
    @id int OUTPUT,
    @a AS int
)
AS
BEGIN
    INSERT dbo.testTable (a) VALUES (@a);

    SET @id = (SELECT CONVERT(int, current_value) 
        FROM sys.sequences WHERE name = 'seq_PK_tblTestTable')
END

You can import this stored procedure into the context by dragging it from the Sql Object Explorer onto the designer surface, which will then look like this:

The next step is to click the testTable class and click the ellipses button for the Insert method (which got enabled by adding the stored procedure to the context):

And customize it as follows:

That's all. Now LINQ-to-SQL will generate a stored procedure call to insert a record, for example:

declare @p3 int
set @p3=8

declare @p5 int
set @p5=0
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[InsertTestTable] @id = @p0 OUTPUT, 
    @a = @p1',N'@p0 int output,@p1 int,@RETURN_VALUE int output',
    @p0=@p3 output,@p1=123,@RETURN_VALUE=@p5 output
select @p3, @p5

Of course you may have to wonder how long you're going to hang on to LINQ-to-SQL. Entity Framework Core has sequence support out of the box. And much more.