首页 文章

SELECT * FROM X WHERE使用Dapper ORM标识IN（...）

提问于
浏览 次
180

当IN子句的值列表来自业务逻辑时，使用Dapper ORM使用IN子句编写查询的最佳方法是什么？例如，假设我有一个查询：

SELECT * 
  FROM SomeTable 
 WHERE id IN (commaSeparatedListOfIDs)

commaSeparatedListOfIDs 正在从业务逻辑传入，它可以是任何类型的 IEnumerable(of Integer) . 在这种情况下，我将如何构造查询？我必须做我不知道的事情吗？

.net sql dapper

8 回答

  • 4

    Dapper直接支持这一点 . 例如...

    string sql = "SELECT * FROM SomeTable WHERE id IN @ids"
var results = conn.Query(sql, new { ids = new[] { 1, 2, 3, 4, 5 }});
    回复于
  • 53

    直接来自GitHub project homepage：

    Dapper允许您传入IEnumerable并自动参数化您的查询 .

    connection.Query<int>(
    @"select * 
      from (select 1 as Id union all select 2 union all select 3) as X 
      where Id in @Ids", 
    new { Ids = new int[] { 1, 2, 3 });

    将被翻译为：

    select * 
from (select 1 as Id union all select 2 union all select 3) as X 
where Id in (@Ids1, @Ids2, @Ids3)

// @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3
    回复于
  • 3

    如果您的 IN 子句对于MSSQL来说太大而无法处理，则可以非常轻松地将TableValueParameter与Dapper一起使用 .

    • 在MSSQL中创建TVP类型：
    CREATE TYPE [dbo].[MyTVP] AS TABLE([ProviderId] [int] NOT NULL)
    • 使用与TVP相同的列创建 DataTable 并使用值填充它
    var tvpTable = new DataTable();
tvpTable.Columns.Add(new DataColumn("ProviderId", typeof(int)));
// fill the data table however you wish
    • 修改您的Dapper查询以在TVP表上执行 INNER JOIN ：
    var query = @"SELECT * FROM Providers P
    INNER JOIN @tvp t ON p.ProviderId = t.ProviderId";
    • 在Dapper查询调用中传递DataTable
    sqlConn.Query(query, new {tvp = tvpTable.AsTableValuedParameter("dbo.MyTVP")});

    当你想要对多列进行大规模更新时，这也非常有效 - 只需构建一个TVP并使用内部联接到TVP进行 UPDATE .

    回复于
  • 30

    这可能是使用ID列表使用Dapper查询大量行的最快方法 . 我向你保证，这比你能想到的几乎任何其他方式都要快（除了在另一个答案中使用TVP的可能例外，我没有测试过，但我怀疑可能会因为你仍然需要填充而变慢TVP） . 它比Dapper更快地使用 IN 语法并且比实体框架逐行快 universes . 它甚至比传递 VALUES 或 UNION ALL SELECT 项列表更快 . 它可以很容易地扩展为使用多列键，只需将额外的列添加到 DataTable ，临时表和连接条件 .

    public IReadOnlyCollection<Item> GetItemsByItemIds(IEnumerable<int> items) {
   var itemList = new HashSet(items);
   if (itemList.Count == 0) { return Enumerable.Empty<Item>().ToList().AsReadOnly(); }

   var itemDataTable = new DataTable();
   itemDataTable.Columns.Add("ItemId", typeof(int));
   itemList.ForEach(itemid => itemDataTable.Rows.Add(itemid));

   using (SqlConnection conn = GetConnection()) // however you get a connection
   using (var transaction = conn.BeginTransaction()) {
      conn.Execute(
         "CREATE TABLE #Items (ItemId int NOT NULL PRIMARY KEY CLUSTERED);",
         transaction: transaction
      );

      new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction) {
         DestinationTableName = "#Items",
         BulkCopyTimeout = 3600 // ridiculously large
      }
         .WriteToServer(itemDataTable);
      var result = conn
         .Query<Item>(@"
            SELECT i.ItemId, i.ItemName
            FROM #Items x INNER JOIN dbo.Items i ON x.ItemId = i.ItemId
            DROP TABLE #Items;",
            transaction: transaction,
            commandTimeout: 3600
         )
         .ToList()
         .AsReadOnly();
      transaction.Rollback(); // Or commit if you like
      return result;
   }
}

    请注意，您需要了解有关批量插入的一些信息 . 有关触发触发器的选项（默认为no），尊重约束，锁定表，允许并发插入等等 .

    回复于
  • 283

    在WHERE子句中添加 () 是 not necessary ，就像在常规SQL中一样 . 因为Dapper会自动为我们做这件事 . 这是 syntax ： -

    const string SQL = "SELECT IntegerColumn, StringColumn FROM SomeTable WHERE IntegerColumn IN @listOfIntegers";

var conditions = new { listOfIntegers };

var results = connection.Query(SQL, conditions);
    回复于
  • 5

    另外，请确保不要在查询字符串周围括起括号，如下所示：

    SELECT Name from [USER] WHERE [UserId] in (@ids)

    我有这个导致使用Dapper 1.50.2的SQL语法错误，通过删除括号修复

    SELECT Name from [USER] WHERE [UserId] in @ids
    回复于
  • 2

    就我而言，我用过这个：

    var query = "select * from table where Id IN @Ids";
var result = conn.Query<MyEntity>(query, new { Ids = ids });

    我在第二行中的变量“ids”是一个IEnumerable字符串，我猜它们也可以是整数 .

    回复于
  • 10

    根据我的经验，最友好的处理方法是使用一个将字符串转换为值表的函数 .

    网上有很多分割器功能，如果你喜欢SQL的话，你可以轻松找到一个 .

    然后你可以......

    SELECT * FROM table WHERE id IN (SELECT id FROM split(@list_of_ids))

    要么

    SELECT * FROM table INNER JOIN (SELECT id FROM split(@list_of_ids)) AS list ON list.id = table.id

    （或类似）

    回复于

相关问题