Post
Ask Question

Dynamic LINQ Query In C#

Introduction

I would like to share how to use a dynamic LINQ library in C#.

Instead of using language operators or type-safe lambda extension methods to construct your LINQ queries, the dynamic query library provides you with string-based extension methods that you can pass any string expression into.

Using a dynamic LINQ library we can do the following,

  • Select statement at runtime (pass select statement as string)
  • Where condition at runtime (pass where statement as string)

Here in this article we will first see what happens without using a dynamic LINQ library if we are passing a SELECT Statement at runtime.

Using the dynamic query library is pretty simple and easy to use and is particularly useful in scenarios where queries are completely dynamic and you want to provide an end user UI to help build them.

Final Objective

Final Objective

Procedure

1. Add the following class as per the screen below

i) Customer Class

  1. public class Customer  
  2. {  
  3.     public int CustomerID { get; set; }  
  4.     public int OrderId { get; set; }  
  5.     public string CustomerName { get; set; }  
  6.     public string CustomerAddress { get; set; }  
  7.     public string CustomerPinCode { get; set; }  
  8.     public string CustomerPhoneNumber { get; set; }  
  9.     public string CustomerEmail { get; set; }  
  10.     public string CustomerOffice { get; set; }  
  11.     public string LocationCode { get; set; }  
  12.   
  13.     public Customer(int custid, int orderid, string custname, string cusAddress,  
  14.                 string custtPin, string custPhone, string CustEmail  
  15.                 , string CustOffice, string LocCode)  
  16.     {
  17.         this.CustomerID = custid;this.OrderId = orderid;  this.CustomerName = custname;  
  18.         this.CustomerAddress = cusAddress; this.CustomerPinCode = custtPin;  
  19.         this.CustomerPhoneNumber = custPhone; this.CustomerEmail = CustEmail;  
  20.         this.CustomerOffice = CustOffice;this.LocationCode = LocCode;  
  21.   
  22.     }  
  23.   
  24. }  
ii) Order Class
  1. public class Order  
  2. {  
  3.     public int OrderId { get; set; }  
  4.     public string ProductName { get; set; }  
  5.     public string ProductCost { get; set; }  
  6.     public string ProductQunatity { get; set; }  
  7.     public Order( int orderid, string pName, string pCost, string Pquant)  
  8.     {
  9.         this.OrderId = orderid; this.ProductCost = pCost;  
  10.         this.ProductQunatity = Pquant; this.ProductName = pName;  
  11.     }
  12. }  
2. Add data to the Customer and Order List

i) Defining Lists. We have defined the global list as in the following:
Clipboard38
ii) Adding data to lists. Add Code to Form load
  1. private void Form2_Load(object sender, EventArgs e)  
  2. {  
  3.   
  4.     liCust = new List<Customer>();  
  5.   
  6.     Customer oCust = new Customer(001, 123000, "Devesh", "Ghaziabad",   
  7.     "250301", "9891586890", "[email protected]", "Genpact", "3123000");  
  8.     liCust.Add(oCust);  
  9.     oCust = new Customer(002, 123001, "NIKHIL", "NOIDA", "250201",   
  10.     "xxx9892224", "[email protected]", "X-vainat", "4123001");  
  11.     liCust.Add(oCust);  
  12.     oCust = new Customer(003, 123002, "Shruti", "NOIDA", "25001",   
  13.     "xxx0002345", "[email protected]", "Genpact", "5123002");  
  14.     liCust.Add(oCust);  
  15.     oCust = new Customer(004, 123003, "RAJ", "DELHI", "2500133",   
  16.     "xxx9898907", "[email protected]", "HCL", "6123003");  
  17.     liCust.Add(oCust);  
  18.     oCust = new Customer(005, 123004, "Shubham", "Patna", "250013",  
  19.     "x222333xx3", "[email protected]", "Genpact", "6123004");  
  20.     liCust.Add(oCust);  
  21.   
  22.   
  23.     //order data  
  24.     liOrder = new List<Order>();  
  25.     Order oOrder = new Order(123000, "Noika Lumia", "7000", "2");  
  26.     liOrder.Add(oOrder);  
  27.     oOrder = new Order(123001, "Moto G", "17000", "1");  
  28.     liOrder.Add(oOrder);  
  29.     oOrder = new Order(123002, "Intext Mobile", "7000", "1");  
  30.     liOrder.Add(oOrder);  
  31.     oOrder = new Order(123001, "Celkom GX898", "2500", "1");  
  32.     liOrder.Add(oOrder);  
  33.     oOrder = new Order(123001, "Micromax", "1000", "10");  
  34.     liOrder.Add(oOrder);  
  35.     oOrder = new Order(222, "NOIKA Asha", "2500", "1");  
  36.     liOrder.Add(oOrder);  
  37.     oOrder = new Order(22212, "Iphone", "1000", "10");  
  38.     liOrder.Add(oOrder);  
  39.   
  40.     DrgCustomer.DataSource = liCust;  
  41.     drgOrder.DataSource = liOrder;  
  42.   
  43. }  
