I would like to share, how to use the 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 Dynamic Linq library we can do the following

1. Select statement at runtime  (Pass select statement as string)
2. Where condition at runtime (Pass where statement as string)

Here in this article, we will first see what happen without using dynamic link library if we are passing 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 end-user UI to help build them.

Final Objective

Steps

Add following class as per the screen below

i)         Customer Class

Code

public class Customer
        {
            public int CustomerID { get; set; }
            public int OrderId { get; set; }
            public string CustomerName { get; set; }
            public string CustomerAddress { get; set; }
            public string CustomerPinCode { get; set; }
            public string CustomerPhoneNumber { get; set; }
            public string CustomerEmail { get; set; }
            public string CustomerOffice { get; set; }
            public string LocationCode { get; set; }
 
            public Customer(int custid, int orderid, string custname, string cusAddress,
                string custtPin, string custPhone, string CustEmail
                , string CustOffice, string LocCode)
            {
 
this.CustomerID = custid;this.OrderId = orderid;  this.CustomerName = custname;
                this.CustomerAddress = cusAddress; this.CustomerPinCode = custtPin;
                this.CustomerPhoneNumber = custPhone; this.CustomerEmail = CustEmail;
                this.CustomerOffice = CustOffice;this.LocationCode = LocCode;
 
            }
 
        }

ii)    Order Class

Code

public class Order
              {
                public int OrderId { get; set; }
                public string ProductName { get; set; }
                public string ProductCost { get; set; }
                public string ProductQunatity { get; set; }
              public Order( int orderid, string pName, string pCost, string Pquant)
              {
                       this.OrderId = orderid; this.ProductCost = pCost;
                      this.ProductQunatity = Pquant; this.ProductName = pName;
              }
 
}

2. Adding data to Customer and Order List
   
   
3. i)   Defining Lists. We have defined the global list

ii)   Adding data to lists. Add Code to Form load

Code

 
       private void Form2_Load(object sender, EventArgs e)
       {
 
       liCust = new List<Customer>();
 
       Customer oCust = new Customer(001, 123000, "Devesh", "Ghaziabad",
       "250301", "9891586890", "devesh.akgec@gmail.com", "Genpact", "3123000");
       liCust.Add(oCust);
       oCust = new Customer(002, 123001, "NIKHIL", "NOIDA", "250201",
       "xxx9892224", "devesh.akgec@gmail.com", "X-vainat", "4123001");
       liCust.Add(oCust);
       oCust = new Customer(003, 123002, "Shruti", "NOIDA", "25001",
       "xxx0002345", "devesh.akgec@gmail.com", "Genpact", "5123002");
       liCust.Add(oCust);
       oCust = new Customer(004, 123003, "RAJ", "DELHI", "2500133",
       "xxx9898907", "devesh.akgec@gmail.com", "HCL", "6123003");
       liCust.Add(oCust);
       oCust = new Customer(005, 123004, "Shubham", "Patna", "250013",
       "x222333xx3", "devesh.akgec@gmail.com", "Genpact", "6123004");
       liCust.Add(oCust);
 
 
       //order data
       liOrder = new List<Order>();
       Order oOrder = new Order(123000, "Noika Lumia", "7000", "2");
       liOrder.Add(oOrder);
       oOrder = new Order(123001, "Moto G", "17000", "1");
       liOrder.Add(oOrder);
       oOrder = new Order(123002, "Intext Mobile", "7000", "1");
       liOrder.Add(oOrder);
       oOrder = new Order(123001, "Celkom GX898", "2500", "1");
       liOrder.Add(oOrder);
       oOrder = new Order(123001, "Micromax", "1000", "10");
       liOrder.Add(oOrder);
       oOrder = new Order(222, "NOIKA Asha", "2500", "1");
       liOrder.Add(oOrder);
       oOrder = new Order(22212, "Iphone", "1000", "10");
       liOrder.Add(oOrder);
 
       DrgCustomer.DataSource = liCust;
       drgOrder.DataSource = liOrder;
 
       }

 

