none
C# Linq with dynamic Where RRS feed

  • Question

  •  

    I have read a number of articles and have yet to figure out how to do this (including the Dynamic Linq at Weblogs by ScottGu and the MSDN page on Linq). I am working in C#.

    I am using a Linq statement (below) to populate an EnumberableRowCollection which is copied into a DataView. I need the ability to replace the Where/Select statements with a variable, as these will be created based on user selections. I also have not found how to limit the columns selected. I keep receiving suggestions to hide the columns, but I need the columns to not exist at all in the final DataView.

     

    //My current code:
    EnumerableRowCollection<DataRow> MyQuery = from list in MyTable.AsEnumerable() where list.Field<string>("Category") == "I" select list;
    
    //What I would like (not sure how to do this)
    EnumerableRowCollection<DataRow> MyQuery = from list in MyTable.AsEnumerable() where <strong>WhereFieldString</strong> select <strong>SelectFieldListString</strong>
    


     

    To be clear, my questions are:

    • How do I control/limit the columns selected into MyQuery?
    • Is it possible to replace the Where/Select constraints with a string (thus allowing the constraints based on user selection)?
    • If this is possible how would I do so?

     

    • Moved by Aspen VJ Monday, April 4, 2011 7:13 AM (From:Visual C# General)
    Thursday, March 31, 2011 3:08 PM

Answers

  • Ah the "dynamic select".
    Since the OP stated, he wants both come IEnumerable<DataRow> (not some anonymous type)
    but only a subset of the original coumns,
    I think good ole ADO.NET DataView.ToTable() and
    DataTable.Select will do the job just fine.

    LINQ is actually second choice and cumbersome for this very task.
    And no need to swerve to bloated 3rd party libs, to accomplish some
    cheap job.

    IEnumerable<DataRow> DynamicQuery(string field, string value, params string[] columnNames)
    { 
       //TODO do null checks + validations
      return MyTable.DefaultView.ToTable(false, columnNames)
      .Select(field + "= '" + value + "'"); //for string types
    }
    
    



    Christoph


    Saturday, April 2, 2011 8:16 PM

All replies

  • Instead of using query syntax, you can use the Where and Select extension method directly:

    MyTable.AsEnumerable().Where(list => SatisfySomeCustomCondition(list)).Select(list => ReturnSomethingBasedOnList(list));

    The parameters to these methods are Func's.  In the case of Where, it's a Func<T,bool>.  A Select is a Func<T,TResult>.  You can externalize these as variables:

    Func<T,bool> customWhereClause = list => SatsifySomeCustomCondition(list);

    MyTable.AsEnumerable().Where(customWhereClause).

    Same with Select, naturally.

    You would use Select to limit the columns in the row.  You'd do something like:

    Func<T, DataRow> selectFunction = t => new DataRow(...)

    where the parameters passed to the new DataRow are only the columns (derived from the instance of T) that you want to return.  If you cant' do it in one line, you can write functions in blocks, but then you need to use the return syntax:

    t => { var row = new DataRow(); row.Blah = t.Blah; return row; }

    I think that's what you want, right?

    Oh, but as far as strings: not easily.  You would have to write a parser and convert the string input into a Func. 

    Evan


    Thursday, March 31, 2011 3:26 PM
  • Hello cyberSurfer,

    You should explore Linq dynamic query library : here

    Kind regards,

    Thursday, March 31, 2011 4:52 PM
  • Evan

    I'm afraid I am unsure what 'list' and 'T' are in your example. If you don't mind would you explain further so that I may gain a better understanding.

    I am thinking my code would like something like what follows. Its not complete, or fully thought out as I'm still unclear on using this method. Please let me know what I may have incorrect, or am missing.

     

    Func<DataView, bool> MyWhereClause = ?MyTable? => "Category == I"(?MyTable?);

    Func<DataView, DataRow> MySelectClause = DataView => new DataRow("Project, Category, Risk, OtherColumnName");

    DataView => {var row = new DataRow(); ....?

     

    Is this moving in the proper direction or am I missing it? Thanks for helping me

    Thursday, March 31, 2011 5:19 PM
  • So I have managed to get this far. However, I'm getting an error on 3rd line about invalid parameters in my overload method. I believe my WhereClause is incorrect. I would appreciate if you could provide some guidance on the following:

    • What is list and myT?
    • What would be the proper format for my Lambda expression?
    • Would DataRowCollection and DataTable be the proper input types for Func?
    • When I attempt to write new DataRow(Column Names), it will not accept any field names? 

    Func<DataRowCollection, Boolean> MyWhereClause = list => ("Category" == "I");
    Func<DataTable, DataRow> MySelectClause = myt => new DataRow(); 
          
    MyTable2.AsEnumerable().Where(MyWhereClause).Select(MySelectClause);
    

    I would appreciate any help you can provide, as this has been the closet I have come to a resolution to this problem. 

     

    Thanks

    Friday, April 1, 2011 6:04 PM
  • Welcome to the world of Lamba expressions.

    When you define a function (a Func<...>), you can think of it like a function pointer (if you are familiar with C++), or conceptually you can think of it like storing a method as a variable.

    There are two basic "method variable" types that people use: Action<T1,T2...Tn> and Func<T1,T2...Tn>.

    Actions represent methods with no return values.  Void methods.  The type parameters are the types of the input paramters to the action.

    So, for example:

    void MyMethod(string s, int i) { ... }
    void MyOtherMethod(string s, int i) { ... }
    
    Action<string,int> actionVariable = MyMethod;
    actionVariable("Hello", 5); // invokes MyMethod
    actionVariable = MyOtherMethod;
    actionVariable("World", 10); // invokes MyOtherMethod
    
    

    Func<> works the same basic way, except functions are used for methods that return a value.  When you use Func<T1,T2,..Tn>, the first n-1 type paramters are the types of the parameters and Tn is the return value, so:

    int MyFunction(string s) { return 5; }
    int MyOtherFunction(string s) { return 10; }
    
    Func<string,int> func = MyFunction;
    var x = func("hello"); // x is 5
    func = MyOtherFunction;
    x = func("world"); // x is 10
    

    The nice thing about lambda expressions is that they allow you to define functions and actions anonymously.  To define an anonymous action, you do it like this:

    Action<string> myAction = delegate(string s) { Console.WriteLine(s); };
    

    To define an anonymous function, you do it like this:

    Func<string, int> myFunc = s => 5;
    

    Now the syntax is a little disarming for newcomers, but basically, the way it works is the symbols on the left of the => operator are the input symbols, and the expression on the right side of the => operator should be thought of like the right side of a "return" statement.  The intention is that you are supposed to use the symbols on the left side as input to the expression on the right, so this would be a more appropriate example:

    Func<int,int,int> multiply = (x,y) => x * y;
    var x = multiply(5,10); // x is 50
    

    The really nice thing about functions is that when they are used as parameters to methods, like the Select method, the return type of the generic Select method is inferred based on what you actually return in your func!  So, when you do something like this:

    IEnumerable<int> ints = new int[] { 1,2,3 };
    var floats = ints.Select(i => (float)i); // type of floats is inferred to be IEnumerable<float> because my lambda returns a float
    
    

    This also explains why the var keyword is necessary in C#.  Type inference is useless if you still have to declare a type on the left side of an assignment operator.

    So, the way you are supposed to use Where is:

    You have an IEnumerable<TInput>.  You want a subset of this enumeration where all of the items meet some condition.  The Where method takes a Func<TInput,bool> as a parameter.  A simple example to return even integers would be:

    var ints = new int[] { 1,2,3,4,5,6 };
    var evenInts = ints.Where(i => (i % 2) == 0);
    

    The left side of the => defines the symbol i, which, when this method is executed, will represent each integer in the ints collection, one after the next.  The right side evaluates to a boolean - true or false, depending on whether it is even.  Only items which, when evaluated by the function that you provide are true, will be included in evenInts.

    Select is similiar, except select doesn't take a Func<TIn, bool>, it takes a Func<TIn,TOut>.  TOut will be automatically inferred based on whatever type you return in your lambda.  So, let's say I wanted to square some integers but I was concerned about overlow, so I want to return longs, I'd do this:

    var ints = new int[] { 1,2,3,4,5 };
    var squares = ints.Select(i => (long)i * (long)i);
    
    

    Because the lambda I used in my Select returns a long, the type of squares is now IEnumerable<long>.

    Is that clearer?  If so, please mark as answer.

    Evan

    Friday, April 1, 2011 7:17 PM
  • I think I am beginning to understand. However, I am still unable to get my select to work. If I may I will explain what I have done below and the reason. If you would please let me know if my thinking is correct?

    Func<DataRow, Boolean> MyWhereClause = list => list.Field<string>("Category") == "I";
    

    Use DataRow- because result of Where Clause should be datarow. Why would it not be DataRowCollection?

    List is a temporary name that ultimately represents my DataTable. Therefore my where would be list(aka DataTable).Field(FieldName) == Value. Right?

     

    I am still having issues with my select clause.

    Func<DataTable, DataRow> MySelectClause = list => new DataRow("Category");
    

    I believe the TIn would be DataTable. However, I get an error on the new DataRow and my actual function call:

    MyTable2.AsEnumerable().Where(MyWhereClause).Select(MySelectClause);
    

     I believe my error on the final call is due to an error in my SelectClause. I am still a bit unclear on using the select. I designed this select based on my understanding and your first post. I believe I would want my select to either return a DataRow (as a template of what to select) or as a DataTable (with named columns selected).

     

    Is my understand thus far correct? Also, can you point out my error on the Select clause (an explaination would be appreciated as well).

     

    Thanks again Evan

    Friday, April 1, 2011 8:20 PM
  •   MyTable.AsEnumerable().AsQueryable()
        .Where("Category == @0", "I") // WhereFieldString
        .Select("New(Category as Cat)"); // SelectFieldListString
        
    

    Would you like something like this ?

    Use the Linq Dynamic Query Library (download includes examples).

    Check out ScottGu's blog for more examples.

    Kind regards,

     


    Friday, April 1, 2011 8:47 PM
  • Link. I do not believe that will work because I have no ability to dynamically build those statements. I am unable to determine the Field names and values ahead of time because they are selected by the user. That is why Evan suggested using the Func. It appears that I should be able to dynamically build the where clause using that method.
    Friday, April 1, 2011 8:58 PM
  • Link. I do not believe that will work because I have no ability to dynamically build those statements. I am unable to determine the Field names and values ahead of time because they are selected by the user. That is why Evan suggested using the Func. It appears that I should be able to dynamically build the where clause using that method.


    If the field names and values are selected by the user then where is the problem ?

    You got everything you need to build your dynamic condition and the fields to retrieve.

    Here is a sample I made for building dynamic contidion and fields :

     

     

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Linq.Dynamic;
    using System.Text;
    using System.Data;
    
    namespace DynamicQuery
    {
      public class Product
      {
        public int Id { get; set; }
        public int Price { get; set; }
        public string Category { get; set; }
        public string Name { get; set; }
      }
    
      class Program
      {
        static string GetDynamicWhereByCategory()
        {
          return "Category == @0";
        }
    
        static string GetDynamicSelectByCategory(params string[] fields)
        {
          return fields.
            Aggregate("New(", (s, field) => s += string.Concat(field, fields.Last() == field ? ")" : ","));
        }
    
        static void Main(string[] args)
        {
          List<Product> products = new List<Product>
          {
            new Product { Id = 1, Price = 10, Category = "A", Name = "Product1"},
            new Product { Id = 2, Price = 10, Category = "A", Name = "Product2"},
            new Product { Id = 3, Price = 10, Category = "B", Name = "Product3"},
            new Product { Id = 4, Price = 10, Category = "B", Name = "Product4"},
            new Product { Id = 5, Price = 10, Category = "C", Name = "Product5"},
            new Product { Id = 6, Price = 10, Category = "C", Name = "Product6"}
          };
    
          // Dynamic Where (Category B)
          string dynamicWhere = Program.GetDynamicWhereByCategory();
          string dynamicSelect = Program.GetDynamicSelectByCategory("Id", "Price", "Name");
    
          var dynamicQuery = products.AsQueryable<Product>().
            Where(dynamicWhere, "B").
            Select(dynamicSelect);
    
          foreach (var item in dynamicQuery)
          {
            Console.WriteLine(item.ToString());
          }
    
          Console.ReadKey();
        }
      }
    }
    
    

     

    For more details about item, please refer to DynamicClass. You can also add extension methods to DynamicClass.

    System.Linq.Dynamic is available on http://msdn.microsoft.com/en-us/vcsharp/bb894665.aspx

    Kind regards,

     

     

    Saturday, April 2, 2011 12:20 PM
  • Use DataRow- because result of Where Clause should be datarow. Why would it not be DataRowCollection?


    because the delegate expects a single element of the IEnumerable<T>, not the enumerable itself.
    T is a DataRow here.

    Func<DataTable, DataRow> MySelectClause = list => new DataRow("Category");

     

    I believe the TIn would be DataTable. However, I get an error on the new DataRow and my actual function call:

    the constructor of DataRow is protected and doesn't take a string, so it won't work.
    Also you don't need to create a new row, but just want to pick one that already exists.

    Given you query is against columns of type string only, the following code is all you need afaics:

    var cathRows = DynamicQuery ("Cathegory", "I");
    var prodRows = DynamicQuery ("Product", "Car");
    
    EnumerableRowCollection<DataRow> DynamicQuery(string field, string value)
    {
      return MyTable.AsEnumerable()
       .Where(row => row.Field<string>(field) == value)
       .Select(row => row);
    }
    


    Saturday, April 2, 2011 2:24 PM
  • Hello Christoph,

     

    Where is the dynamic select ?

     

    Kind regards,

    Saturday, April 2, 2011 7:33 PM
  • Am 02.04.2011 21:33, schrieb Link.fr:

    Where is the dynamic select ?

    The what?

    Saturday, April 2, 2011 7:52 PM
  • Ah the "dynamic select".
    Since the OP stated, he wants both come IEnumerable<DataRow> (not some anonymous type)
    but only a subset of the original coumns,
    I think good ole ADO.NET DataView.ToTable() and
    DataTable.Select will do the job just fine.

    LINQ is actually second choice and cumbersome for this very task.
    And no need to swerve to bloated 3rd party libs, to accomplish some
    cheap job.

    IEnumerable<DataRow> DynamicQuery(string field, string value, params string[] columnNames)
    { 
       //TODO do null checks + validations
      return MyTable.DefaultView.ToTable(false, columnNames)
      .Select(field + "= '" + value + "'"); //for string types
    }
    
    



    Christoph


    Saturday, April 2, 2011 8:16 PM
  • I know this is not what you asked for, but since you're using only DataTables in your query, you can perform the same operation using DataTable's SELECT method.

    List<DataRow> MyQuery = (from list in MyTable.Select("Category== 'I'") select list).ToList<DataRow>();
    
    
    List<DataRow> MyQuery = (from list in MyTable.Select("Category== 'I' AND Name like 'Test%' ") select list).ToList<DataRow>();
    

     

     

     

     

     

    If you decide to use other objects different than DataTable, you may create a kind of expression evaluation class to test properties values using reflection.

     

    There is another alternative also, that I've used previously in a project: You can use MSCSharp namespace to compile and build an memory assembly at runtime, creating a class in C# (at runtime) that will deal any kind of expression as you want.

    I can share this project with you, if you wish. It's quite simple.

     

    Good Luck


    ...
    Sunday, April 3, 2011 7:31 PM
  • Hi CyberSurfer,

    Based on your description, I think you issue is more related to LINQ. So I will move this thread from Visual C# General forum to LINQ to SQL forum. Thanks.


    Vin Jin [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, April 4, 2011 7:13 AM
  • @Christoph

    Thanks for the help. I feel like I continue to get to the edge of solving this problem, but can't make the final connection. What datatype would I assign the return value to? I attempted both DataTable and DataView, but neither works. Also, just as an attempt I tried List<DataRow> as well. 

    I think your solution would also work well as I can replace the Field/Value with a string array of Where clauses as well. As I said though I seem to be stuck on the return assignment.

    Monday, April 4, 2011 2:03 PM
  • @Link

    Thanks for your response (and letting others know I need Dynamic).

    I tried to use a modification of your code, but was unable to have any success. The biggest difference for me is that I am not using a public class or list. All the information is coming form a database. However, I thought I should still be able to use a modified version of your dynamicQuery.

    var MyQuery1 = MyTable.AsEnumerable().Where("Category == I").Select(new("Project", "Category"));
    

    However, I receive an "invalid argument" error regarding the where clause. From what I can tell, this is how your where clause would be returned as well. Am I wrong? also I had to use MyTable.AsEnumerable rather then List.AsQuerable.

    I should also note, that I will not know what fields are being used ahead of time. I am populating a list box with the field names. The user selects a field, then an operator (another combobox), and finally inserts value into a textbox. That is why I can not even code the field names.

    Monday, April 4, 2011 2:27 PM
  • @Christiano

    This had been suggested in the past, but allows no ability to program dynamically. I also was unable to determine how to select specific columns using that method. However, if you can provide a example showing how I can dynamically control the Where and Select potions I would certainly take another look.

    Monday, April 4, 2011 2:31 PM
  • @Link

    var MyQuery1 = MyTable.AsEnumerable().Where("Category == I").Select(new("Project", "Category"));
    

     

    However, I receive an "invalid argument" error regarding the where clause. From what I can tell, this is how your where clause would be returned as well. Am I wrong? also I had to use MyTable.AsEnumerable rather then List.AsQuerable.

    I should also note, that I will not know what fields are being used ahead of time. I am populating a list box with the field names. The user selects a field, then an operator (another combobox), and finally inserts value into a textbox. That is why I can not even code the field names.

    Hi CyberSurfer,

    Your query is not valid, you should write it like that (Previous posts) :

    Where("Category == @0", "I")

    The dynamic where close take a string for conditions whith @0, @1 ... @n for values and a params string[] values.

    You should also need AsQuerable.

    I think that you can easily manage your server code side without writing any dynamic query If you refer to Christoph posts.

    Kind regards,

     

    Monday, April 4, 2011 2:50 PM
  • I am attempting that solution, but have been unable to determine how to assign the IEnumerable<DataRow> to my DataView or a DataTable.

    Does anyone know how to properly assign this?

    Monday, April 4, 2011 6:28 PM
  • Ones you got your IEnumerable<DataRow>, you can bind it to your dataTable like that :

          oDataTable.Clear();
    
          // add your columns
          oDataTable.Columns.Add("Category", typeof(string));
          // ...
    
          // add your rows
          foreach (var row in dynamicQuery)
          {
            oDataTable.Rows.Add(row);
          }
          
          // Finish your binding
          oDataTable.AcceptChanges();
    
    Kind regards,

    Monday, April 4, 2011 7:07 PM
  • Thanks.

    I actually used the .CopytoDataTable<DataRow>() function and it seems to work now. I am attempting to convert my code to allow multiple Where Clauses. If I can get that I will mark this thread as answered. Hopefully I can work on this tonight and tomorrow morning. I'll let you know how that part goes...

    Monday, April 4, 2011 7:15 PM
  • Hi CyberSurfer!

    In your first post you said: "I am using a Linq statement (below) to populate an EnumerableRowCollection which is copied into a DataView".
    If you have the code to copy from an EnumerableRowCollection
    <DataRow> into a DataView, you could probably use almost the same code to
    copy from an array of DataRow or a List<DataRow> or any IEnumerable<DataRow> into a DataView.
    Or did I miss something? (Actually never used EnumerableRowCollection<DataRow>, but from the specs it seems just a very
    thin wrapper around
    IEnumerable<DataRow>.
    Maybe you could just show the initial code that fills the DataView?

    Chris

    Monday, April 4, 2011 7:18 PM
  • Okay. Here is what I did below and it seems to work. (Feel free to point out any problems you may see).

    IEnumerable<DataRow> MyDynQuery = BudgetGlobal.GetDynamicQuery(FullTable, MyWhere, MyColumnList);
    FilteredTable = new DataTable();
    foreach (DataRow MyRow in MyDynQuery)
    {
       FilteredTable.ImportRow(MyRow); //Used importRow rather then .Add because row already exist in FullTable
    }
    
    public static IEnumerable<DataRow> GetDynamicQuery(DataTable MyTable, string MyWhere, params string[] MyColumns)
    {      
       try
       {   return MyTable.DefaultView.ToTable(false, MyColumns).Select(MyWhere);   }
       catch(Exception ex)
       { //Call Error Logger}
    }
    

    Initially I was using the .CopyToTable() funcation rather then the foreach loop, but I'm using the .Net 2.0 enviroment in my actual application and that is not available.

     

    If there aren't any other thoughts I'm gonna mark this as answered. Thanks again for all your help.

     

    Tuesday, April 5, 2011 4:57 PM