49

I would like to know what is the best way of creating dynamic queries with entity framework and linq.

I want to create a service that has many parameters for sorting and filtering (over 50). I will be getting object from gui where these will be filled out... and query will be executed from a single service method.

I looked around And I saw that I could dynamically create a string that can be executed at the end of my method. I don't like this way very much. Is there a better way to do this? Preferably type safe with compile check?

Gabe Moothart
  • 31,211
  • 14
  • 77
  • 99
Eduard
  • 3,176
  • 3
  • 21
  • 31

4 Answers4

65

You could compose an IQueryable<T> step by step. Assuming you have a FilterDefinition class which describes how the user wants to filter ...

public class FilterDefinition
{
    public bool FilterByName { get; set; }
    public string NameFrom { get; set; }
    public string NameTo { get; set; }

    public bool FilterByQuantity { get; set; }
    public double QuantityFrom { get; set; }
    public double QuantityTo { get; set; }
}

... then you could build a query like so:

public IQueryable<SomeEntity> GetQuery(FilterDefinition filter)
{
    IQueryable<SomeEntity> query = context.Set<SomeEntity>();
    // assuming that you return all records when nothing is specified in the filter

    if (filter.FilterByName)
        query = query.Where(t => 
            t.Name >= filter.NameFrom && t.Name <= filter.NameTo);

    if (filter.FilterByQuantity)
        query = query.Where(t => 
            t.Quantity >= filter.QuantityFrom && t.Quantity <= filter.QuantityTo);

    return query;
}
Slauma
  • 175,098
  • 59
  • 401
  • 420
  • 1
    Thank you, but how dows this work? Doesnt this pull all the data from database and then step by step narrow it down to desired set of data?? – Eduard Apr 05 '11 at 06:36
  • 7
    @t-edd: No, it leverages `deferred execution` (http://blogs.msdn.com/b/charlie/archive/2007/12/09/deferred-execution.aspx). That means that `IQueryable` which is composed in the example above is only a query expression which describes how the data are filtered. The real execution of the query isn't in the example at all. You execute the query then by applying a "greedy" operator to `IQueryable`, for instance `query.ToList()`. At this point - and not earlier - the query expression is translated into SQL and sent to the server. – Slauma Apr 05 '11 at 10:03
  • It's not that good because it assumes that `SomeEntity` has Name and Quantity fields so this is only half dynamic. – Maciej Szpakowski Jan 23 '17 at 17:56
  • Is there any way to define `and` or `or` dynamically? – Yusril Maulidan Raji Apr 20 '17 at 08:14
  • it also doesn't allow for dynamic conditional operators, for example if you wanted to dynamically allow users to filter by "cost > 10" or "cost < 10" – ferr Sep 14 '17 at 18:01
  • This is exactly what I am looking for. – Ji_in_coding Apr 16 '18 at 02:37
40

The only other way that I know of would be to build an IQueryable based on your filter vaues.

    public List<Contact> Get(FilterValues filter)
    {
        using (var context = new AdventureWorksEntities())
        {
            IQueryable<Contact> query = context.Contacts.Where(c => c.ModifiedDate > DateTime.Now);

            if (!string.IsNullOrEmpty(filter.FirstName))
            {
                query = query.Where(c => c.FirstName == filter.FirstName);
            }

            if (!string.IsNullOrEmpty(filter.LastName))
            {
                query = query.Where(c => c.LastName == filter.LastName);
            }

            return query.ToList();
        }
    }
BrandonZeider
  • 8,014
  • 2
  • 23
  • 20
  • Yes, but is this efective performance wise? When is the select executed? In the end when ToList() is called? Imagine I have very large set of data.... – Eduard Apr 05 '11 at 06:38
  • 4
    No, it's not a performance hit, as it uses deferred execution to only query once. – BrandonZeider Apr 05 '11 at 12:54
10

I have created a generic repository which should help you. It supports uniform API to query and sort on both known and dynamic fields: 

       //Filter on known fields
       var keyboard = Query<Product>.Create(p=>p.Category=="Keyboard");
       var keyboards = repository.Get(keyboard);

       //Or filter on dynamic fields
       var filter = Query<Product>.Create("Rating", OperationType.GreaterThan, 4)
       var filteredKeyboards = repository.Get(filter);

       //You can also combine two queries togather
       var filterdKeyboards2 = repository.Get(keyboard.And(filter))

       //Order it on known fields
       var orderedKeyboard = keyboard.OrderBy(o=>o.Asc(p=>p.Name));
       var orderedKeyboards = repository.Get(orderedKeyboard);

       //Or order by on dynamic fields
       var userOrdering = keyboard.OrderBy(o=>o.Asc("Name"));
       var orderedKeyboards2 = repository.Get(userOrdering);

I do not know about the search object/DTO you're getting but you can easily create a generic search object/DTO and can map it to a Query object in few lines of code. I have used it in past around a WCF service and it has worked very well for me.

Gurmit Teotia
  • 179
  • 2
  • 7
1

You could look into creating the service using WCF Data Services and dynamically create the URI to query your entity model.

Thomas Li
  • 3,358
  • 18
  • 14