使用 LINQ 将数据插入到使用序列作为主密钥生成器的表中

Question

我有一个表，它从一个序列生成其主键（只是从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 Designer为表创建映射文件;该字段定义为：id[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_id", DbType="Int NOT NULL", IsPrimaryKey=true)]public int id {…}现在我正在尝试通过 LINQ 插入数据。var testTableRecord = new testTable(){    a = 1,};db.Connection.Open();db.testTables.InsertOnSubmit(testTableRecord);db.SubmitChanges();Console.WriteLine($"id: {testTableRecord.id}");我遇到的问题是，LINQ 似乎无法通过序列处理 id 生成，因为它在插入时隐式将 0 设置为 0。id当我将 设置为 时，插入失败，因为它尝试将 NULL 插入到不可为 null 的字段中。idCanBeNull当我将设置为 时，插入工作，但它需要一个字段，并尝试加载生成的id，并将对象中的设置为null，因为返回...idIsDbGeneratedIDENTITYSELECT CONVERT(Int,SCOPE_IDENTITY()) AS [value]',N'@p0 int',@p0=1idSCOPE_IDENTITY()null我一直在考虑只使用 ，销毁 LINQ 对象并在数据库中查询 ，但我没有任何唯一的东西可以搜索。IsDbGeneratedid不幸的是，将ID创建机制更改为IDENTITY不是一种选择。我是否必须显式查询数据库以获取下一个序列值并手动设置 id？通过 LINQ 处理这些插入件的最佳方式是什么？PS：我需要id，因为我必须插入更多使用id作为FK的数据。

Answer 1

从原始 sql 的角度来看待解决方案：

1.

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

据我所知，在LINQ to SQL中根本无法完成

2.

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

这可以通过从实体中排除 id 属性在 LINQ to SQL 中实现。testTable

如果需要从表中检索 ID，可以创建用于插入和查询的单独实体：

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)

如前所述，这基本上可以通过在每次插入之前手动请求下一个id来实现。如果采用此路线，则可通过多种方式在代码中实现此目的，可以考虑使用存储过程或使用 LINQ 数据上下文手动执行 sql 以检索下一个序列值。

下面是一个代码示例，演示如何使用分部方法扩展生成的 DataContext。

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

        }         

    }

}

实现上述操作后，您可以安全地插入这样的实体，它们将生成正确的序列 ID。

TestTable entity = new TestTable { a = 2 };

dataContext.TestTables.InsertOnSubmit(entity);

dataContext.SubmitChanges();

Answer 2

你唯一的希望是进行非常深刻的重构，并使用存储过程来插入记录。可以在数据上下文设计器中将存储过程映射到类的方法。Insert

使用您的表定义，存储的只是：

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 对象资源管理器拖到设计器图面上来将其导入到上下文中，然后如下所示：

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

并按如下方式进行自定义：

就这样。现在，LINQ-to-SQL 将生成一个存储过程调用来插入记录，例如：

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 多长时间。实体框架核心具有开箱即用的序列支持。还有更多。