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
- 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 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
Steps
Add following class as per 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;
}
}
- Adding data to Customer and Order List
- i) Defining Lists. We have defined 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 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 developer has defined columns to be display in compile time
We just want make it dynamic
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
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 snap shot
Columns to be display has been passed to select statement
8. Output
Following is output.
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
11. Sample data for Dynamic linq library
Following is just snap shot 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 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
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 display.
a
b.
d.
Conclusion
We have learned how to use Dynamic linq library to make our Linq query dymamic