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?

Improve this question

4 Answers 4

Reset to default
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;
}
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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??
@t-edd: No, it leverages deferred execution (blogs.msdn.com/b/charlie/archive/2007/12/09/…). That means that IQueryable<T> 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<T>, for instance query.ToList(). At this point - and not earlier - the query expression is translated into SQL and sent to the server.
It's not that good because it assumes that SomeEntity has Name and Quantity fields so this is only half dynamic.
Is there any way to define and or or dynamically?
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"
|
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();
        }
    }
Improve this answer

3 Comments

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....
No, it's not a performance hit, as it uses deferred execution to only query once.
Would "IsNullOrWhiteSpace()" be better than "IsNullOrEmpty()" in case the user enters spaces? Normally one wants such ignored so user is not confused, especially if the option boxes have an "Is Blank" and "Is Not Blank" comparer. By the way is there a way to factor bunches of such comparer options so coding of each one doesn't have to be repeated per text column? (Example comparison drop-down options adjacent to each column: Equals, Not-Equals, Is-Blank, Is-Not-Blank, Contains, Not-Contains, Starts-With, and Ends-With. Numbers and dates can have range-boxes. Users love such filters.)
11

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.

Improve this answer

Comments

1

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.