Apply "List" of IQueryables to a target?

Question

I have this idea to create a "list" of IQueryables that do different kinds of operations.

So basically:

var query1 = Enumerable.Empty<Person>().AsQueryable().Where(e => e.Name == "Ronald");
var query2 = Enumerable.Empty<Person>().AsQueryable().Where(e => e.Age == 43);
var query3 = Enumerable.Empty<Person>().AsQueryable().Select(e => e.EyeColor);

var listOfQueries = new List<IQueryable<Person>
{
    query1,
    query2,
    query3
};

Now, I also have this DbSet full of "Persons" and I would like to "apply" all my queries against that DbSet. How would I do that? Is it even possible?

An updated example:

        var personQueryFactory = new PersonQueryFactory();
        var personQueryByFirstname = personQueryFactory.CreateQueryByFirstname("Ronald");           //Query by Firstname.
        var personQueryByAge = personQueryFactory.CreateQueryByAge(42);                             //Query by age.
        var personQueryByHasChildWithAgeOver = personQueryFactory.CreateQueryByChildAgeOver(25);    //Query using a "join" to the child-relationship.
        var personQuerySkip = personQueryFactory.Take(5);                                           //Only get the 5 first matching the queries.

        var personQuery = personQueryFactory.AggregateQueries                                       //Aggragate all the queries into one single query.
        (
            personQueryByFirstname,
            personQueryByAge,
            personQueryByHasChildWithAgeOver,
            personQuerySkip
        );

        var personSurnames = personsService.Query(personQuery, e => new { Surname = e.Surname });          //Get only the surname of the first 5 persons with a firstname of "Ronald" with the age 42 and that has a child thats over 25 years old.
        var personDomainObjects = personsService.Query<DomainPerson>(personQuery);          //Get the first 5 persons as a domain-object (mapping behind the "scenes") with a firstname of "Ronald" with the age 42 and that has a child thats over 25 years old.
        var personDaos = personsService.Query(personQuery);          //Get the first 5 persons as a DAO-objects/entityframework-entities with a firstname of "Ronald" with the age 42 and that has a child thats over 25 years old.

The reason for doing this would be to create a more "unified" way of creating and re-using predefined queries, and then to be able to execute them against the DbSet and return the result as a domain-object and not an "entity-framework-model/object"

