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


231

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?

Odpowiedzi:


366

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

47
Uważam, że ważne jest, aby pamiętać, że istnieje skończony limit liczby przedmiotów, które można wysłać do tablicy. Zrozumiałem to na własnej skórze, kiedy podałem zbyt wiele identyfikatorów. Nie pamiętam dokładnej liczby, ale z pamięci myślę, że to 200 elementów, zanim Dapper przestanie działać / wykonuje zapytanie.
Marko

8
Marko, to JEST ważne. A jeśli robisz to w ten sposób, możesz rozważyć znalezienie innego sposobu sprawdzania danych, takiego jak łączenie lub blokowanie połączenia zamiast przekazywania listy identyfikatorów. Klauzula IN nie jest najbardziej wydajnym zapytaniem i często można ją zastąpić istniejącą klauzulą, która będzie szybsza.
Don Rolling

24
Do Twojej wiadomości - SQL Server 2008 R2 ma limit 2100 wpisów w INklauzuli.
Jesse

6
SQLite ma domyślny limit 999 zmiennych.
Cameron

8
Uwaga: w SQL Server nie powiedzie się to, jeśli masz wiele elementów w tablicy i zawijasz parametr w nawiasach. Usunięcie nawiasów rozwiąże problem.
ajbeaven

66

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

43

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.


Świetne rozwiązanie, jednak nie działa na .NET Core zobaczyć to pytanie: stackoverflow.com/questions/41132350/... . Zobacz także tę stronę: github.com/StackExchange/Dapper/issues/603
pcdev,

3
Można też rozważyć wprowadzenie ProviderIdna MyTVPbyć PRIMARY KEY CLUSTERED, jak to tylko rozwiązać problem z wydajnością dla nas (wartości Mijaliśmy nie zawierał żadnych duplikatów).
Richardissimo,

@Richardissimo Czy możesz pokazać przykład, jak to zrobić? Nie mogę uzyskać poprawnej składni.
Mike Cole,


14

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.


Tak, zgadzam się z twoim ogólnym pomysłem stworzenia tabeli tymczasowej z ID, a następnie wewnętrznego połączenia na tym stole. Zrobiliśmy to wewnętrznie, co znacznie poprawiło wydajność zapytań. Nie jestem pewien, czy użyłbym klasy DataTable do niczego, ale twoje rozwiązanie jest całkowicie poprawne. To znacznie szybszy sposób.
Marko

DataTableJest wymagana dla wkładki luzem. Jak pan wstawić do tabeli temp 50.000 wartości?
ErikE

1
W kawałkach po 1000, czy poprawnie pamiętam limit? W każdym razie nie wiedziałem, że możesz ominąć limit dzięki DataTable, więc nauczyłem się dziś czegoś nowego ...
Marko

1
To niedorzeczne nakłady pracy, kiedy można zamiast tego użyć parametru wartości tabeli. Dapper czysto obsługuje przekazywanie tabeli danych jako TVP, co pozwala zrezygnować z tworzenia i niszczenia tabeli tymczasowej, a także zapełniania tej tabeli tymczasowej za pomocą BulkCopy. Rutynowo używamy rozwiązania opartego na TVP w przypadkach, gdy liczba parametrów dla klauzuli IN byłaby zbyt duża.
Pan T

3
Nie jest to absurdalnie dużo pracy, szczególnie jeśli ktoś trochę ją streszcza za pomocą klasy pomocniczej lub metody rozszerzenia.
ErikE

11

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

7

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

6

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

3

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.


List<string>?
Kiquenet

2

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)

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.