WYBIERZ * OD X GDZIE ID IN (…) z Dapper ORM

Jaki jest najlepszy sposób na napisanie zapytania z klauzulą ​​IN za pomocą Dapper ORM, gdy lista wartości dla klauzuli IN pochodzi z logiki biznesowej? Powiedzmy na przykład, że mam zapytanie:

SELECT * 
  FROM SomeTable 
 WHERE id IN (commaSeparatedListOfIDs)

commaSeparatedListOfIDsPrzekazywana jest z logiką biznesową i może być dowolnego typu IEnumerable(of Integer). Jak skonstruowałbym zapytanie w tym przypadku? Czy muszę robić to, co do tej pory robiłem, a mianowicie konkatenację łańcuchów znaków, czy też istnieje zaawansowana technika mapowania parametrów, o której nie wiem?

Dapper obsługuje to bezpośrednio. Na przykład...

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

Bezpośrednio ze strony głównej projektu GitHub :

Dapper pozwala przekazać w IEnumerable i automatycznie sparametryzuje twoje zapytanie.

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

Zostanie przetłumaczony na:

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

Jeśli twoja INklauzula jest zbyt duża, aby ją obsłużyć MSSQL, możesz dość łatwo użyć TableValueParameter z Dapper.

1. Utwórz swój typ TVP w MSSQL:

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

2. Utwórz DataTablez tymi samymi kolumnami co TVP i wypełnij go wartościami

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

3. Zmodyfikuj zapytanie Dapper, aby wykonać INNER JOINna tabeli TVP:

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

4. Przekaż tabelę danych w wywołaniu zapytania Dapper

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

Działa to również fantastycznie, gdy chcesz dokonać masowej aktualizacji wielu kolumn - po prostu zbuduj TVP i wykonaj UPDATEwewnętrzne połączenie z TVP.

Oto prawdopodobnie najszybszy sposób na zapytanie dużej liczby wierszy za pomocą Dappera przy użyciu listy identyfikatorów. Obiecuję ci, że jest to szybsze niż prawie jakikolwiek inny sposób, w jaki możesz myśleć (z możliwym wyjątkiem użycia TVP podanego w innej odpowiedzi, a którego nie przetestowałem, ale podejrzewam, że może być wolniejszy, ponieważ nadal musisz się wypełnić TVP). Jest planetami szybszymi od Dappera przy użyciu INskładni i wszechświatów szybciej niż Entity Framework wiersz po rzędzie. I nawet kontynenty są szybsze niż przekazywanie listy VALUESlub UNION ALL SELECTprzedmiotów. Można go łatwo rozszerzyć, aby używał klucza wielokolumnowego, wystarczy dodać dodatkowe kolumny do DataTable, tabeli tymczasowej i warunków łączenia.

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

Pamiętaj, że musisz trochę dowiedzieć się o wkładkach luzem. Istnieją opcje dotyczące wyzwalania wyzwalaczy (domyślnie nie), przestrzeganie ograniczeń, blokowanie tabeli, zezwalanie na jednoczesne wstawianie itd.

Upewnij się również, że nie zawijasz nawiasów wokół ciągu zapytania w taki sposób:

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

Miałem tę przyczynę błędu składni SQL przy użyciu Dappera 1.50.2, naprawionego przez usunięcie nawiasów

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

Nie jest konieczne dodawanie ()klauzuli WHERE, tak jak w zwykłym SQL. Ponieważ Dapper robi to dla nas automatycznie. Oto syntax:

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

var conditions = new { listOfIntegers };

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

Przykład dla postgres:

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

W moim przypadku użyłem tego:

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

moje zmienne „ids” w drugim wierszu są IEnumerable ciągów, również mogą to być liczby całkowite.

Z mojego doświadczenia wynika, że ​​najbardziej przyjaznym sposobem radzenia sobie z tym jest posiadanie funkcji, która przekształca ciąg znaków w tabelę wartości.

W Internecie dostępnych jest wiele funkcji splittera, z łatwością znajdziesz jedną dla siebie, niezależnie od smaku SQL.

Możesz wtedy zrobić ...

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

Lub

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

(Lub podobne)