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.

Clicked on Run button and Following is debug snap shot

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.

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

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

23 Comments

Join the conversation! Your thoughts help the community grow.

Kevin Burton5y ago
Thank you this is just what I needed. But I have one question. I have a .NET 5 project and it seems that DefineDynamicAssembly has been removed. Is there a workaround? This code compiles fine with the exception for the one error with DefineDynamicAssembly.
Dinesh Gabhane6y ago
Nice Article. Thanks
Gajjala Mallikarjun Reddy9y ago
This library will work for windows 8.1 store apps development.
Yury Yatskov9y ago
A very good example!How to create a query string with group in Dynamic Linq? Group can contain any fields. var queryShipments = from shipmentItem in shipmentsData group shipmentItem by new { City = shipmentItem.City, Country = shipmentItem.Country } into shipmentGroup select new { City = shipmentGroup.Key.City, Country = shipmentGroup.Key.Country, Quantity = shipmentGroup.Sum(q => q.Quantity), SumMoney = shipmentGroup.Sum(s => s.SumMoney) };
pradeep variyath9y ago
Very simple and easy to understand.
Sonu Chaudhary10y ago
good one
Chandu Kumawat10y ago
very nice .. thanxx
Abhinav Pal11y ago
Great Work Sir.Very Helping.Thanks for all.
Upendra Pratap Shahi11y ago
good sir.
James Hardy11y ago
Hello, could you show an example of using a 'Where' clause?.... Thanks
Devesh Omar11y ago
sample has been attached
Madhu Kumar11y ago
the project is not compatible with vs 2010 can you share ....? also sample for dynamic where plz
Madhu Kumar11y ago
nice article ,
Kalai12y ago
Good one
Former member12y ago
experting more like this
Gurunatha Dogi12y ago
Superb article bro
Pradeep Shet12y ago
good 1
Devesh Omar12y ago
Thanks All
Sanjay Kumar12y ago
Nice article Devesh
Guest User12y ago
One thought. can you also share how can we use PLINQ alongwith dynamism
Guest User12y ago
+1
Del Woodcock12y ago
Great article
Former member12y ago
Nice one