IEnumerable Tricks 1 : DataTable and DataColumn

Tricks of TSQL type subqueries with IEnymerable extention methods OfType and Cast 

and using String function Join().

Simplify logic, to use sub queries in business layer's managed code your component

over Datatables  and datasets.

• Method OfType<TResult> : Returns An IEnumerable<T> that contains elements from the input sequence of type TResult.

             E.g:

ArrayList MSSequence = new ArrayList(4);
MSSequence.Add("C#");
MSSequence.Add("WCF");
MSSequence.Add("Silverlight");
MSSequence.Add(1.0);
MSSequence.Add(101);
MSSequence.Add("WWF");

// Apply OfType() to the ArrayList.
IEnumerable<string> query1 = MSSequence.OfType<string>();

// Here OfType will give elements that can be cast to type 'T', here it is 'string'
// If we use method Cast<TResult>(IEnumerable source), you will get exception for 
//element from source which could not be cast to tye 'T'

Response.Write("Elements of type 'string' are:");
foreach (string ele in query1)
{
Response.Write(ele + "<br>");
}

output :

Now, lets see what else can we do with it! 

In T-SQL sub queries are so easy. But when in come to do same in Data Access layer C# 2.0 coding, it’s a bit pain. Here’s lambda expression and IEnumerable methods for the rescue .

 //Say,For a Table [TblEmpInfo] with Columns [EmpID],[ProjectID],[Designation],[Departmet]
//you need to query like 

          Select * from [TblEmpInfo] 
Where ProjectID in (Select [ProjectID] from [TblEmpInfo] 
Where [EmpID] in (/*Some id's like ('E3','E5')*/))

E.g:

We have (dtSource )

You want (dtRequired ),

Following is what you need to code,        

DataTable dtSource = DataFetcher.GetTable("SP_GetEmpInfo");
//Assume DataFetcher is data access layer level object to perform DB 
//operations like GetDataTable or GetDataSet etc. And "SP_GetEmpInfo" Sp give data source
DataTable dtRequired = null;
//Get DataRows where we will get Project id's for employee E3 and E5
dtSource.DefaultView.RowFilter = "EmpID in('E5','E3')";
using (DataTable dtFilter = dtSource.DefaultView.ToTable())
{
//Get comma seperated ProjectId's to filter
string qryFilt = string.Join(", ", dtFilter.Rows.OfType<DataRow>().Select(r => r[dtFilter.Columns["projectid"]]));
dtSource.DefaultView.RowFilter = "projectid in (" + qryFilt + ")";
dtRequired = dtSource.DefaultView.ToTable();
}