Dynamic query in LINQ

Question

How do I write a dynamic query for Linq, if I have say Customer class which holds the fields:

string name
string address
int phoneno

I have to query based on information given similar to

query = string.Empty;

if(!string.IsNullorEmpty(name))
{
   query += "@name = name";
}

if(!string.IsNullorEmpty(address))
{
   query += "@address = address";
}

if(!string.IsNullorEmpty(phoneno))
{
   query += "@phoneno = phoneno";
}

var result = from condition in customer
    where(query)
    select condition;

Edit #1:

the items are changeable at run time like

private Customer[] GetCustomers(Dictionary<string,string> attributes)
{
   here the attribute may be, name alone, or name and address, or name address and phoneno


      foreach(string field in attributes.key)
      {
           query += field == attributes[key];

      }

         Customers[] =ExecuteQuery(query);

}

Is this kind of query supported by LINQ?

Edit #2:

Hi Mouk,
As I am new to C#, I am still struggling, this is not working for me.

var query = _ConfigFile.ConnectionMasterSection;

for(int i = 0; i < filter.count; i++)
{
    query = result.Where(p => typeof(ConnectionMaster).GetProperty(filter[i].Attribute).Name == filter[i].Value);
}

This yeilds Empty, where as i used this

var query = _ConfigFile.ConnectionMasterSection;

//Hard coded
res.Where(q => q.category == filter[0].Value);

And it worked as I expected.

Hi Bryan Watts,
I tried your code also and I getting this error: "Lambda Parameter not in scope".

for(int i = 0; i < filter.count; i++)
{
    Field item = filter[i];

    MemberExpression param = Expression.MakeMemberAccess(Expression.Parameter(typeof(Connection), "p"), typeof(Connection).GetProperty(item.Attribute));

    MemberExpression constant = Expression.MakeMemberAccess(Expression.Constant(item), typeof(Field).GetProperty("Value"));
}


try
{
    var myquery = Queryable.Where(coll, Expression.Lambda<Func<Connection, bool>>(
    Expression.Equal(param, constant), Expression.Parameter(typeof(Connection),"p")));
}

What is the mistake here?

Answer 1

Check out this http://www.albahari.com/nutshell/predicatebuilder.aspx, it allows for strongly typed predicate building, it can be really nice. If you want actually dynamic string built predicates than you can use the LINQ Dynamic Query Library provided by ScottGu.

Both will accomplish what you want although I would recommend the first option before the second.

Allowing you to do:

var predicate = PredicateBuilder.True<MyLinqType>();

if(!string.IsNullOrEmpty(name))
    predicate = predicate.And(p => p.name == name);


...

var myResults = Context.MyLinTypeQueryTable.Where(predicate);

And more.

Answer 2

Here you go:

var result = from customer in Customers
             where string.IsNullOrEmpty(phoneNo) || customer.PhoneNo == phoneNo
             where string.IsNullOrEmpty(address) || customer.Address == address
             select customer;

If you're concerned that this generate the optimal SQL query underneath, as always you should attach a SQL Query Analyzer and check. But I believe the expression parser in Linq To Sql will collapse down the where clauses as appropriate based on the value of the arguments.

Answer 3

You can use the fluent interface and add a new Where clause fpr each condition. Something like:

 var result = from cus in customers select cus;
 if(!string.IsNullOrEmpty(name))
         result= result.Where(p => p.Name == name);

EDIT upon hte comment:

if you are querying over a collection in memory, you could retrieve the properties using reflection.

private Customer[] GetCustomers(Dictionary<string,string> attributes)
{
      var result = from cus in customers select cus;    

      foreach(string key in attributes.Keys)
             result= result.Where(p => GetProperty(p, key )== attributes[key]);

         return result.ToList();    
}

Supposing GetProperty retrieve the property by reflection.

Using Linq2Sql this method will result in retrieving all record an then iterating over them using reflection.

Answer 4

See Edit #1 in Original Question.

Answer 5

It sounds like you need to dynamically compose queries.

See my answer to this question.

It explains how queries against an IQueryable<T> are composed by the compiler, and what you can do to add dynamic elements.

Edit

Here is an example of how you would dynamically build a set of Where conditions on top of an IQueryable<Customer>:

// This method ANDs equality expressions for each property, like so:
//
// customers.Where(c => c.Property1 == value1 && c.Property2 == value2 && ...);

private IQueryable<Customer> FilterQuery(IQueryable<Customer> customers, IDictionary<string, string> filter)
{
    var parameter = Expression.Parameter(typeof(Customer), "c");
    Expression filterExpression = null;

    foreach(var filterItem in filter)
    {
        var property = typeof(Customer).GetProperty(filterItem.Key);
        var propertyAccess = Expression.MakeMemberAccess(parameter, property);
        var equality = Expression.Equal(propertyAccess, Expression.Constant(filterItem.Value));

        if(filterExpression == null)
        {
            filterExpression = equality;
        }
        else
        {
            filterExpression = Expression.And(filterExpression, equality);
        }
    }

    if(filterExpression != null)
    {
        var whereBody = Expression.Lambda<Func<Customer, bool>>(filterExpression, parameter);

        customers = customers.Where(whereBody);
    }

    return customers;
}

Answer 6

See Edit #2 In Question.

Answer 7

I've had good experience with Dynamic LINQ.

I used it for a rich HTML table that could be filtered and sorted server side. The server receives a request containing a request parameter where the key is the name of the property (for example 'Lastname') and the value is the value that the property needs to be sorted on (for example 'Smith'). Using that information I built a query string that I passed to the Dynamic LINQ's Where method.

Roughly, you could think of something like the following:

public static IQueryable<T> Filter<T>(this IQueryable<T> query, Dictionary<string, string> dictionary)
{
 Type t = typeof(T);
 StringBuilder sb = new StringBuilder();
       PropertyInfo[] properties = t.GetProperties();
       foreach(string key in dictionary.Keys)
       {
        PropertyInfo property = properties.Where(p => p.Name == key).SingleOrDefault();
        if(property != null)
        {
         if (sb.Length > 0) sb.Append(" && ");

         string value = dictionary[key];

         sb.Append(string.Format(@"{0}.ToString().Contains(""{1}"")", key, value));
        }
       }

       if (sb.Length > 0)
  return query.Where(sb.ToString());
 else
         return query;
}

The code is out of the top of my head and thus untested.

Of course, this is the most basic version: it does a simple string comparison. If you want to have numerical comparison (meaning you want for example the User where UserID is exactly 100, not where the UserID.ToString().Contains("100")), or query nested Properties (Customer.Company.CompanyAddress for example), or query Collections this gets more complicated. You should also think about security: while Dynamic LINQ is not vulnerable to SQL injection, you shouldn't let it blindly parse all user input.