Linq-to-SQL: як вказати умова, що перевіряє значення, що містяться в деякому пулі, але має додаткове пов'язане значення, яке потрібно перевірити

Гаразд, його важко пояснити в назвах вище. Отже, очевидно, очевидно:

Скажімо, у мене є сукупність номерів документів та пов'язаних з ними областей (DocNum, Area): (AB, 1), (GH, 2), (UI, 3)

Тепер я хочу написати запит Linq-to-SQL, щоб отримати інформацію про всі документи з такою комбінацією значень з бази даних, як мені це зробити? Тому що я думав, що це можна зробити за допомогою словника:

var data = from document in context.documents
           where dictionary.Contains(new KeyValuePair(document.DocNum, document.Area))
           select new
           {
               ...whatever
           }

Якщо я спробую це зробити так, то я отримаю таку помилку:

System.NotSupportedException: член   'System.Collections.Generic.KeyValuePair`2 [System.String, Area] .Key'   не підтримує переклад на SQL.

Чи може кожен, будь ласка, допомогти?

0

2 Відповіді

LINQ to SQL не може представляти пошук в Dictionary в пам'яті як SQL. Один із способів полягає у використанні AsEnumerable , щоб переконатися, що ви використовуєте LINQ для об'єктів замість:

var data = from document in context.documents.AsEnumerable()
       where dictionary.Contains(new KeyValuePair(document.DocNum, document.Area))
       select new
       {
           ...whatever
       }

Зауважте, що це означає, що ви отримуєте рядок кожен з context.documents . Якщо це невеликий стіл, це не повинно бути проблемою, але для великого столу слід розглянути такий вид фільтрації на стороні сервера, безпосередньо в SQL - наприклад, замінивши Dictionary на стороні клієнта таблицею на стороні сервера (яку ви можете приєднати до documents ), можливо, навіть тимчасової таблиці (залежно від ваших потреб).

--- EDIT ---

If the Dictionary is small and the table is large but well indexed, it might be worth executing a separate query for each value being searched. For example:

class Program {

    static void Main(string[] args) {

        var criteria = new Dictionary, object> {
            { new KeyValuePair("n1", "a1"), null },
            { new KeyValuePair("n2", "a2"), null },
            { new KeyValuePair("n3", "a3"), null },
        };

        using (var ctx = new DataClasses1DataContext()) {

            ctx.Log = Console.Out;

            var rows = new List();
            foreach (var criterion in criteria.Keys) {

                var q = from document in ctx.Documents
                    where document.DocNum == criterion.Key && document.Area == criterion.Value
                    select document;

                rows.AddRange(q);

            }

            foreach (var row in rows)
                Console.WriteLine("{0}, {1}", row.DocNum, row.Area);

        }

    }

}

Виводить такий вивід:

SELECT [t0].[DocNum], [t0].[Area]
FROM [dbo].[Document] AS [t0]
WHERE ([t0].[DocNum] = @p0) AND ([t0].[Area] = @p1)
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [n1]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [a1]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

SELECT [t0].[DocNum], [t0].[Area]
FROM [dbo].[Document] AS [t0]
WHERE ([t0].[DocNum] = @p0) AND ([t0].[Area] = @p1)
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [n2]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [a2]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

SELECT [t0].[DocNum], [t0].[Area]
FROM [dbo].[Document] AS [t0]
WHERE ([t0].[DocNum] = @p0) AND ([t0].[Area] = @p1)
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [n3]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [a3]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

n1, a1
n2, a2
n3, a3

Зауважте, однак, що це відхиляє вроджені можливості пошуку Dictionary (це може бути як і будь-який інший IEnumerable ) і дуже погано працює, якщо є багато словникових елементів.

1
додано

Contains може використовувати простий масив єдиних значень та перекласти його в пункт Where x In {a, b, c} у SQL. Я не думаю, де ... В може підтримувати кілька стовпців у SQL. У результаті вам дійсно потрібно об'єднати або складно, де (стиль SQL-82). Ви можете приєднатися до колекції об'єктів до таблиці (але не до таблиці колекції об'єктів):

var data = from dict in dictionary.Values
           join document in context.documents 
           on new {dict.DocNum, dict.Area} equals new {document.DocNum, document.Area}
           select new {};

Однак це вимагатиме, щоб вся таблиця документів була витягнута в пам'ять, а приєднання потім виконується через LINQ для об'єктів.

0
додано
FYI, Oracle підтримує кілька стовпців в IN, хоча більшість інших баз даних не мають. У будь-якому разі більшість баз даних мають обмеження на розмір оператора SQL та розмір самого списку IN (...) (наприклад, Oracle дозволяє не більше 1000 елементів у цьому списку).
додано Автор Branko Dimitrijevic, джерело
SQL Server має аналогічне обмеження на кількість параметрів. Оскільки OP використовує LINQ to SQL, я припускаю, що в даному випадку вона намагається потрапити на SQL Server, а не на Oracle, але добре знати про різницю.
додано Автор Jim Wooley, джерело
var chat = new Chat();
var chat = new Chat();
642 учасників

Обсуждение вопросов по C# / .NET / .NET Core / .NET Standard / Azure Сообщества-организаторы: — @itkpi — @dncuug