Introduction

I would like to share, how to use 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 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 linq 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

Dynamic Linq Library in C#

Steps
Add following class as per screen below
i)         Customer Class

Dynamic Linq Library in C#
Code
public class Customer
        {
            public int CustomerID { getset; }
            public int OrderId { getset; }
            public string CustomerName { getset; }
            public string CustomerAddress { getset; }
            public string CustomerPinCode { getset; }
            public string CustomerPhoneNumber { getset; }
            public string CustomerEmail { getset; }
            public string CustomerOffice { getset; }
            public string LocationCode { getset; }
 
            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

Dynamic Linq Library in C#

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;
              }
 
}

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


Dynamic Linq Library in C#

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

Dynamic Linq Library in C#

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 basic linq query then will use Dynamic linq library

Dynamic Linq Library in C# 


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

Dynamic Linq Library in C#  

4.  Understanding the code 

Here developer has defined columns to be display in compile time 

 We just want make it dynamic

Dynamic Linq Library in C# 


Objective here, we will pass columns to be display on grid from UI then will execute. Let’s User allow to enter required columns  


5.  Changes For dynamic Query 


As per first screen we have textbox for Select statement, We will pass text of


column to select statement of linq 


Dynamic Linq Library in C#

6.  Running application 


Dynamic Linq Library in C# 


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 snap shot 


Columns to be display has been passed to select statement 


Dynamic Linq Library in C# 


8.  Output


Following is output. 


Dynamic Linq Library in C# 


Understanding


Here we are not getting required Columns in output grid.


But our objective to display 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 following approach to get correct result 


9.  We will using Dynamic Linq library to get correct result.  


Introduction of Dynamic Linq Library
10. First we need to add Dynamic linq library from 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
Following is snapshot

Dynamic Linq Library in C# 
11. Sample data for Dynamic linq library 
Following is just snap shot of Dynamic linq library 
Dynamic Linq Library in C#     
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 display 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 

Dynamic Linq Library in C# 


16.  Using Foreach to get data from Iqueryable

 

Dynamic Linq Library in C# 


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

Dynamic Linq Library in C# 


20. Now let’s change the Columns to be display.

a
Dynamic Linq Library in C#

b.      

Dynamic Linq Library in C#

d.   

Dynamic Linq Library in C# 

  

Conclusion

 

We have learned how to use Dynamic linq library to make our Linq query dymamic

 

  Modified On Nov-30-2017 05:25:30 AM

Leave Comment