Filtering DataTable with LINQ

I had a situation today where I needed to modify an existing method that fetched a DataTable  from the Data Access Layer, modified and returned it as a DataView.  My task was to filter the rows in the DataTable based on a call into managed code (ie: not something that could be done at the db level).

Now, I'm somewhat new to DataTables, having used ORMs for most of my .Net experience, so this was actually more difficult than I initially expected.  I was hoping to just set a value in RowFilter and be done. Unfortunately, as best I can tell, RowFilter does not allow row-specific dynamic filters (ie: you cannot call into a method with each row).  Furthermore, the return value of the method needed to stay as a DataView, since I'm tasked next with backporting  the change to our production branch and need to greatly limit the scope of my changes.

So after some googling, I was able to craft a solution using LINQ, which I wanted to document here for future reference.  Obviously, the code has been changed to protect the guilty, which has the side effect of greatly simplifying the logic. 

I needed to reference System.Data.DataSetExtentions to have access to the DataTable LINQ extensions. 

Then I did this:

     public abstract bool SecretFilteringMethod(int someId);

     public DataView RetrieveFilteredRecords(int someId)
        {

           DataSet ds = DALServiceProxy.RetrieveRecords(someId);
           DataTable myTable = ds.Tables[0];

            // this is bound to a UI drop-list, so add some usability enhancing rows
             ds.SuspendColumnValidation();
        
            DataRow row1 = myTable.NewRow();
            row1[Consts.Columns.Name] = "-- Select One --";
            row1[Consts.Columns.ID] = -1;
            myTable.Rows.InsertAt(row1, 0);

            ds.ResumeColumnValidation();

            // unlike most LINQ methods, this returns an EnumerableRowCollection<T> instead of IEnumerable<T>
            var rowsAfterManagedCodeFiltering = myTable.AsEnumerable()
                .Where(dpRow => !dpRow.IsNull(Consts.Columns.ImportantField))
                .Where(dpRow => SecretFilteringMethod((int)dpRow[Consts.Columns. ImportantField]));

            return rowsAfterManagedCodeFiltering.AsDataView();
        }

The return value of AsDataView() is a LinqDataView object, which has its Table property set to the original DataTable, so the result is fairly close to what would happen if I'd set a RowFilter. However, instead of RowFilter, the RowPredicate property is set with the LINQ representation. 

One thing to note: RowPredicate and RowFilter are mutually exclusive, so if a consumer later tries to set RowFilter to further refine the view, it will erase the RowPredicate, thus falling back to the base DataTable's full set of records. 

Personally, I'd rather move away from the DataTable altogether and return an IEnumerable<T>, but that wasn't really a logistical option at this point. 

Additionally, if you have an EnumerableRowCollection<T>, you can create a new DataTable with only the rows in the collection with the .CopyToDataTable() extension method.

References:

• LINQ query on a DataTable: http://stackoverflow.com/questions/10855/linq-query-on-a-datatable
• DataTableExtensions.AsDataView