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

Program / 글쓴이

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에 내부 조인으로 수행하십시오.

답변

다음은 ID 목록을 사용하여 Dapper로 많은 수의 행을 쿼리하는 가장 빠른 방법입니다. 나는 이것이 당신이 생각할 수있는 거의 모든 다른 방법보다 빠르다고 약속합니다 (다른 답변에서 주어진 TVP를 사용하는 것을 제외하고는 테스트하지 않았지만 여전히 채워야 하기 때문에 속도가 느릴 수 있습니다) TVP). 그것은이다 행성 빠르고 말끔이 사용하는 것보다 IN구문과 우주 빠른 행에 의해 엔티티 프레임 워크의 행보다 더합니다. 그리고 그것은 대륙의 목록 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;
   }
}

벌크 인서트에 대해 약간 배워야합니다. 트리거 발생 (기본값은 아니오), 제한 조건 존중, 테이블 잠금, 동시 삽입 허용 등에 대한 옵션이 있습니다.

답변

또한 쿼리 문자열을 괄호로 묶지 마십시오.

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

Dapper 1.50.2를 사용하여 괄호를 제거하여 SQL 구문 오류가 발생했습니다.

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

답변

일반 SQL에서와 같이 WHERE 절 을 추가 할 필요 는 없습니다() . Dapper가 자동으로 처리하기 때문입니다. 여기에 syntax:-

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

var conditions = new { listOfIntegers };

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

답변

postgres의 예 :

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