LINQ - Create & Execute LINQ query at runtime in C#

Asked By Varsha Ramnani on 27-Jan-11 07:31 AM
Hi All,

I have a requirement where i want to Group Data in the DataTable & Aggregate Other Columns according to the Grouped Columns in C#.

this is a common code so, Query, Columns & Data in the Datatable is not known to me before execution of the Function.

I found two options :
1) Using DataSetHelper Class as Shown in this article
http://www.digitalsupporttech.com/mskb/326/326145_HOW_TO:_Implement_a_DataSet_GROUP_BY_Helper_Class_in_Visual_C__.NET.htm

but this will take time to execute as it loops thru all the Rows in the DataTable & my Datatable contains mre than 10000 rows.

2) using LINQ.

this is better option if i know the LINQ Query which is not the case with me i dont even know that how many columns will be used for Grouping & how many will be used for aggregation (also which column will use sum,count or avg)

is there any way i can create a LINQ as a String at Runtime & run it??????

or is there any other solution which is faster & better than the Option - 1.



Thanx


Indranil Chatterjee replied to Varsha Ramnani on 30-Jan-11 03:03 AM
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
this is a link that talks about a dynamic LINQ library that allows queries like:
db.Employees.Where("EmpId>200 And Rank<4").OrderBy("RegistrationId");
See if this serves your purpose.
Varsha Ramnani replied to Varsha Ramnani on 30-Jan-11 10:44 PM
Hi,

Thanx for the link.

I have already downloaded this library but Group By is not working in this library

& also it does not work with Datasets it only works with either LINQ to SQL or Enumerable list of type Class & with DataTable it takes type as Enumerable Collection of DataRow & it is not able to find the Property (i.e. ColumnName in case of DataRow) coz it is ColumnName not the Property.

So, i am trying to change the Code in this library to support DataRow.
So, if you have any idea how i can solve it than pls let me know.

Thanx
Subhashini Janakiraman replied to Varsha Ramnani on 04-Feb-11 07:23 AM
I will give the LINQ Query to display the grouping and displaying of Aggregate function.Here is the code,
var computation=from t in Orders
              group t by t.orderid
              select new
             {
              orderid=t.orderid,
              productno=Count(t=>t.Product),
              amount=Sum(t=>t.total),
              quantity=Sum(t=>t.quantity)
               };
foreach(var c in computation)
{
Response.Write(c.orderid.ToString()+" "+c.producno.ToString()+" "+c.amount.ToString()+"  "+c.quantity.ToString());
}
Here I assume the columns of the Orders data table to be
orderid,
product,
quantity,
total,
customername,
location.
Hope this code gives the grouped computation for a particular orderid.
Varsha Ramnani replied to Subhashini Janakiraman on 04-Feb-11 11:19 PM

Thanx Subhashini for your answer.

But i already know how to do it if i know the Fieldnames in advance but this is not the case with me as i have mentioned before.

i will get the Group Field Names & Aggregate Field name & even the Query at runtime so this solution will not work for my case .


anyways thanx for ur reply