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

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

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

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

Ответы

Ответ 1

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

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

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

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

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

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

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, а не в более ранних версиях).

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

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

Итак, единственное жизнеспособное решение, о котором я могу думать, это комбинация Contains и a 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;

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

Решение 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 (!) Целых чисел, выглядит следующим образом:

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 является масштабируемым: Масштабируемый Содержит метод LINQ для SQL-сервера

Ответ 2

Вы можете создать коллекцию строк с этими двумя ключами (я предполагаю, что ваши ключи - это тип 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();
            }

Ответ 3

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

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

Ответ 4

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

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

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

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

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

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

Ответ 5

Я попробовал это решение, и оно работало со мной, и выходной запрос был идеальным без каких-либо параметров

using LinqKit; // nuget     
   var customField_Ids = customFields?.Select(t => new CustomFieldKey { Id = t.Id, TicketId = t.TicketId }).ToList();

    var uniqueIds1 = customField_Ids.Select(cf => cf.Id).Distinct().ToList();
    var uniqueIds2 = customField_Ids.Select(cf => cf.TicketId).Distinct().ToList();
    var predicate = PredicateBuilder.New<CustomFieldKey>(false); //LinqKit
    var lambdas = new List<Expression<Func<CustomFieldKey, bool>>>();
    foreach (var cfKey in customField_Ids)
    {
        var id = uniqueIds1.Where(uid => uid == cfKey.Id).Take(1).ToList();
        var ticketId = uniqueIds2.Where(uid => uid == cfKey.TicketId).Take(1).ToList();
        lambdas.Add(t => id.Contains(t.Id) && ticketId.Contains(t.TicketId));
    }

    predicate = AggregateExtensions.AggregateBalanced(lambdas.ToArray(), (expr1, expr2) =>
     {
         var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
         return Expression.Lambda<Func<CustomFieldKey, bool>>
               (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
     });


    var modifiedCustomField_Ids = repository.GetTable<CustomFieldLocal>()
         .Select(cf => new CustomFieldKey() { Id = cf.Id, TicketId = cf.TicketId }).Where(predicate).ToArray();

Ответ 6

Вы можете использовать Union для каждого составного первичного ключа:

var compositeKeys = new List<CK> 
{
    new CK { id1 = 1, id2 = 2 },
    new CK { id1 = 1, id2 = 3 },
    new CK { id1 = 2, id2 = 4 }
};

IQuerable<CK> query = null;
foreach(var ck in compositeKeys)
{
    var temp = context.Table.Where(x => x.id1 == ck.id1 && x.id2 == ck.id2);
    query = query == null ? temp : query.Union(temp);
}
var result = query.ToList();

Ответ 7

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

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

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

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