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



Procedure

1. Add the following class as per the screen below

i) Customer Class

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

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. Add data to the Customer and Order List

i) Defining Lists. We have defined the global list as in the following:

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

private void Form2_Load(object sender, EventArgs e)
{

    liCust = new List<Customer>();

    Customer oCust = new Customer(001, 123000, "Devesh", "Ghaziabad",
    "250301", "9891586890", "[email protected]", "Genpact", "3123000");
    liCust.Add(oCust);
    oCust = new Customer(002, 123001, "NIKHIL", "NOIDA", "250201",
    "xxx9892224", "[email protected]", "X-vainat", "4123001");
    liCust.Add(oCust);
    oCust = new Customer(003, 123002, "Shruti", "NOIDA", "25001",
    "xxx0002345", "[email protected]", "Genpact", "5123002");
    liCust.Add(oCust);
    oCust = new Customer(004, 123003, "RAJ", "DELHI", "2500133",
    "xxx9898907", "[email protected]", "HCL", "6123003");
    liCust.Add(oCust);
    oCust = new Customer(005, 123004, "Shubham", "Patna", "250013",
    "x222333xx3", "[email protected]", "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 lists

First we are trying to run a basic LINQ query then will use a dynamic LINQ library.

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 at compile time.

We just want make it dynamic.

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.

 

6. 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.



8. Output

The following is 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. 

 

11. Sample data for Dynamic LINQ library

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

12. Add following namespace to working class

using System.Linq.Dynamic;

 

13. Make the following changes in the 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

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.

string selectStatement= "new ( " + textSelectStatement.Text +")";

    IQueryable iq = result.Select(selectStatement);

15. The following is a snapshot of the complete code.

 

16. 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

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

 

 

 

 

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