Как сделать запрос по составным ключам, используя ORM + LINQ?

Question

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

public class Key
{
    public string Part1 {get;set;}
    public string Part2 {get;set;}
}

Теперь скажи, что у меня есть IQueryable<Table1> и я хочу вернуть все записи с вышеуказанным Keys. Таблица 1 не имеет суррогатного ключа, она имеет составной ключ, состоящий из 2 столбцов. Я также хочу, чтобы избежать для каждой строки поиска.

Как я могу это сделать? Я пробовал стандартное объединение, но по понятным причинам мой ORM сбивается с толку, когда в памяти выбрасываются объекты.

Answer 1

В подобных случаях я обнаружил, что лучшим решением будет первое, в котором выполняется выбор в базе данных, а затем точные совпадения в памяти:

var parts1 = Keys.Select(k => k.Part1).ToArray();
var parts2 = Keys.Select(k => k.Part2).ToArray();

var dbSelection = context.TableRecords.Where(r => parts1.Contains(r.Part1)
                                               && parts2.Contains(r.Part2);

var finalSelection = from record in dbSelection
                                    .AsEnumerable() // to memory
                     join key in Keys on new { record.Part1, record.Part2 }
                                         equals
                                         new { key.Part1, key.Part2 }
                     select record;

Если у вас есть ключи

1,2
2,1

затем dbSelection также будет содержать {1,1} а также {2,2} (но не подавляющее большинство других записей). Они отфильтрованы вторым запросом.

Преимущество состоит в том, что запрос к базе данных может использовать индексы, что невозможно, если вы работаете с вычисленными ключами (например, значениями сцепленных ключей).

Недостатком является то, что вы должны убедиться, что parts1 а также parts2 не может расти чрезмерно, иначе SQL IN операторы станут крайне неэффективными или даже потерпят крах из-за слишком большого количества элементов (мы говорим о многих тысячах пунктов здесь для Sql Server). Но это верно для любого решения, использующего Contains,

Answer 2

Обходной путь мог работать с конкатенацией

что-то вроде того.

var keys = new List<Key> {
    {new Key {Part1="12", Part2="3"},
    {new Key {Part1="1", Part2="23"}
    };

var concatenatedKeys = keys.Select(m => m.Part1 + "~" + m.Part2).ToList();

var queryable = repository.MyEntity.Where(x => concatenatedKeys.Contains(x.CompositeKey1 + "~" + x.CompositeKey2);

Разделитель (~ в данном случае) является произвольным, то есть символом, который не будет найден в ваших строках.

Это поможет избежать "неправильных" совпадений (например, 12 и 3 против 1 и 23)