Linq to entities - SQL Query - Where list contains object with 2 properties (or more)

Question

Having the following example:

 var myIds = db.Table1.Where(x=>x.Prop2 == myFilter).Select(x=>x.Id).ToList();
 var results = db.Table2.Where(x=> myIds.Contains(x.T1)).ToList();

This part is straight forward.

However, now I am facing a "slight" change where my "filter list" has 2 properties instead of only one:

// NOTE: for stackoverflow simplification I use a basic query to 
// get my "myCombinationObject".
// In reality this is a much more complex case, 
// but the end result is a LIST of objects with two properties.
var myCombinationObject = db.Table3.Where(x=>x.Prop3 == myFilter)
                                   .Select(x=> new { 
                                          Id1 = x.T1, 
                                          Id2 = x.T2
                                    }).ToList();

 var myCombinationObjectId1s = myCombinationObject.Select(x=>xId1).ToList();
 var myCombinationObjectId2s = myCombinationObject.Select(x=>xId2).ToList();

 // step#1 - DB SQL part
 var resultsRaw = db.Tables.Where( x=> 
                     myCombinationObjectId1s.Contains(x.Prop1) 
                  || myCombinationObjectId2s.Contains(x.Prop2))
                .ToList();
//  step#2 - Now in memory side - where I make the final combination filter.
var resultsFiltered = resultsRaw.Where( x=>
            myCombinationObject.Contains( 
                       new {Id1 = x.Prop1, Id2 = x.Prop2 }
            ).ToList();

My question: is it even possible to merge the step#2 in the step#1 (query in linq to entities) ?

Unfortunately, it is complex problem.. Please read my question - maybe something will be useful: http://stackoverflow.com/questions/25563338/linq-simulating-multiple-columns-in-in-clausule — , May 28 '15 at 08:57

score 2 · Answer 1 · answered May 28 '15 at 08:48

2

Can you do something like this:

var result= 
        db.Tables
           .Where(t=> 
              db.Table3
                 .Where(x=>x.Prop3 == myFilter)
                 .Any(a=>a.T1==t.Prop1 || a.T2==t.Prop2)
         ).ToList();

answered May 28 '15 at 08:48

Arion

31,011
10
70
88

Based on my question where I simplified my problem (easier to migrate to stackoverflow) with the myCombinationObject, yes, I would say yes, it is a correct answer. However in reality my myCombinationObject does NOT come from db.Table3, instead is the result of an other complex LOONNNGGG query where even use "lets of the type "let temp1 = xxxxxx" and so on..... and for that I still do not have a solution.... Any way thank you for the answer I will definitely use it in the future for other cases). – Dryadwoods May 28 '15 at 09:04

mr100 · Accepted Answer · 2015-05-29T03:52:52.837

I've managed once to do what you want, however it is pretty hard and requires changing entity model a bit. You need an entity to map type

new {Id1 = x.Prop1, Id2 = x.Prop2 }

So you need enity having 2 properties - Id1 and Id2. If you have one - great, if not then add such entity to your model:

public class CombinationObjectTable
{
    public virtual Guid Id1 { get; set; }
    public virtual Guid Id2 { get; set; }
}

Add it to your model:

public DbSet<CombinationObjectTable> CombinationObjectTable { get; set; }

Create new migration and apply it database (database will have now additional table CombinationObjectTable). After that you start to build a query:

DbSet<CombinationObjectTable> combinationObjectTable = context.Set<CombinationObjectTable>();
StringBuilder wholeQuery = new StringBuilder("DELETE * FROM CombinationObjectTable");
foreach(var obj in myCombinationObject)
{
    wholeQuery.Append(string.Format("INSERT INTO CombinationObjectTable(Id1, Id2) VALUES('{0}', '{1}')", obj.Id1, obj.Id2);
}
wholeQuery.Append(
    db.Tables
        .Where( x=> 
                 myCombinationObjectId1s.Contains(x.Prop1) 
              || myCombinationObjectId2s.Contains(x.Prop2))
        .Where( x=>
           combinationObjectTable.Any(ct => ct.Id1 == x.Id1 && ct.Id2 == x.Id2)
        ).ToString();
    );

 var filteredResults = context.Tables.ExecuteQuery(wholeQuery.ToString());

Thanks to this your main query stays written in linq. If you do not want to add new table to your db this is as well achievable. Add new class CombinationObjectTable to model, generate new migration to add it and afterwards remove code creating that table from migration code. After that apply migration. This way the db schema won't be changed but EF will think that there is CombinationObjectTable in database. Instead of it you will need to create a temporary table to hold data:

StringBuilder wholeQuery = new StringBuilder("CREATE TABLE #TempCombinationObjectTable(Id1 uniqueidentifies, Id2 uniqueidentifier);");

And when you invoke ToString method on your linq query change CombinationObjectTable to #TempCombinationObjectTable:

...
.ToString()
.Replace("CombinationObjectTable", "#TempCombinationObjectTable")

Other thing worth considering would be using query parameters to pass values in INSERT statements instead of just including them in query yourself - this is of course achievable with EF as well. This solution is not fully ready to apply, rather some hint in which direction you may go for the solution.

score 1 · Answer 3 · answered May 28 '15 at 10:05

1

If you simply want to avoid the intermediate result (and also creating a second intermediary list) you can do the following

var resultsFiltered = db.Tables.Where( x=> 
                 myCombinationObjectId1s.Contains(x.Prop1) 
              || myCombinationObjectId2s.Contains(x.Prop2))
            .AsEnumerable() // everything past that is done in memory but isn't materialized immediately, keeping the streamed logic of linq
            .Where( x=>
                 myCombinationObject
                     .Contains(new {Id1 = x.Prop1, Id2 = x.Prop2 })
            .ToList();

answered May 28 '15 at 10:05

Ronan Thibaudau

3,413
3
29
78

I appreciate your help, but you are not providing me with any answer to my question ( only one query LINQ TO ENTITIES). – Dryadwoods May 28 '15 at 10:15
@Dryadwoods It would be much easier to tell you if all of it can be done in one query if we had the actual not "simplified for stackoverflow" version of your code, if you want to do it all on the DB side we need to know what you're doing exactly – Ronan Thibaudau May 28 '15 at 10:17

Linq to entities - SQL Query - Where list contains object with 2 properties (or more)

3 Answers3