locked
How to generate a dynamic LINQ query? RRS feed

  • Question

  •  

    Hi, here is my question ... I have a Linq query as such:

     

     

                var query = from p in vrlQueryResults
                            where p.Station.ToString().Contains(filter)
                               || p.Duration.ToString().Contains(filter)
                               || p.DateTime.ToString().Contains(filter)
                               || p.FlagBits.ToString().Contains(filter)
                               || p.FilePath.ToString().Contains(filter)
                            select new { p.DateTime, p.Station, p.Duration, p.FlagBits, p.FilePath };

     

    Now, that works fine, however ...

     

    I want the user to be able to DYNAMICALLY select which fields the the ".ToString().Contains(filter)" is performed on.

     

    In other words, the user might want to check more or less fields than are listed above.

     

    I have tried to figure out how to do this using expression trees, but none of the examples I have seem were sufficient to show how to do this.

     

    If someone knows how this can be performed, please let me know.  Thank you

    Saturday, December 22, 2007 12:58 AM

Answers

  • Woo, what a journey.

     

    I learned about Expressions at this link:

    http://community.bartdesmet.net/blogs/bart/archive/2007/04/06/the-iqueryable-tales-linq-to-ldap-part-2-getting-started-with-iqueryable-t.aspx

     

     

    Then I built to your specification, this code:

     

    Code Block

    public static void DynamicQuery()

    {

      List<string> myFilterTargets = new List<string>();

      myFilterTargets.Add("CustomerName");

      myFilterTargets.Add("CustomerID");

     

      string myFilter = "Bob";

     

      DataClasses1DataContext myDC = new DataClasses1DataContext();

     

      var q = myDC.Customers.Where(GetFilter(myFilterTargets, myFilter));

     

      Console.WriteLine("--Query--");

      Console.WriteLine(myDC.GetCommand(q).CommandText);

      Console.WriteLine("--Results--");

      foreach (var c in q)

      {

        Console.WriteLine(c.CustomerName);

      }

    }

     

    public static System.Linq.Expressions.Expression<Func<Customer, bool>> GetFilter(IEnumerable<string> FilterTargets, string Filter)

    {

      ParameterExpression c = Expression.Parameter(typeof(Customer), "c");

      Type[] ContainsTypes = new Type[1];

      ContainsTypes[0] = typeof(string);

      System.Reflection.MethodInfo myContainsInfo = typeof(string).GetMethod("Contains", ContainsTypes);

     

      List<Expression> myFilterExpressions =

    FilterTargets.Select<string, Expression>(s =>

      Expression.Call(

        Expression.Call(

          Expression.Property(c, typeof(Customer).GetProperty(s)),

          "ToString",

          null,

          null

        ),

        myContainsInfo,

        Expression.Constant(Filter)

      )

    ).ToList();

     

    //build a one-sided tree of Or's from this collection.

      Expression OrExpression = null;

      foreach (Expression myFilterExpression in myFilterExpressions)

      {

        if (OrExpression == null)

        {

          OrExpression = myFilterExpression;

        }

        else

        {

          OrExpression = Expression.Or(myFilterExpression, OrExpression);

        }

      }

    //lambda is where the magic happens.

      Expression<Func<Customer, bool>> predicate =

        Expression.Lambda<Func<Customer, bool>>(OrExpression, c);

     

      Console.WriteLine("--predicate--");

      Console.WriteLine(predicate.ToString());

     

      return predicate;

    }

     

     

     

    Enjoy!

     

    Saturday, December 22, 2007 3:50 AM
  • Dmitry,

     

    It's easy extend IQueryable as your've shown in case with "AND" composition of filters (note that aemami needs "OR" operator).

    This is possible with "Union" method:

     

    Code Block

    var result = (from p in vrlQueryResults
    where p.Station.ToString().Contains(filter)

    select p).Union

    (from p in vrlQueryResults
    where p.Duration.ToString().Contains(filter)

    select p);

     

     

    The problem is that in case of complex queries LinqToSql and SQL Server query optimizers not always will be able to produce optimal execution plan.

     

    To create better and more elagant Linq queries you should use Tomas Petricek or LINQKit solution (see gourmete's post).

    Monday, December 24, 2007 11:20 AM

All replies

  • Here's a naive solution:

     

     

    var query = from p in vrlQueryResults
                            where  (userWantsStationFilter && p.Station.ToString().Contains(filter))
                               || (userWantsDurationFilter && p.Duration.ToString().Contains(filter))
                               || (userWantsDateTimeFilter && p.DateTime.ToString().Contains(filter))
                               || (userWantsFlagBitsFilter && p.FlagBits.ToString().Contains(filter))
                               || (userWantsFilePathFilter && p.FilePath.ToString().Contains(filter))
                            select new { p.DateTime, p.Station, p.Duration, p.FlagBits, p.FilePath };

    The userWants* items are all bools.

     

    I'll see if I can come up with a better solution later.

     

    Saturday, December 22, 2007 1:16 AM
  • I want it to be truly dynamic ... I don't want to even know the names of the fields at compile time (i.e. I would like to pass it in as a string during runtime).

    Saturday, December 22, 2007 1:24 AM
  • Woo, what a journey.

     

    I learned about Expressions at this link:

    http://community.bartdesmet.net/blogs/bart/archive/2007/04/06/the-iqueryable-tales-linq-to-ldap-part-2-getting-started-with-iqueryable-t.aspx

     

     

    Then I built to your specification, this code:

     

    Code Block

    public static void DynamicQuery()

    {

      List<string> myFilterTargets = new List<string>();

      myFilterTargets.Add("CustomerName");

      myFilterTargets.Add("CustomerID");

     

      string myFilter = "Bob";

     

      DataClasses1DataContext myDC = new DataClasses1DataContext();

     

      var q = myDC.Customers.Where(GetFilter(myFilterTargets, myFilter));

     

      Console.WriteLine("--Query--");

      Console.WriteLine(myDC.GetCommand(q).CommandText);

      Console.WriteLine("--Results--");

      foreach (var c in q)

      {

        Console.WriteLine(c.CustomerName);

      }

    }

     

    public static System.Linq.Expressions.Expression<Func<Customer, bool>> GetFilter(IEnumerable<string> FilterTargets, string Filter)

    {

      ParameterExpression c = Expression.Parameter(typeof(Customer), "c");

      Type[] ContainsTypes = new Type[1];

      ContainsTypes[0] = typeof(string);

      System.Reflection.MethodInfo myContainsInfo = typeof(string).GetMethod("Contains", ContainsTypes);

     

      List<Expression> myFilterExpressions =

    FilterTargets.Select<string, Expression>(s =>

      Expression.Call(

        Expression.Call(

          Expression.Property(c, typeof(Customer).GetProperty(s)),

          "ToString",

          null,

          null

        ),

        myContainsInfo,

        Expression.Constant(Filter)

      )

    ).ToList();

     

    //build a one-sided tree of Or's from this collection.

      Expression OrExpression = null;

      foreach (Expression myFilterExpression in myFilterExpressions)

      {

        if (OrExpression == null)

        {

          OrExpression = myFilterExpression;

        }

        else

        {

          OrExpression = Expression.Or(myFilterExpression, OrExpression);

        }

      }

    //lambda is where the magic happens.

      Expression<Func<Customer, bool>> predicate =

        Expression.Lambda<Func<Customer, bool>>(OrExpression, c);

     

      Console.WriteLine("--predicate--");

      Console.WriteLine(predicate.ToString());

     

      return predicate;

    }

     

     

     

    Enjoy!

     

    Saturday, December 22, 2007 3:50 AM
  • Hi aemami!


    I see two ways in general:

    1. The Microsoft way: Download the Linq samples. The "DynamicQuery" sample contains a file called Dynamic.cs which will give you the System.Linq.Dynamic Namespace. That allowes you to define dynamic querys in a string based manner, e.g.

    db.Customers.Where("City == @0 and Orders.Count >= @1", "London", 10)

    That´s easy but you don´t have typesafety and compile-time checks anylonger although that´s exactly what makes Linq such a cool feature.

    2. All other approaches I have seen are based on directly manipulating the expression tree.
    I belive Tomas Petricek worked out the most advanced and elaborated solution: Look here

    You can download the Utility Classes he mentions here.



    I am somewhat disappointed about all this dynamic query stuff. I was very looking forward to use Linq to Sql in my next project, because the samples looked really promising. But I think in a real world app it is quite useless without dynamic querys and the string approach microsoft provides with the System.Linq.Dynamic namespace reminds me of "we build our SQL query with a string builder"...

    Regards
    Christian
     
    Saturday, December 22, 2007 11:05 AM
  • i think you treat problem too complex. You have IQueryable construction with deferred execution which you can easily extend by condition and execute later.
    db.Table.Where(o=>o.col1==val1).Where(o=>o.col2==val2).Where(o=>o.col3==val3)...etc....Select(o=>new { o.id }); 

    is correctly transformed to the following sql:
    select id from Table where (col1==val1) AND (col2==val2) AND (col3==val3)



    Monday, December 24, 2007 10:54 AM
  • the only problem i can't figure out is how to define OR without inversing it with AND logic. Possibly we need to mess up with Expressions here Sad
    Monday, December 24, 2007 11:07 AM
  • Dmitry,

     

    It's easy extend IQueryable as your've shown in case with "AND" composition of filters (note that aemami needs "OR" operator).

    This is possible with "Union" method:

     

    Code Block

    var result = (from p in vrlQueryResults
    where p.Station.ToString().Contains(filter)

    select p).Union

    (from p in vrlQueryResults
    where p.Duration.ToString().Contains(filter)

    select p);

     

     

    The problem is that in case of complex queries LinqToSql and SQL Server query optimizers not always will be able to produce optimal execution plan.

     

    To create better and more elagant Linq queries you should use Tomas Petricek or LINQKit solution (see gourmete's post).

    Monday, December 24, 2007 11:20 AM
  • Thanks Curufinwe1, but UNION is completely different operation. It combines 2 different result sets, and it can't be optimized in any way to become closer to OR condition filter. I'll try to find different approaches
    Monday, December 24, 2007 2:23 PM
  •  

    David,

     

    Thank you, that looks like what I am looking for.  I have been on vacation since I posted the message and don't return until Wednesday, but I will try out that code as soon as I return.

     

    I didn't know how to do the "double reflection" of ToString().Contains(), and then how to pass in the filter parameter to the Contains function.  Actually come to think of it I didn't know how to do any of what you wrote, so, thank you.

     

     

    Monday, December 31, 2007 7:57 PM
  • Worked perfectly.  Thank you.

    Wednesday, January 2, 2008 6:28 PM
  • Linq doesn't just make queries "Type Safe", but it also allows you to cross query diferent sorts of data sources using the same query language. Dynamic query is necessary... it's basically dynamic Lambda experssion building... powerfull stuff....

    Monday, January 31, 2011 7:38 AM
  • you can use LinqTextQueryBuilder library. also there other ways to build dynamic linq queries. more detailed about this point you can read form here.
    Tuesday, October 11, 2011 4:57 AM