c# entity framework mixing linq to objects with linq to entities

Question

I have database which values that I want to search.

To search something, user needs to select specific checkboxes which value is contained in list in memory (otherwise any change value of checkbox would result saving database, what I want to avoid).

Data from my database are loaded by

.Load()

method.

So, my problem is that idk how to construct query which mix my database values with memory true/false values.

var query1 = from i in db.MyValuesTable
              from j in MyMemoryObjects
              where j.IsTrue && j.Id == i.Tab2Id && i.Value.Contains(_searchPhrase)
              select i;

(from i in db.TableToLoad
from j in query1
where i.CollectionWithValues.Any(x => x.Tab1Id == j.Tab1Id && x.Tab2Id == j.Tab2Id).select i).Load();

My queries may be a bit confusing, so I'll put database schemes below (EF Code First)

//db object
public class MyValuesTable
{
   public int Tab1Id { get; set; }
   public int Tab2Id { get; set; }
   public string Value { get; set; }
}

//memory object
public class MyMemoryObjects
{
   public int Id { get; set; }
   public bool IsTrue { get; set; }
}

//db object
public class TableToLoad
{
   public int Tab1Id { get; set; }
   public int Tab2Id { get; set; }
   public string Value { get; set; }
}

I want to make query which mixes linq to entities and linq to objects returning a correct result (which should be returned if query2 doesn't throw an exception with message: "Unable to create constant value of type only primitive or enumeration types are supported in this context") — d3st1ny
– d3st1ny, Commented Aug 7, 2017 at 8:10

Ivan Stoev · Accepted Answer · 2017-08-07 08:50:42Z

4

Mixing LINQ to Entities with LINQ to Objects is not generally supported. Practically the only supported construct is Contains method applied on in memory primitive type IEnumerable<T>, which translates to SQL IN (value1, value2, ...) clause.

Luckily it's applicable to your scenario since the j.IsTrue && j.Id == i.Tab2Id criteria can be converted to in memory list of j.Id with j.IsTrue filter applied, which then can be used inside the LINQ to Entities query as Contains criteria:

// has to be variable outside of the L2E query
var idFilter = from j in MyMemoryObjects
               where j.IsTrue
               select j.Id;

var query1 = from i in db.MyValuesTable
             where idFilter.Contains(i.Tab2Id) // <-- and use it inside
                 && i.Value.Contains(_searchPhrase)
             select i;

// ...

answered Aug 7, 2017 at 8:50

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

c# entity framework mixing linq to objects with linq to entities

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related