LINQ :Querying in-memory collections/objects in ASP .NET

  This article is about Querying in-memory collection/objects in ASP .NET(C#) Using LINQ. NOTE:"objects" in this article refers to any in-memory collection like array,list etc or class objects.   LINQ (Language Integrated Query) introduced from .NET 3.5 provides very important programming features to developers. Using LINQ developer can perform query on in-memory objects data,just like we query in database like SQL Server. All developers may face scenario where they want to query in-memory objects like selecting data from DataTable according to specific criteria. This also avoids to write database query/stored procedures for each and every criteria if the data retrieved from database. We can write the same query in C# for in-memory objects thanks to LINQ. Query can be performed only on objects which implements IEnumerable<T>. Here is simple LINQ query:

int[] marks = { 20,55,34,89,70,60};

var over60 = from m in marks
             where m >= 60
             select m;

var firstClassMarks = over60.AsEnumerable().ToList();

In above example marks is an array of integers containing marks of 6 students. Now to get marks of students got first class we performed query on marks.  The query returns anonymous type data as m don't have any data type. So we must convert the return data from query to IEnumerable<T> and then to List. AsEnumerable() returns data as IEnumerable<int> in above example. firstClassMarks gets the data in list format using ToList()  for IEnumerable<int>.

 The result of above query is:

 Here is another example for string array:
 

 Now let's perform query on DataTable. LINQ can increase performance and avoid trip to database for some cases. Yes, let's consider case where you have gridview showing all data, and you have filters defined on web page. So if user change the filter, we will send the filter data to stored procedure or use in database query to get filtered data. But hey,you have already retrieved all data in previous trip to database server. So why don't we use that data again and perform query on that data. So let's perform query on DataTable in C#.

DataSet studentDs = student.getAllStudent();
        
var query = from t in studentDs.Tables[0].AsEnumerable()
            where t.Field<string>("department")=="Computer"
            select t;
var datatable = query.CopyToDataTable();

Now let's add filter to gridview and using LINQ we will filter the data. I have added Department Filter using DropDownList as shown below.

  

When Data is bound to gridview I have stored the DataTable in ViewState object to be used while performing filter operation. The code for filter data is:

 protected void btnFilter_Click(object sender, EventArgs e)
    {
        var department = ddlDepartmentFilter.SelectedItem.Text;
        DataTable studentDt = (DataTable)ViewState["studentDt"];
        
        var query = from t in studentDt.AsEnumerable()
                    where t.Field<string>("department")==department
                    select t;
        var dt = query.CopyToDataTable();
        grdStudent.DataSource = query.CopyToDataTable();
        grdStudent.DataBind();
        
    }

The Output is:

 

  SELECT :- We can customize Select operator to select only specific data say only Name in above case. But the problem is that it will create IEnumerable<anonymous Type>, on which we can't perform CopyToDataTable(). For that we must write code to convert this data to DataTable.

var query = from t in studentDt.AsEnumerable()
                    where t.Field<string>("department")==department
                    select new { Name= t.Field<string>("name") }
                    ;
var names = query.AsEnumerable().ToList();
//Create New DataTable 
DataTable newDt = new DataTable();
newDt.Columns.Add( new DataColumn("Name",typeof(string)));
foreach (var n in names)
{
    DataRow newRow = newDt.NewRow();
    newRow["Name"] = n.Name;
    newDt.Rows.Add(newRow);
}

//Use newDt from here

The new keyword is used to create anonymous type,obviously its returns IEnumerable<anonymous type>. The contents of newDt for department Computer is:

 

  Sorting:- We can sort the query result for specific field same as we use "Order By" in SQL database query for sort. In above scenario to get Name in sorted order we can write query as:

 var query = from t in studentDt.AsEnumerable()
             where t.Field<string>("department")==department
             orderby t.Field<string>("name")   //Order by fieldName
             select new { Name= t.Field<string>("name") };

The result will be IEnumerable<anonymous type> so you can not use CopyToDataTable() for above query result. Join:- Join can be performed on two in-memory objects on matching key extracted from the data. If you are familiar with Join in database then LINQ also does this job in C#. We can join different type of collection as well like string array with DataTable.

string[] dep = { "Computer", "Civil" };

var query = from d in dep 
            join t in studentDt.AsEnumerable() 
            on d equals t.Field<string>("department")
            select t;
      
var dt = query.CopyToDataTable();

The dt will now contain:

 

  Grouping:- Same as database "Group By", we can group in-memory object data based on grouping key. We will use "into" keyword for referencing the grouped data. The use of into keyword is not compulsory but it eliminates use of nested query.

 //using into keyword
var query = from t in studentDs.AsEnumerable() 
            group t by t.Field<string>("department") into g
            select new {_department=g.Key,count=g.Count() };


//using nested query
var query_ = from g in
             (from t in studentDs.AsEnumerable()
              group t by t.Field<string>("department"))
              select new { _department = g.Key, count = g.Count() };

The output of above query is:

 

 I have covered basic functions of LINQ and also gave some idea about how we can use it to minimize database server trip. I hope you've learned from this article. If you have any query/suggestions please comment below.