Select multiple columns from table based on different set of multiple columns

.net c# entity-framework-6 linq postgresql

Popular Answer

I don't know an elegant solution to this unpleasant dilemma.

Consider that you only wish to choose the key combinations that are indicated with an asterisk (*) in the list.

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

How can I accomplish this while making Entity Framework happy? Let's examine some potential remedies to determine how effective they are.

Answer 1:Join (or Contains (in pairs)

The best course of action would be to compile a list of the desired pairs, such as Tuples, (List<Tuple<int,int>> ) and use this list to link the database's data:

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

This would be ideal for LINQ to objects, but EF would instead throw an exception like

Unable to create a constant value of type 'System.Tuple`2 (...) Only primitive types or enumeration types are supported in this context.

which is a somewhat awkward way of saying that it cannot convert this statement into SQL.Tuples is not a collection of basic values (such asint or string ).1. For the same reason, a comparable assertion made usingContains (or an other LINQ statement) would not succeed.

Alternative 2: In-memory

Of course, we may simplify the issue using LINQ to objects as follows:

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

It goes without saying that this is a poor choice.db.Table could have a huge number of records.

Alternative 3: TwoContains statements

Give EF two lists of primitive values then.[1,2] for Id1 and [2,3] for Id2 . As join is not something we want to utilize (see side note), let's useContains :

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

However, the results now include include an entity.{1,3} ! Of course, this entity completely satisfies the requirements of the two predicates. But let's not forget that we are drawing near. We now only acquire four of the millions of entities that were previously pulled into memory.

Choice 4: OneContains using calculated values

Solution 3 was unsuccessful due of the two differentContains Statements filter more than just their values' last 126 bits. What if we first make a list of possible combinations and then attempt to match them? Since this list should include primitive values, we can infer this from solution 1. For illustration:

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

and the LINQ assertion

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

This strategy comes with several drawbacks. You will first notice that this also returns entity.{1,6} The combination function (a*b) does not yield results that permit a pair in the database to be identified specifically. Now that we have a list of strings, we could make["Id1=1,Id2=2","Id1=2,Id2=3]" and do

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

(EF6 would be the only version where this would work.)

Quite a mess is developing here. The fact that this method is not sargable, however, is a more significant issue because it circumvents any database indexes onId1 and Id2 that could have served another purpose. This will function incredibly horribly.

The best of options two and three is proposed as solution number five.

Therefore, a mix of is the only workable approach I can conceive of.Contains and ajoin In my head: Start by completing the contains statement as in solution 3. Recall that it brought us extremely near to achieving our goals. The query result can then be improved by joining it as an in-memory list:

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;

It's not particularly elegant or tidy, but so far it's the only scalable solution to this issue that I have discovered and implemented in my own code.

Build a query with OR clauses as a sixth option.

You can create a query that has an OR clause for each item in the list of combinations using a predicate builder like Linqkit or an equivalent. It's possible that this will work for really brief lists. The query will start performing terribly with a few hundred entries. Therefore, unless you can be absolutely certain that there will only ever be a tiny number of elements, I don't think this is a good option. This option has one elaboration, which can be found at here.


1 Funny side note: When you join a primitive list, EF does generates a SQL statement like follows.

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

However, the produced SQL is... ludicrous. An instance from the real worldMyIntegers only 5 (five) numbers, and it looks like this:

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]

Existing are n-1UNION That obviously isn't scalable at all.

After the fact:
This has been improved somewhere along the way to EF version 6.1.3. TheUNION s are no longer nested and are more simpler. When there were fewer than 50 elements in the local sequence, the query would previously fail (SQL exception: Your SQL statement has a portion that is nested too deeply..) it is not nestedUNION permit local sequences with up to a few thousand(!) elements. Although it has "many" parts, it is still slow.

2 Regarding theContains Scalable statement: Scalable contains a LINQ method that connects to a SQL backend

40
5/23/2017 12:02:42 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow