How to query by composite keys using ORM + LINQ?

Question

I have an array of in memory objects that represent the composite keys that I want to query by eg:

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

Now say I have an IQueryable<Table1> and I want to return all the records with the above Keys. Table1 does not have a surrogate key, it has a composite key made up of 2 columns. I also want to avoid per row retrieval.

How can I do this? I tried standard join, but for obvious reasons my ORM is confused when in memory objects is thrown in the mix.

Answer 1

In similar cases I've found the best solution to be one in which a first selection is done in the database, followed by the exact matches in memory:

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;

If you've got Keys

1,2
2,1

then dbSelection will also contain {1,1} and {2,2} (but not the vast majority of other records). These are filtered out by the second query.

The advantage is that the database query can take advantage of indexes, which is not possible if you work with computed keys (like concatenated key values).

The downside is that you must ensure that parts1 and parts2 can't grow excessively, otherwise the SQL IN statements will become highly inefficient or even crash because of too many elements (we're talking many thousands of items here for Sql Server). But this is true for any solution using Contains.

Answer 2

A workaround could to to work with concatenation

something like that.

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

The separator (~ in this case) is arbitrary, meant to be a char that won't be found in your strings.

It 's there to avoid "wrong" matches (12 and 3 vs 1 and 23, for example)