使用 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)ASBEGIN    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 intset @p3=8declare @p5 intset @p5=0exec 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 outputselect @p3, @p5当然，您可能不得不怀疑您将坚持使用 LINQ-to-SQL 多长时间。实体框架核心具有开箱即用的序列支持。还有更多。