为什么Dapper无法返回多个插入的行ID？ [英] Why is Dapper unable to return multiple inserted row ids?

问题描述

我有一个SQL Server表，使用以下命令将行插入其中：

I have a SQL Server table into which rows are inserted using:

var sql = @"
DECLARE @InsertedRows AS TABLE (Id BIGINT);
INSERT INTO Person ([Name], [Age]) OUTPUT Inserted.Id INTO @InsertedRows
VALUES (@Name, @Age);
SELECT Id FROM @InsertedRows;";

Person person = ...;

var id = connection.Query<long>(sql, person).First();

这一切都很好，但是如果我尝试插入多个项目并使用以下命令返回所有插入的ID：

This all works well however if I try to insert multiple items and return all the inserted ids using:

IEnumerable<Person> people = ...;    
var ids = connection.Query<long>(sql, people);

我收到一个错误：

System.InvalidOperationException：在此上下文中不允许使用可枚举的参数序列（数组，列表等）
Dapper.SqlMapper.GetCacheInfo（$ identity，Object exampleParameters，Boolean addToCache ）

在Dapper.SqlMapper.d__23`1.MoveNext（）

---从上一个引发异常的位置开始的堆栈跟踪---

System.InvalidOperationException : An enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context
at Dapper.SqlMapper.GetCacheInfo(Identity identity, Object exampleParameters, Boolean addToCache)
at Dapper.SqlMapper.d__23`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---

一个人如何在Dapper中返回多个插入的ID？

How would one return multiple inserted ids in Dapper?

推荐答案

某处需要循环。您有一条插入一行的sql语句，并在列表中发送了代码。由于您喜欢使用字符串文字形式的SQL，因此我会坚持每次插入一个人，将循环放入C＃中，并使用 SELECT SCOPE_IDENTITY（）。您不再需要@InsertedRows或OUTPUT。

Something, somewhere, needs to loop. You have a sql statement that inserts one row, and code sending in a list. Since you like your SQL in string literals, I would stick to inserting one person at a time, putting the loop in the C# and recovering each id in the C# using SELECT SCOPE_IDENTITY(). You no longer need @InsertedRows, or OUTPUT.

如果您真的想循环使用SQL，我相信您需要查看表值参数以传递列表的插入。 Dapper很高兴返回多个ID。

If you really want to loop in SQL, I believe you'll need to look at table valued parameters for passing your list of inserts. Dapper is very happy to return multiple Ids. It's complaining about multiple people as an input parameter.

有一天，希望很快，我们将回顾一下字符串文字中的SQL，就像我们现在看到的山羊牺牲。

One day, hopefully soon, we're going to look back at SQL in string literals like we presently look at goat sacrifice.

这篇关于为什么Dapper无法返回多个插入的行ID？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！