3.  Joining Data from two list

First, we are trying to run a basic LINQ query then will use Dynamic Linq library

 

Code

var result = from T1 in liCust
                         join T2 in liOrder
                         on T1.OrderId equals T2.OrderId
                         select new {T1.OrderId,T1.CustomerName,T1.CustomerID,
                  T2.ProductCost,T2.ProductName};

Result

  

4.  Understanding the code 

Here the developer has defined columns to be displayed in compile time 

 We just want to make it dynamic

 

The objective here, we will pass columns to be displayed on a grid from UI then will execute. Let’s User allow to enter required columns  

5.  Changes For dynamic Query 

As per the first screen, we have a textbox for Select statement, We will pass the text of column to select statement of LINQ.

6.  Running application 

 

Text at Select Statement Textbox

T1.OrderId, T1.CustomerName, T1.CustomerID, T2.ProductCost, T2.ProductName Here we have considered T1 as Customer table and T2 and Order Table 

7.  Clicked on Run button and Following is debug snapshot Columns to be displayed has been passed to select statement 

 

8.  Output

The following is output. 

 

Understanding

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

But our objective to display the following columns at 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 use the Dynamic Linq library to get the correct result.  

Introduction of Dynamic Linq Library

10. First, we need to add Dynamic Linq library from the following a 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

Following is snapshot

 

11. Sample data for Dynamic Linq library 

Following is just a snapshot of Dynamic Linq library 

     

12. Add following namespace to working-class 

using System.Linq.Dynamic; 

13. Do following changes in LINQ query

 

            var result = (from T1 in liCust
                          join T2 in liOrder
                            on T1.OrderId equals T2.OrderId
                            select new { T1,T2}).AsQueryable();
 
we have make this query AsQueryable

14. Select columns to be displayed at runtime 

Now we are passing Columns name from UI via text box named as textSelectStatement

 string selectStatement= "new ( " + textSelectStatement.Text +")";
 
   IQueryable iq = result.Select(selectStatement);  

15. Following is snap of  complete code 

 

16.  Using Foreach to get data from Iqueryable

 

 

17. In attached zip, I have added the code to parse above data to datagridview

columns and rows 

18. Complete Code 

              private void btnRun_Click(object sender, EventArgs e)
              {
                     // code from devesh omar
                     dataGridView1.Rows.Clear();
                     dataGridView1.Columns.Clear();
                     var result = (from T1 in liCust
                     join T2 in liOrder
                     on T1.OrderId equals T2.OrderId
                     select new { T1,T2}).AsQueryable();
                     string selectStatement= "new ( " + textSelectStatement.Text +")";
                     IQueryable iq = result.Select(selectStatement);
 
                     int i = 0;
                     foreach ( var data in iq)
                     {
                           List<object> li = new List<object>();
                           if (i == 0)
                           {
string[] str = data.ToString().Replace("{", "").Replace("}", "").Split(',');
 
                     foreach (string col in str)
                     {
                     // adding columns to grid
                           string colname = col.Substring(0, col.IndexOf("="));
                           string dataValue = col.Substring(col.IndexOf("=") + 1);
                           li.Add(dataValue);
                           dataGridView1.Columns.Add(colname, colname);
                     }
                     }
                     else
                     {
string[] str = data.ToString().Replace("{", "").Replace("}", "").Split(',');
                     foreach (string col in str)
                     {
                           string colname = col.Substring(0, col.IndexOf("="));
                           string dataValue = col.Substring(col.IndexOf("=") + 1);
                           li.Add(dataValue);
 
                     }
                     }
                     dataGridView1.Rows.Add(li.ToArray());
                     i++;
              }
              }

 19. OutPut and Running the Code

 

20. Now let’s change the Columns to be displayed.
(a).

b.      

d.   

 

  Conclusion 

We have learned how to use the Dynamic Linq library to make our Linq query dynamic.