Dynamic query building with entity framework core - Build a query "by steps"

Question

I'm trying to make a class to perform a dynamic filters, depending on what parameters I'll send to its "filter" method but I'm facing some difficulties on building my query.

It's more easy to explain with my following code, where I'm simply trying to apply some filters basing on what i receive in input:

public class PeopleManager : Controller
    {
        private readonly MyPeopleContext _context;
        public PeopleManager(PeopleContext context)
        {
            _context = context;
        }

        public IEnumerable<Person> filter (int? id, string name, int? age)
        {
            var peoplequery = _context.People;
            if(id!=null)
            {
               peoplequery = peoplequery .Where(p => p.Id.Equals(id));
            }

            if(name!="")
            {
               peoplequery = peoplequery .Where(p => p.Name.Contains(name));
            }

            if(age!=null)
            {
               peoplequery  = peoplequery .Where(p => p.Age.Equals(age));
            }

            return peoplequery;
        }
    }

Obviously, my code is not working and according to this question that I've found:

Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Data.Entity.DbSet'

I've managed to edit my code as follows:

public IEnumerable<Person> filter (int? id, string name, int? age)
            {
                var people = _context.People;

                var peoplequery = from p in people select people;

                if(id!=null)
                {
                   peoplequery = peoplequery .Where(p => p.Id.Equals(id));
                }

                if(name!="")
                {
                   peoplequery = peoplequery .Where(p => p.Name.Contains(name));
                }

                if(age!=null)
                {
                   peoplequery  = peoplequery .Where(p => p.Age.Equals(age));
                }

                return peoplequery;
            }

But doing this, I can't access to people's properties like id, name, etc... in my lambda expressions.

How can I build this conditional query without any error?

Remove `var` inside `if` blocks. Currently it won't even compile. Also at the beginning use simply `var peoplequery = _context.People.AsQueryable();` — Ivan Stoev, Apr 10 '18 at 10:14
@IvanStoev done, it was a copy-paste error while writing the question. I update the question content right now — , Apr 10 '18 at 10:17
Then apply `AsQueryable()` and you are done :) I mean, instead of `from p in ...`. — Ivan Stoev, Apr 10 '18 at 10:18
@IvanStoev So it will figure out as: `var people= _context.People.AsQueryable();`, without declaring the `var peoplequery = from p in people select people;` right? — , Apr 10 '18 at 10:23
Right. Same as `IQueryable people = _context.People;` Or `var people = from p in _context.People select p;` (note `select p`, not `people`). But `AsQueryable()` is the shorter. — Ivan Stoev, Apr 10 '18 at 10:27
@IvanStoev Thanks! Now it works fine! Just a quick question that for a beginner like me could be useful... In your advice, are there any risks on returning an IQueryable on this filter method, that actually expects to return an IEnumerable? — , Apr 10 '18 at 10:31
If you look at the declaration of the interfaces, you will see that `IQueryable` inherits `IEnumerable`, so no problem (from OOP, queryablle *is a* enumerable) — Ivan Stoev, Apr 10 '18 at 10:34
changing `var peoplequery = _context.People;` to `IQueryable peoplequery = _context.People;` should sort this — Marc Gravell, Apr 10 '18 at 10:34

score 4 · Answer 1 · answered Jul 18 '18 at 12:01

I guess you want to have smth like this:

public class PeopleController : Controller
{
    private readonly MyPeopleContext _context;

    public PeopleManager(PeopleContext context)
    {
        _context = context;
    }

    public IEnumerable<Person> Filter (int? id, string name, int? age)
    {
        var peoplequery = _context.People.AsQueryable();
        if(id.HasValue)
        {
           peoplequery = peoplequery.Where(p => p.Id == id.Value);
        }
        if(!string.IsNullOrEmpty(name))
        {
           peoplequery = peoplequery.Where(p => p.Name.Contains(name));
        }
        if(age.HasValue)
        {
           peoplequery = peoplequery.Where(p => p.Age == age.Value);
        }

        return peoplequery.ToArray();
    }
}

Dynamic query building with entity framework core - Build a query "by steps"

1 Answers1