Dapper ORM을 사용하여 ID를 입력 할 때 *에서 * 선택 (…)

Dapper ORM을 사용하여 ID를 입력 할 때 *에서 * 선택 (…)

---

IN 절의 값 목록이 비즈니스 논리에서 올 때 Dapper ORM을 사용하여 IN 절로 쿼리를 작성하는 가장 좋은 방법은 무엇입니까? 예를 들어 검색어가 있다고 가정 해 보겠습니다.

SELECT * 
  FROM SomeTable 
 WHERE id IN (commaSeparatedListOfIDs)

는 commaSeparatedListOfIDs비즈니스 로직에서 전달되고 있으며 그것은 모든 종류의 수 있습니다 IEnumerable(of Integer). 이 경우 쿼리를 어떻게 구성합니까? 기본적으로 문자열 연결이거나 내가 알지 못하는 일종의 고급 매개 변수 매핑 기술이 있습니까? 지금까지 내가 한 일을해야합니까?

---

Dapper는 이것을 직접 지원합니다. 예를 들어 ...

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

---

GitHub 프로젝트 홈페이지 에서 직접 :

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

---

INMSSQL이 처리 하기에 절이 너무 큰 경우 Dapper와 함께 TableValueParameter를 매우 쉽게 사용할 수 있습니다.

1. MSSQL에서 TVP 유형을 작성하십시오.

   CREATE TYPE [dbo].[MyTVP] AS TABLE([ProviderId] [int] NOT NULL)
   

2. 만들기 DataTableTVP 동일한 컬럼 (들) 및 값으로 채우는

   var tvpTable = new DataTable();
   tvpTable.Columns.Add(new DataColumn("ProviderId", typeof(int)));
   // fill the data table however you wish
   

3. INNER JOINTVP 테이블 에서 작업을 수행하도록 Dapper 쿼리를 수정하십시오 .

   var query = @"SELECT * FROM Providers P
       INNER JOIN @tvp t ON p.ProviderId = t.ProviderId";
   

4. Dapper 쿼리 호출에서 DataTable을 전달하십시오.

   sqlConn.Query(query, new {tvp = tvpTable.AsTableValuedParameter("dbo.MyTVP")});
   

여러 열의 대량 업데이트를 원할 때 환상적으로 작동합니다. 간단히 TVP를 구축하고 TVP UPDATE에 내부 조인으로 수행하십시오.

---

Here is possibly the fastest way to query a large number of rows with Dapper using a list of IDs. I promise you this is faster than almost any other way you can think of (with the possible exception of using a TVP as given in another answer, and which I haven't tested, but I suspect may be slower because you still have to populate the TVP). It is planets faster than Dapper using IN syntax and universes faster than Entity Framework row by row. And it is even continents faster than passing in a list of VALUES or UNION ALL SELECT items. It can easily be extended to use a multi-column key, just add the extra columns to the DataTable, the temp table, and the join conditions.

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

Be aware that you need to learn a little bit about Bulk Inserts. There are options about firing triggers (the default is no), respecting constraints, locking the table, allowing concurrent inserts, and so on.

---

Also make sure you do not wrap parentheses around your query string like so:

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

I had this cause a SQL Syntax error using Dapper 1.50.2, fixed by removing parentheses

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

---

It is not necessary to add () in the WHERE clause as we do in a regular SQL. Because Dapper does that automatically for us. Here is the syntax:-

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

var conditions = new { listOfIntegers };

var results = connection.Query(SQL, conditions);

---

In my case I've used this:

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

my variable "ids" in the second line is an IEnumerable of strings, also they can be integers I guess.

---

In my experience, the most friendly way of dealing with this is to have a function that converts a string into a table of values.

There are many splitter functions available on the web, you'll easily find one for whatever if your flavour of SQL.

You can then do...

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

Or

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

(Or similar)

참고URL : https://stackoverflow.com/questions/8388093/select-from-x-where-id-in-with-dapper-orm