3. Joining Data from two lists

First we are trying to run a basic LINQ query then will use a dynamic LINQ library.
  1. var result = from T1 in liCust  
  2.                          join T2 in liOrder  
  3.                          on T1.OrderId equals T2.OrderId  
  4.                          select new {T1.OrderId,T1.CustomerName,T1.CustomerID,  
  5.                   T2.ProductCost,T2.ProductName};  
Result
 
result 

4. Understanding the code

Here the developer has defined columns to be displayed at compile time.

We just want make it dynamic.
Understanding the code
The objective here is to pass columns to be displayed on the grid from the UI then execute it. That lets the user enter the required columns.

5. Changes For dynamic Query

As per the first screen we have a TextBox for the Select statement. We will pass the text of the column to a select statement of LINQ.
 
Changes For dynamic Query
6. Running application

Running application

Text at Select Statement TextBox

T1.OrderId,T1.CustomerName,T1.CustomerID,T2.ProductCost,T2.ProductName

Here we have considered T1 as the Customer table and T2 and Order Table.

7. Clicked on Run button and Following is debug snap shot

The columns to be displayed have been passed to the select statement.

Clicked on Run button and Following is debug snap shot

8. Output

The following is output.

Output

Understanding

Here we are not getting the required columns in the output grid.

But our objective to display the following columns at the output grid.

T1.OrderId,T1.CustomerName,T1.CustomerID,T2.ProductCost,T2.ProductName

Here T1 = Customer Table

T2= Order Table

We will use the following approach to get the correct result.

9. We will using Dynamic LINQ library to get the correct result.
 
Introduction of Dynamic LINQ Library

10. First we need to add a Dynamic LINQ library from the following link.

http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library

For now I have attached a sample project where you will find this library.

The following is a snapshot. 

Introduction of Dynamic Linq 

11. Sample data for Dynamic LINQ library

The following is just a snapshot of a Dynamic LINQ library.

Sample data for Dynamic linq library

12. Add following namespace to working class

using System.Linq.Dynamic;
 
13. Make the following changes in the LINQ query
  1. var result = (from T1 in liCust  
  2.                           join T2 in liOrder  
  3.                             on T1.OrderId equals T2.OrderId  
  4.                             select new { T1,T2}).AsQueryable();  
  5.   
  6. we have make this query AsQueryable  
We have made this query AsQueryable.

14. Select columns to be display at runtime

Now we are passing the columns name from the UI via a text box named textSelectStatement.
  1. string selectStatement= "new ( " + textSelectStatement.Text +")";  
  2.   
  3.     IQueryable iq = result.Select(selectStatement);  
15. The following is a snapshot of the complete code.
 


16. Using foreach to get data from Iqueryable.
Using Foreach to get data from Iqueryable

17. In the attached Zip I have added the code to parse the above data to DataGridView columns and rows.

18. Complete Code
  1. private void btnRun_Click(object sender, EventArgs e)  
  2. {     
  3.     // code from devesh omar
  4.     dataGridView1.Rows.Clear();  
  5.     dataGridView1.Columns.Clear();  
  6.     var result = (from T1 in liCust  
  7.     join T2 in liOrder  
  8.     on T1.OrderId equals T2.OrderId  
  9.     select new { T1,T2}).AsQueryable();  
  10.     string selectStatement= "new ( " + textSelectStatement.Text +")";  
  11.     IQueryable iq = result.Select(selectStatement);  
  12.   
  13.     int i = 0;  
  14.     foreach ( var data in iq)  
  15.     {  
  16.         List<object> li = new List<object>();  
  17.         if (i == 0)  
  18.         {  
  19.             string[] str = data.ToString().Replace("{", "").Replace("}", "").Split(',');  
  20.   
  21.             foreach (string col in str)  
  22.             {  
  23.             // adding columns to grid  
  24.                 string colname = col.Substring(0, col.IndexOf("="));  
  25.                 string dataValue = col.Substring(col.IndexOf("=") + 1);  
  26.                 li.Add(dataValue);  
  27.                 dataGridView1.Columns.Add(colname, colname);  
  28.             }  
  29.         }  
  30.         else  
  31.         {  
  32.             string[] str = data.ToString().Replace("{", "").Replace("}", "").Split(',');  
  33.             foreach (string col in str)  
  34.             {  
  35.                 string colname = col.Substring(0, col.IndexOf("="));  
  36.                 string dataValue = col.Substring(col.IndexOf("=") + 1);  
  37.                 li.Add(dataValue);  
  38.   
  39.             }  
  40.         }  
  41.         dataGridView1.Rows.Add(li.ToArray());  
  42.         i++;  
  43.     }  
  44. }  
19. Output and running the code
 
OutPut and running the Code

20. Now let's change the columns to be displayed
 
 
 Now change the Columns to be display 1
 
Now change the Columns to be display 2

Conclusion
 
We have learned how to use a Dynamic LINQ library to make our LINQ query dymamic.

Refrences

http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library


Similar Articles