我如何使用超过2100个值在一个in子句中使用Dapper
本文关键字:in 一个 子句 Dapper 何使用 2100个 | 更新日期: 2023-09-27 18:07:56
我有一个包含id的列表,我想使用Dapper插入到一个临时表中,以避免在' in '子句中对参数的SQL限制。
现在我的代码是这样的:
public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
using (var db = new SqlConnection(this.connectionString))
{
return db.Query<int>(
@"SELECT a.animalID
FROM
dbo.animalTypes [at]
INNER JOIN animals [a] on a.animalTypeId = at.animalTypeId
INNER JOIN edibleAnimals e on e.animalID = a.animalID
WHERE
at.animalId in @animalIds", new { animalIds }).ToList();
}
}
我需要解决的问题是,当有超过2100个id在animalIds列表然后我得到一个SQL错误"传入的请求有太多的参数。"服务器最多支持2100个参数"。
所以现在我想创建一个临时表,填充传入方法的animalid。然后,我可以在临时表上连接animals表,避免使用巨大的"IN"子句。
我已经尝试了各种语法组合,但没有得到任何地方。这是我现在的位置:
public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
using (var db = new SqlConnection(this.connectionString))
{
db.Execute(@"SELECT INTO #tempAnmialIds @animalIds");
return db.Query<int>(
@"SELECT a.animalID
FROM
dbo.animalTypes [at]
INNER JOIN animals [a] on a.animalTypeId = at.animalTypeId
INNER JOIN edibleAnimals e on e.animalID = a.animalID
INNER JOIN #tempAnmialIds tmp on tmp.animalID = a.animalID).ToList();
}
}
我不能让SELECT INTO与id列表一起工作。也许有一个更好的方法来避免"IN"子句的限制。
我确实有一个备份解决方案,我可以将传入的animalid列表拆分为1000个块,但我已经读到大的"in"子句会受到性能打击,并且加入临时表将更有效,这也意味着我不需要额外的"拆分"代码来批量处理id到1000个块。
好的,这是你想要的版本。我将此添加为一个单独的答案,因为我使用SP/TVP的第一个答案使用了不同的概念。
public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
using (var db = new SqlConnection(this.connectionString))
{
// This Open() call is vital! If you don't open the connection, Dapper will
// open/close it automagically, which means that you'll loose the created
// temp table directly after the statement completes.
db.Open();
// This temp table is created having a primary key. So make sure you don't pass
// any duplicate IDs
db.Execute("CREATE TABLE #tempAnimalIds(animalId int not null primary key);");
while (animalIds.Any())
{
// Build the statements to insert the Ids. For this, we need to split animalIDs
// into chunks of 1000, as this flavour of INSERT INTO is limited to 1000 values
// at a time.
var ids2Insert = animalIds.Take(1000);
animalIds = animalIds.Skip(1000).ToList();
StringBuilder stmt = new StringBuilder("INSERT INTO #tempAnimalIds VALUES (");
stmt.Append(string.Join("),(", ids2Insert));
stmt.Append(");");
db.Execute(stmt.ToString());
}
return db.Query<int>(@"SELECT animalID FROM #tempAnimalIds").ToList();
}
}
测试:var ids = LoadAnimalTypeIdsFromAnimalIds(Enumerable.Range(1, 2500).ToList());
你只需要修改你的select语句到它原来的样子。因为我的环境中没有您的所有表,所以我只从已创建的临时表中选择一个,以证明它的工作方式应该是正确的。
陷阱,见注释:
- 从一开始就打开连接,否则临时表将关闭在dapper自动关闭连接后,他就走了
- 这种特殊口味的
INSERT INTO
是有限的到1000个值,所以传递的id需要拆分为相应的块。 - 不要传递重复的键,因为临时表上的主键是不允许的。
编辑
似乎Dapper支持一个基于集合的操作,它也可以使这个工作:
public IList<int> LoadAnimalTypeIdsFromAnimalIdsV2(IList<int> animalIds)
{
// This creates an IEnumerable of an anonymous type containing an Id property. This seems
// to be necessary to be able to grab the Id by it's name via Dapper.
var namedIDs = animalIds.Select(i => new {Id = i});
using (var db = new SqlConnection(this.connectionString))
{
// This is vital! If you don't open the connection, Dapper will open/close it
// automagically, which means that you'll loose the created temp table directly
// after the statement completes.
db.Open();
// This temp table is created having a primary key. So make sure you don't pass
// any duplicate IDs
db.Execute("CREATE TABLE #tempAnimalIds(animalId int not null primary key);");
// Using one of Dapper's convenient features, the INSERT becomes:
db.Execute("INSERT INTO #tempAnimalIds VALUES(@Id);", namedIDs);
return db.Query<int>(@"SELECT animalID FROM #tempAnimalIds").ToList();
}
}
我不知道与以前的版本相比,这将有多好。2500个单独的插入,而不是三个分别有1000、1000、500个值的插入)。但是文档建议如果与async, MARS和Pipelining一起使用,它的性能会更好。
在您的示例中,我看不到的是您的animalIds
列表实际上是如何传递给要插入#tempAnimalIDs
表的查询的。
有一种不使用临时表的方法,使用带有表值参数的存储过程。
SQL:CREATE TYPE [dbo].[udtKeys] AS TABLE([i] [int] NOT NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[myProc](@data as dbo.udtKeys readonly)AS
BEGIN
select i from @data;
END
GO
这将创建一个名为udtKeys
的用户定义表类型,它只包含一个名为i
的int列,以及一个期望该类型参数的存储过程。该过程除了选择您传递的id之外什么都不做,但是您当然可以将其他表连接到它。有关语法的提示,请参阅此处。
var dataTable = new DataTable();
dataTable.Columns.Add("i", typeof(int));
foreach (var animalId in animalIds)
dataTable.Rows.Add(animalId);
using(SqlConnection conn = new SqlConnection("connectionString goes here"))
{
var r=conn.Query("myProc", new {data=dataTable},commandType: CommandType.StoredProcedure);
// r contains your results
}
过程中的参数通过传递一个DataTable来填充,并且该DataTable的结构必须与您创建的表类型相匹配。
如果你真的需要传递超过2100个值,你可能需要考虑索引你的表类型来提高性能。你可以给它一个主键如果你不传递任何重复键,像这样:
CREATE TYPE [dbo].[udtKeys] AS TABLE(
[i] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[i] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
您可能还需要为执行该类型的数据库用户分配执行权限,如下所示:
GRANT EXEC ON TYPE::[dbo].[udtKeys] TO [User]
GO
对我来说,我能想到的最好的方法是在c#中将列表转换为逗号分隔的列表,然后在SQL中使用string_split
将数据插入临时表中。这可能有上限,但在我的情况下,我只处理6000条记录,它运行得非常快。
public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
using (var db = new SqlConnection(this.connectionString))
{
return db.Query<int>(
@" --Created a temp table to join to later. An index on this would probably be good too.
CREATE TABLE #tempAnimals (Id INT)
INSERT INTO #tempAnimals (ID)
SELECT value FROM string_split(@animalIdStrings)
SELECT at.animalTypeID
FROM dbo.animalTypes [at]
JOIN animals [a] ON a.animalTypeId = at.animalTypeId
JOIN #tempAnimals temp ON temp.ID = a.animalID -- <-- added this
JOIN edibleAnimals e ON e.animalID = a.animalID",
new { animalIdStrings = string.Join(",", animalIds) }).ToList();
}
}
值得注意的是,string_split
仅在SQL Server 2016或更高版本中可用,或者如果使用Azure SQL,则兼容模式为130或更高。https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15