EntityFramework – содержит запрос составного ключа

учитывая список идентификаторов, я могу запросить все соответствующие строки:

context.Table.Where(q => listOfIds.Contains(q.Id)); 

Но как вы достигаете такой же функциональности, когда таблица имеет составной ключ?

Это неприятная проблема, для которой я не знаю ничего элегантного решения.

Предположим, что у вас есть эти комбинации клавиш, и вы хотите выбрать только отмеченные (*).

 Id1 Id2 --- --- 1 2 * 1 3 1 6 2 2 * 2 3 * ... (many more) 

Как это сделать, так как Entity Framework счастлив? Давайте посмотрим на некоторые возможные решения и посмотрим, хороши ли они.

Решение 1: Join (или Contains ) с парами

Лучшим решением было бы создать список пар, который вы хотите, например, Tuples, ( List> ) и объединить данные базы данных с этим списком:

 from entity in db.Table // db is a DbContext join pair in Tuples on new { entity.Id1, entity.Id2 } equals new { Id1 = pair.Item1, Id2 = pair.Item2 } select entity 

В LINQ для объектов это было бы прекрасно, но слишком плохо, EF будет генерировать исключение, например

Невозможно создать постоянное значение типа «System.Tuple`2 (…) В этом контексте поддерживаются только примитивные типы или типы перечислений.

который является довольно неуклюжим способом сказать вам, что он не может перевести этот оператор в SQL, потому что Tuples не является списком примитивных значений (например, int или string ). 1 . По той же причине аналогичный оператор, использующий Contains (или любой другой оператор LINQ), потерпит неудачу.

Решение 2: встроенная память

Конечно, мы могли бы превратить проблему в простой LINQ к таким объектам:

 from entity in db.Table.AsEnumerable() // fetch db.Table into memory first join pair Tuples on new { entity.Id1, entity.Id2 } equals new { Id1 = pair.Item1, Id2 = pair.Item2 } select entity 

Излишне говорить, что это нехорошее решение. db.Table может содержать миллионы записей.

Решение 3: два предложения Contains

Итак, давайте предложим EF два списка примитивных значений, [1,2] для Id1 и [2,3] для Id2 . Мы не хотим использовать join (см. Примечание к стороне), поэтому давайте использовать Contains :

 from entity in db.Table where ids1.Contains(entity.Id1) && ids2.Contains(entity.Id2) select entity 

Но теперь результаты также содержат объект {1,3} ! Ну, конечно, эта сущность идеально соответствует двум предикатам. Но давайте иметь в виду, что мы приближаемся. Вместо того, чтобы вытаскивать миллионы объектов в память, мы получаем только четыре из них.

Решение 4: Один Contains с вычисленными значениями

Решение 3 потерпело неудачу, потому что два отдельных оператора Contains не только фильтруют комбинации своих значений. Что делать, если мы сначала создадим список комбинаций и попытаемся сопоставить эти комбинации? Из решения 1 известно, что этот список должен содержать примитивные значения. Например:

 var computed = ids1.Zip(ids2, (i1,i2) => i1 * i2); // [2,6] 

и оператор LINQ:

 from entity in db.Table where computed.Contains(entity.Id1 * entity.Id2) select entity 

Есть некоторые проблемы с этим подходом. Во-первых, вы увидите, что это также возвращает объект {1,6} . Комбинационная функция (a * b) не дает значений, которые однозначно идентифицируют пару в базе данных. Теперь мы можем создать список строк, таких как ["Id1=1,Id2=2","Id1=2,Id2=3]" и сделать

 from entity in db.Table where computed.Contains("Id1=" + entity.Id1 + "," + "Id2=" + entity.Id2) select entity 

(Это будет работать в EF6, а не в более ранних версиях).

Это становится довольно грязным. Но более важная проблема заключается в том, что это решение не поддается сопоставлению , а это означает: он обходит любые индексы базы данных на Id2 и Id2 которые могли быть использованы иначе. Это будет очень плохо.

Решение 5: Лучшее из 2 и 3

Таким образом, единственное жизнеспособное решение, о котором я могу думать, это комбинация Contains и join в память: сначала сделайте оператор contains, как в решении 3. Помните, что он приблизился к тому, что мы хотели. Затем уточните результат запроса, присоединив результат как список в памяти:

 var rawSelection = from entity in db.Table where ids1.Contains(entity.Id1) && ids2.Contains(entity.Id2) select entity; var refined = from entity in rawSelection.AsEnumerable() join pair in Tuples on new { entity.Id1, entity.Id2 } equals new { Id1 = pair.Item1, Id2 = pair.Item2 } select entity; 

Это не изящно, беспорядочно все-таки возможно, но пока это единственное масштабируемое решение для этой проблемы, которое я нашел, и применяется в моем собственном коде.

Решение 6. Создайте запрос с предложениями OR

Используя построитель Predicate, такой как Linqkit или альтернативы, вы можете создать запрос, содержащий предложение OR для каждого элемента в списке комбинаций. Это может быть жизнеспособным вариантом для действительно коротких списков. С несколькими сотнями элементов запрос начнет работать очень плохо. Поэтому я не считаю это хорошим решением, если вы не можете быть на 100% уверены, что всегда будет небольшое количество элементов. Один из вариантов этого варианта можно найти здесь .


1 Как забавная заметка, EF создает инструкцию SQL, когда вы присоединяетесь к примитивному списку, например

 from entity in db.Table // db is a DbContext join i in MyIntegers on entity.Id1 equals i select entity 

Но сгенерированный SQL, ну, абсурдно. Реальный пример, когда MyIntegers содержит только 5 (!) MyIntegers чисел, выглядит следующим образом:

 SELECT [Extent1].[CmpId] AS [CmpId], [Extent1].[Name] AS [Name], FROM [dbo].[Company] AS [Extent1] INNER JOIN (SELECT [UnionAll3].[C1] AS [C1] FROM (SELECT [UnionAll2].[C1] AS [C1] FROM (SELECT [UnionAll1].[C1] AS [C1] FROM (SELECT 1 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] UNION ALL SELECT 2 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1] UNION ALL SELECT 3 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2] UNION ALL SELECT 4 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable4]) AS [UnionAll3] UNION ALL SELECT 5 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable5]) AS [UnionAll4] ON [Extent1].[CmpId] = [UnionAll4].[C1] 

Есть n-1 UNION s. Конечно, это не масштабируемо.

Позднее добавление:
Где-то по дороге в EF версии 6.1.3 это значительно улучшилось. UNION стали проще, и они больше не вложены. Раньше запрос отказывался от менее 50 элементов в локальной последовательности (исключение SQL: некоторая часть вашего оператора SQL вложен слишком глубоко .) Не вложенный UNION разрешает локальные последовательности до нескольких тысяч (!) Элементов , Это все еще медленно, но с «множеством» элементов.

2 Поскольку оператор Contains является масштабируемым: Scalable Содержит метод LINQ для SQL-сервера

в случае составного ключа вы можете использовать другой идентификатор и добавить условие для этого в свой код

 context.Table.Where(q => listOfIds.Contains(q.Id) && listOfIds2.Contains(q.Id2)); 

или вы можете использовать один трюк, создавая список своих ключей, добавляя их

 listofid.add(id+id1+......) context.Table.Where(q => listOfIds.Contains(q.Id+q.id1+.......)); 

Вам нужен набор объектов, представляющих ключи, которые вы хотите запросить.

 class Key { int Id1 {get;set;} int Id2 {get;set;} 

Если у вас есть два списка, и вы просто проверяете, что каждое значение отображается в их соответствующем списке, тогда вы получаете декартовы продукт из списков, что, скорее всего, не то, что вы хотите. Вместо этого вам нужно запросить требуемые комбинации

 List keys = // get keys; context.Table.Where(q => keys.Any(k => k.Id1 == q.Id1 && k.Id2 == q.Id2)); 

Я не совсем уверен, что это действительное использование Entity Framework; у вас могут возникнуть проблемы с отправкой типа Key в базу данных. Если это произойдет, вы можете быть творческими:

 var composites = keys.Select(k => p1 * k.Id1 + p2 * k.Id2).ToList(); context.Table.Where(q => composites.Contains(p1 * q.Id1 + p2 * q.Id2)); 

Вы можете создать изоморфную функцию (простые числа хороши для этого), что-то вроде hash-кода, который вы можете использовать для сравнения пары значений. Пока мультипликативные факторы являются совместными, этот шаблон будет изоморфным (взаимно однозначным), т. p1*Id1 + p2*Id2 Результат p1*Id1 + p2*Id2 однозначно идентифицирует значения Id2 и Id2 если простые числа правильно выбран.

Но тогда вы оказываетесь в ситуации, когда вы реализуете сложные концепции, и кто-то должен будет поддержать это. Вероятно, лучше написать хранимую процедуру, которая принимает действительные ключевые объекты.

Вы можете создать коллекцию строк с такими ключами, как это (я предполагаю, что ваши ключи имеют тип int):

 var id1id2Strings = listOfIds.Select(p => p.Id1+ "-" + p.Id2); 

Затем вы можете просто использовать «Содержит» на своем db:

 using (dbEntities context = new dbEntities()) { var rec = await context.Table1.Where(entity => id1id2Strings .Contains(entity.Id1+ "-" + entity.Id2)); return rec.ToList(); } 

В отсутствие общего решения, я думаю, есть две вещи, которые следует учитывать:

  1. Избегайте многоколоночных первичных ключей (также упростит модульное тестирование).
  2. Но если вам нужно, возможно, что один из них уменьшит размер результата запроса до O (n), где n – размер идеального результата запроса. Отсюда его Решение 5 от Герда Арнольда выше.

Например, проблема, связанная с этим вопросом, заключалась в том, чтобы запросить строки заказа, где ключ – идентификатор заказа + номер строки заказа + тип заказа, а источник имел тип заказа неявный. То есть тип заказа был константой, идентификатор заказа уменьшал бы набор запросов к порядковым строкам соответствующих заказов, и обычно их было бы 5 или менее.

Перефразировать: если у вас есть составной ключ, изменения заключаются в том, что один из них имеет очень мало дубликатов. Примените решение 5 выше.

  • ADO.Net Entity Framework Объект сущности не может ссылаться на несколько экземпляров IEntityChangeTracker
  • Entity Framework - создание classов
  • Loop / отражать все свойства во всех моделях EF для установки типа столбца
  • Круговая ссылка была обнаружена при сериализации объекта типа SubSonic.Schema. DatabaseColumn.
  • DbContext отменяет изменения без утилизации
  • Преобразование сплющенных иерархических данных из SQL Server в структурированный объект JSON с C # / Linq
  • Необработанный SQL-запрос Entity framework
  • Entity Framework: как отключить ленивую загрузку для конкретного запроса?
  • ASP.NET MVC 2.0 Реализация поиска в jqgrid
  • Как создать индекс в Entity Framework 6.2 с первым кодом
  • Контекст данных EF - Async / Await & Multithreading
  • Interesting Posts

    Как я могу создать FLAC + cue из нескольких файлов FLAC?

    Статические и экземпляры с тем же именем?

    bootstrap 3 – как разместить марку в центре навигатора?

    Доступ к базе данных SQL в Excel-VBA

    Задача производительности C ++: целое число для преобразования std :: string

    Как читать значения AppSettings из Config.json в ASP.NET Core

    Можно ли использовать тернарный / условный оператор Java (? :) для вызова методов вместо назначения значений?

    Самый надежный снимок неудачного жесткого диска?

    Является ли это плохой практикой, чтобы поймать неспецифическое исключение, такое как System.Exception? Зачем?

    Servicestack – архитектура и повторное использование POCOs для всего

    Чтение PDF с использованием itextsharp, где язык PDF не является английским

    Как вы дублируете текущий открытый вид Finder на новой вкладке (Mavericks)?

    Группа AngularJS по директиве без внешних зависимостей

    Случайные точки внутри параллелограмма

    ASP.NET MVC Razor: как визуализировать HTML-код Razor Partial View внутри действия controllerа

    Давайте будем гением компьютера.