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

Answer 1

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.

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

Answer 3

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