使用LINQ将数据插入到使用序列作为主键生成器的表中 [英] Using LINQ to insert data into a table that uses a sequence as primary key generator

问题描述

我有一个表，该表从一个序列(仅从0开始计数)生成其主键:

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]

我已经使用Visual Studio的O/R设计器来创建表的映射文件. 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 {…}

现在我正在尝试通过LINQ插入数据.

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}");

我遇到的问题是，LINQ似乎无法通过序列处理id生成，因为它在插入时会将id隐式设置为0.

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.

• 当我将id设置为CanBeNull时，插入失败，因为它试图将NULL插入到不可为空的字段中.
• 当我将id设置为IsDbGenerated时，插入可以工作，但是它需要一个IDENTITY字段，并尝试使用SELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 int',@p0=1加载生成的ID，然后将对象中的id设置为null，因为SCOPE_IDENTITY()返回null…
• 我一直在考虑只使用IsDbGenerated，销毁LINQ对象并向数据库查询id，但是我没有什么可搜索的.

• 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.

不幸的是，不能将ID创建机制更改为IDENTITY.

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

我是否必须明确地在数据库中查询下一个序列值并手动设置ID?

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

通过LINQ处理这些插入物的最佳方法是什么?

Whats the best way to handle these inserts via LINQ?

PS:我需要ID，因为我必须插入更多使用ID作为FK的数据.

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

推荐答案

您唯一的希望是深刻的重构，并使用

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

您可以通过将该存储过程从Sql Object Explorer中拖动到设计器图面上，将其导入到上下文中，如下所示:

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:

下一步是单击testTable类，然后单击Insert方法的省略号按钮(通过将存储过程添加到上下文中来启用该按钮):

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:

仅此而已.现在，LINQ-to-SQL将生成一个存储过程调用以插入一条记录，例如:

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

当然，您可能不得不怀疑要挂多久才能使用LINQ-to-SQL. Entity Framework Core具有序列支持开箱即用 .还有更多.

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.

这篇关于使用LINQ将数据插入到使用序列作为主键生成器的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！