Nrthugu

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?

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;

}

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();

        }

    }

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.

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