c# - EntityFramework - contains query of composite key -

c# - EntityFramework - contains query of composite key -

given list of ids, can query relevant rows by:

context.table.where(q => listofids.contains(q.id));

but how accomplish same functionality when table has composite key?

this nasty problem don't know elegant solution.

suppose have these key combinations, , want select marked ones (*).

id1 id2 --- --- 1 2 * 1 3 1 6 2 2 * 2 3 * ... (many more)

how way entity framework happy? let's @ possible solutions , see if they're good.

solution 1: join (or contains) pairs

the best solution create list of pairs want, instance tuples, (list<tuple<int,int>>) , bring together database info list:

from entity in db.table // db dbcontext bring together pair in tuples on new { entity.id1, entity.id2 } equals new { id1 = pair.item1, id2 = pair.item2 } select entity

in linq objects perfect, but, bad, ef throw exception like

unable create constant value of type 'system.tuple`2 (...) primitive types or enumeration types supported in context.

which rather clumsy way tell can't translate statement sql, because tuples not list of primitive values (like int or string).1. same reason similar statement using contains (or other linq statement) fail.

solution 2: in-memory

of course of study turn problem simple linq objects so:

from entity in db.table.asenumerable() // fetch db.table memory first bring together pair tuples on new { entity.id1, entity.id2 } equals new { id1 = pair.item1, id2 = pair.item2 } select entity

needless not solution. db.table contain millions of records.

solution 3: 2 contains statements

so let's offer ef 2 lists of primitive values, [1,2] id1 , [2,3] id2. don't want utilize bring together (see side note), let's utilize contains:

from entity in db.table ids1.contains(entity.id1) && ids2.contains(entity.id2) select entity

but results contains entity {1,3}! well, of course, entity matches 2 predicates. let's maintain in mind we're getting closer. in stead of pulling millions of entities memory, 4 of them.

solution 4: 1 contains computed values

solution 3 failed because 2 separate contains statements don't filter combinations of values. if create list of combinations first , seek match these combinations? know solution 1 list should contain primitive values. instance:

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

and linq statement:

from entity in db.table computed.contains(entity.id1 * entity.id2) select entity

there problems approach. first, you'll see returns entity {1,6}. combination function (a*b) not produce values uniquely identify pair in database. create list of strings ["id1=1,id2=2","id1=2,id2=3]" , do

from entity in db.table computed.contains("id1=" + entity.id1 + "," + "id2=" + entity.id2) select entity

(this work in ef6, not in before versions).

this getting pretty messy. more of import problem solution not sargable, means: bypasses database indexes on id1 , id2 have been used otherwise. perform very poorly.

solution 5: best of 2 , 3

so viable solution can think of combination of contains , join in memory: first contains statement in solution 3. remember, got close wanted. refine query result joining result in-memory list:

var rawselection = entity in db.table ids1.contains(entity.id1) && ids2.contains(entity.id2) select entity; var refined = entity in rawselection.asenumerable() bring together pair in tuples on new { entity.id1, entity.id2 } equals new { id1 = pair.item1, id2 = pair.item2 } select entity;

it's not elegant, messy same maybe, far it's scalable2 solution problem found, , applied in own code.

solution 6: build query or clauses

using predicate builder linqkit or alternatives, can build query contains or clause each element in list of combinations. viable alternative really short lists. couple of hundreds of elements query start performing poorly. don't consider solution unless can 100% sure that there little number of elements.

1as funny side note, ef does create sql statement when bring together primitive list, so

from entity in db.table // db dbcontext bring together in myintegers on entity.id1 equals select entity

but generated sql is, well, absurd. real-life illustration myintegers contains 5(!) integers looks this:

class="lang-sql prettyprint-override">select [extent1].[cmpid] [cmpid], [extent1].[name] [name], [dbo].[company] [extent1] inner bring together (select [unionall3].[c1] [c1] (select [unionall2].[c1] [c1] (select [unionall1].[c1] [c1] (select 1 [c1] ( select 1 x ) [singlerowtable1] union select 2 [c1] ( select 1 x ) [singlerowtable2]) [unionall1] union select 3 [c1] ( select 1 x ) [singlerowtable3]) [unionall2] union select 4 [c1] ( select 1 x ) [singlerowtable4]) [unionall3] union select 5 [c1] ( select 1 x ) [singlerowtable5]) [unionall4] on [extent1].[cmpid] = [unionall4].[c1]

there n-1 unions. of course of study that's not scalable @ all.

later addition: somewhere along road ef version 6.1.3 has been improved. unions have become simpler , no longer nested. query give less 50 elements in local sequence (sql exception: some part of sql statement nested deeply.) non-nested union allow local sequences couple of thousands(!) of elements. it's still slow though "many" elements.

2as far contains statement scalable: scalable contains method linq against sql backend

c# .net entity-framework