I think your query variables are the resultset not the queries. — Gaurang Dave, Apr 25 '18 at 08:44
Possible duplicate of [Is there any way to create a LINQ query as a variable without having the data source (yet)?](https://stackoverflow.com/questions/8683391/is-there-any-way-to-create-a-linq-query-as-a-variable-without-having-the-data-so) — Gaurang Dave, Apr 25 '18 at 08:47
Is that what you want to do ? https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/expression-trees/how-to-use-expression-trees-to-build-dynamic-queries — Ahmad Ibrahim, Apr 25 '18 at 09:00
@GaurangDave: That's not a duplicate. The answer to the posted question is `Func`, but OP is essentially asking how to _merge multiple_ `Func` objects. — Flater, Apr 25 '18 at 09:00

score 5 · Answer 1 · edited Jun 20 '20 at 09:12

This is possible, but you need to reframe your approach slightly.

Storing the filters

var query1 = Enumerable.Empty<Person>().AsQueryable().Where(e => e.Name == "Ronald");
var query2 = Enumerable.Empty<Person>().AsQueryable().Where(e => e.Age == 43);
var query3 = Enumerable.Empty<Person>().AsQueryable().Select(e => e.EyeColor);

Reading your intention, you don't actually want to handle IQueryable objects, but rather the parameter that you supply to the Where method.

Edit: I missed that the third one was a Select(), not a Where(). Ive adjusted the rest of the answer as if this had also been a Where(). See the comments below for my response as to why you can't mix Select() with Where() easily.

Func<Person,bool> filter1  = (e => e.Name == "Ronald");
Func<Person,bool> filter2  = (e => e.Age == 43);
Func<Person,bool> filter3  = (e => e.EyeColor == "Blue");

This stores the same information (filter criteria), but it doesn't wrap each filter in an IQueryable of its own.

A short explanation

Notice the Func<A,B> notation. In this case, A is the input type (Person), and B is the output type (bool).

This can be extended further. A Func<string,Person,bool> has two input parameters (string, Person) and one output parameter (bool). A usage example:

Func<string, Person, bool> filter = (inputString, inputPerson) => inputString == "TEST" && inputPerson.Age > 35;

There is always one output parameter (the last type). Every other mentioned type is an input parameter.

Putting the filters in a list

Intuitively, since Func<Person,bool> represents a single filter; you can represent a list of filters by using a List<Func<Person,bool>>.

Nesting generic types get a bit hard to read, but it does work just like any other List<T>.

List<Func<Person,bool>> listOfFilters = new List<Func<Person,bool>>()
{
    (e => e.Name == "Ronald"),
    (e => e.Age == 43),
    (e => e.EyeColor == "Blue")
};

Executing the filters

You're in luck. Because you want to apply all the filters (logical AND), you can do this very easily by stacking them:

var myFilteredData = myContext.Set<Person>()
                                 .Where(filter1)
                                 .Where(filter2)
                                 .Where(filter3)
                                 .ToList();

Or, if you're using a List<Func<Person,bool>>:

var myFilteredData = myContext.Set<Person>().AsQueryable();

foreach(var filter in listOfFilters)
{
    myFilteredData = myFilteredData.Where(filter);
}

Fringe cases

However, if you were trying to look for all items which fit one or more filters (logical OR), it becomes slightly more difficult.

The full answer is quite complicated.. You can check it here.

However, assuming you have the filters set in known variables, there is a simpler method:

Func<Person, bool> filterCombined = 
              e => filter1(e) || filter2(e) || filter3(e);

var myFilteredData = myContext.Set<Person>()
                                 .Where(filterCombined)
                                 .ToList();

Thanks for a very well written post! You are perfectly correct, I could actually just turn it all into Func and then just execute that "list" of Func against my DbSet, however.. the problem is that in query3 Im doing a .Select(e => e.EyeColor) and not a .Where(myPredicate), so could I actually do Set().Where(query3) and expect that to be "interpreted" into a Select? Or would I need to "differentiate" my queries and then execute them to the corresponding APIs?(Not sure if this is an entierly different question.. please let me know and Ill create it as a new one) — Inx51, Apr 25 '18 at 09:27
@Inx51: A `Select()` has a different type (`Func`) so you can't add it to the list. I don't see a way to blindly handle both `Where()` and `Select()`. If you were doing `Select(e => e.IsAlive)`, the parameter would also be `Func`, which makes it impossible for your code to figure out whether you want to do a `Where()` or a `Select()`. Furthermore, the order of operations is important here (your `Where()` might not work after you've done a `Select()`). You'll need to handle these cases separately because it otherwise opens the door to many problems. — Flater, Apr 25 '18 at 09:41

score 1 · Answer 2 · answered Apr 25 '18 at 13:15

One of the problems is that the collection of IQueryable is only valid as long as your DbSet is valid. As soon as your DbContext is Disposed your carefully filled collection is worthless.

So you have to think of another method to reconstruct the query than the one that uses the DbSet<Person>

Although at first glance they seem the same, there is a difference between IEnumerable and IQueryable. An Enumerable has everything in it to enumerate over the resulting sequence.

A Queryable on the other hand holds an Expression and a Provider. The Provider knows where the data can be fetched. This is usually a database, but it can also be a CSV-file or other items where you can fetch sequences. It is the task of the Provider to interpret the Expression and to translate it info a format that the database can understand, usually SQL.

While concatenating the linq statements into one big linq statements, the database is not accessed. Only the Expression is changed.

Once you call GetEnumerator() and MoveNext() (usually by doing ForEach, or ToList(), or similar), the Expression is sent to the Provider who will translate it into a query format that the database understands and perform the query. the result of the query is an Enumerable sequence, so Getenumerator() and MoveNext() of the provider's query result are called.

Because your IQueryable holds this Provider, you can't enumerate anymore after the Provider has been disposed.

When using entity framework, the DbSet holds the Provider. In the Provider is the Database information held by the DbContext. Once you Dispose the DbContext you can't use the IQueryable anymore:

IQueryable<Person> query = null;
using (var dbContext = new MyDbcontext())
{
     query = dbContext.Persons.Where(person => person.Age > 20);
}
foreach (var person in query)
{
      // expect exception: the DbContext is already Disposed
}

So you can't put the Provider in your collection or possible queries. However, you could remember the Expression. The only thing your require from your Expression is that it returns a Person. You also need a function that takes this Expression and a QueryaProvider for Persons to convert it to an IQueryable.

Let's create a generic function for this, so It can be used for any type, not just for Persons:

static IQueryable<TSource> ToQueryable<TSource>(this IQueryProvider provider, 
    Expression expression)
{
     return provider.CreateQuery(expression);
}

// well, let's add the following also:

static IQueryable<Tsource> ToQueryable<TSource>(this DbContext dbContext, 
     Expression expression)
{
     return dbContext.Set<TSource>.Provider.ToQueryable<TSource>(expression);
}

For help on extension functions see Extension Functions Demystified

Now only once you create your collection of Expressions. For fast lookup make it a Dictionary:

enum PersonQuery
{
    ByFirstname,
    ByAge,
    ByHasChildWithAgeOver,
    Skip,
}

public IReadOnlyDictionary<PersonQuery, Expression> CreateExpressions()
{
    Dictionary<PersonQuery, Expression> dict = new Dictionary<PersonQuery, Expression>();
    using (var dbContext = new MyDbContext())
    {
         IQueryable<Person> queryByFirstName = dbContext.Persons
             .Where(...);
         dict.Add(PersonQuery.ByfirstName, queryByFirstName.Expression);
         ... // etc for the other queries
    }
    return dict.
}

Usage:

IReadOnlyCollection<Person> PerformQuery(PersonQuery queryId)
{
     using (var dbContext = new MyDbContext())
     {
          // get the Expression from the dictionary:
          var expression = this.QueryDictionary[queryId];
          // translate to IQueryable:
          var query = dbContext.ToQueryable<Person>(expression);
          // perform the query:
          return query.ToList();

          // because all items are fetched by now, the DbContext may be Disposed
     }
}

Thanks! This should be a valid "embryo" for my example above :) — Inx51, Apr 25 '18 at 13:20

Apply "List" of IQueryables to a target?

2 Answers2

Storing the filters

Putting the filters in a list

Executing the filters