LINQ filter Datatable by n Columns

http://stackoverflow.com/questions/10451015/linq-filter-datatable-by-n-columns

Remove the parameters columnFilter and expression and instead pass your data in a collection of columnsFilter and expression. You can create a custom class or just use a list ofTuple<string,string> like this:

public static DataTable Page(DataTable dataTableToPage, int pageSize, int pageNumber, 
    IList<Tuple<string, string>> columnToExpression,
    out int totalrecords, Operator Operator)

To create the Tuple you are doing something like this:

var colsExps = new List<Tuple<string, string>>();
colsExps.Add(new Tuple<string,string>("Name", "John"));
colsExps.Add(new Tuple<string,string>("City", "Miami"));

Then modify the code in your queries to something like this:

if (Operator == Operator.Equal)     
{     
    query = (from dr in dataTableToPage.AsEnumerable()     
                 select dr);     
    foreach (var pair in columnToExpression)
    {
       string columnFilter = pair.Item1;
       string expression = pair.Item2;
       query = query.Where (dr => dr[columnFilter].ToString() == expresion);
    }    
}     
else if(Operator == Operator.Like)     
{     
    query = (from dr in dataTableToPage.AsEnumerable()     
                 select dr);     
    foreach (var pair in columnToExpression)
    {
       string columnFilter = pair.Item1;
       string expression = pair.Item2;
       query = query.Where (dr => dr[columnToFilter].ToString().IndexOf(expression,
                    StringComparison.OrdinalIgnoreCase) >= 0);
    }    
}

Alternatively, you can use PredicateBuilder:

query = (from dr in dataTableToPage.AsEnumerable()     
             select dr);     

// I'm not sure exactly what T you should be using in the following statement:
// DataRow should be the type of dr
var predicate = PredicateBuilder.False<DataRow>(); 

foreach (var pair in columnToExpression)
{
   string columnFilter = pair.Item1;
   string expression = pair.Item2;
   predicate = predicate.Or(dr => dr[columnFilter].ToString() == expresion);
}    

query = query.Where(predicate.Compile());

Since I haven't test this, I wonder if you need to do dataTableToPage.AsQueryable() instead of AsEnumerable()?

edited May 5 '12 at 1:31

rpmlins
6018

answered May 4 '12 at 15:32

Brad Rem
3,99211030

Creating a class with properties for columnName, filterValue, and operator is probably best – Jim McKeon May 4 '12 at 15:39

@brad I think your solution work, but it does not work for me as I wanted to, I think I missed a point on my question... but anyway with your solution, Is like using an 'AND' ont he where clause, if I wanted to get a result using 'OR' that would not be possible. but tkx anyway +1 – rpmlins May 4 '12 at 15:41

@rpmlins, I've added some info on PredicateBuilder which should help you do your OR comparisons. – Brad Rem May 4 '12 at 16:26

well sadly I cant use dataTableToPage.AsQueryable() and I am receiving an error because of it :( – rpmlins May 4 '12 at 18:07

okey just got the solution query = query.Where(predicate.Compile()); Thank you very much @Brad – rpmlins May 4 '12 at 18

You shouldn't query your DataTable in a class or method where you want to do pagination. That's breaking the responsibility of the class. You should rather separate those two behavior.

You can do it like that:

public static class Paging
{
    public static DataTable Page(this IEnumerable<DataRow> dataTableQuery, int pageSize, int pageNumber, out int totalrecords)
    {
        int skip = (pageNumber - 1) * pageSize;

        var queryConverted = dataTableQuery.Skip(skip).Take(pageSize);

        if (queryConverted.Count() > 0)
        {
            totalrecords = query.Count();

            return queryConverted.CopyToDataTable();
        }
        totalrecords = 0;

        return new DataTable();
    }
}

Now that code is only caring about paging.

And for the query:

public static class DataTableQuery
{
    public static IEnumerable<DataRow> Where(this DataTable dataTable, string columnName, string expression) {
        return from dr in dataTableToPage.AsEnumerable()
                     where dr[columnToFilter].ToString() == expression
                     select dr;
    }

    public static IEnumerable<DataRow> Like(this DataTable dataTable, string columnName, string expression) {
        return from dr in dataTableToPage.AsEnumerable()
                     where dr[columnToFilter].ToString().IndexOf(expression, StringComparison.OrdinalIgnoreCase) >= 0
                     select dr;
    }
}

I did them as Extension methods because in this case you are just extending that class behavior and they read nicely.

And you can use the code by doing:

int totalRecords = 0;
DataTable dataTable = ...;
var page = dataTable.Where("Name", "Jhon").Page(1, 1, out totalRecords);
var page = dataTable.Like("City", "florida").Page(2, 3, out totalRecords);

answered May 4 '12 at 17:22

Adauto
32614

thank you for the advice @Adauto!! since my rep is too low, I cant vote up! but thx a lot – rpmlins May 4 '12 at 18:19

No problem! I just want to be aware that separating the concerns, you can add as many "filters" as you like and the paging will still work the same way because you didn't change it. I hope you see the advantages of that and I hope you like the solution. – Adauto May 4 '12 at 18:23

I really like it!!! thx!!! – rpmlins May 4 '12 at 18:43

add a comment

up vote0down vote

I think you will need some sort of parser/walker to transform your string expressions into linq expressions. Once you have that you 'll be able to apply those filters. You need something like a tiny linq provider. At least that's the only way to come to my mind...

answered May 4 '12 at 15:01

amiralles
417410

add a comm

www.google.com

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
class Program
{

public delegate int Add(int x, int y);
static void Main(string[] args)
{

//Func<double, double> cpointer = r => r * 3.14;
//Console.WriteLine(cpointer(4));
//Console.ReadKey();
//Add objAdd = AddTwoNumbers;
//Add objAdd = (a, b) => a + b;
//Console.WriteLine(objAdd(4,5));

DataTable dt = new DataTable();
dt.Columns.Add("AssetType", typeof(string));
dt.Columns.Add("AssetDescription", typeof(string));
dt.Columns.Add("PublishDate", typeof(DateTime));
dt.Columns.Add("Distribution", typeof(string));
dt.Columns.Add("GoalsBasedSegment", typeof(string));

dt.Rows.Add("Document", "Material Document", "9/23/2014", "Client-Approved#Internal", "Segment5#Segment9");
dt.Rows.Add("Article", "Article Document", "9/27/2014", "Internal#Forms", "Segment12#Segment9");
dt.Rows.Add("Page", "Page Document", "9/29/2014", "Client-Approved#Internal", "Segment5#Segment9");

string SelectonCriteria = "AssetType:Document$#AssetDescription:Matrial Document$#PublishDate:9/23/2014$#Distribution:,Client-Approved#Internal$#GoalsBasedSegment:,Segment5#Segment8";

string[] controlSplit = SelectonCriteria.Split(new string[]{"$#"},StringSplitOptions.None);

var colsExpsTest = new List<Tuple<string, string>>();

foreach (var item in controlSplit)
{
var coloumnSplit=item.Split(':');
colsExpsTest.Add(new Tuple<string, string>(coloumnSplit.ElementAt(0), coloumnSplit.ElementAt(1).TrimStart(',')));
}

DataTable dttest = Page(dt, colsExpsTest);

dttest = dttest.AsEnumerable().Distinct(DataRowComparer.Default).CopyToDataTable();

var test = 1;
}
public static DataTable Page(DataTable dataTableToPage, IList<Tuple<string, string>> columnToExpression)
{
DataTable dt = dataTableToPage.Clone();
IEnumerable<DataRow> productsQuery;
foreach (var pair in columnToExpression)
{
string columnFilter = pair.Item1;
string expression = pair.Item2;
if (columnFilter == "Distribution" || columnFilter == "GoalsBasedSegment")
{
//string[] sTypes = expression.Split(',');
productsQuery = dataTableToPage.AsEnumerable().Where(r => r.Field<string>(columnFilter).Contains(expression));
//productsQuery = dataTableToPage.AsEnumerable().ToList().Where(item => expression.Split(',').Contains(item.Field<string>(columnFilter).ToString()));
}
else
{
productsQuery = dataTableToPage.AsEnumerable().Where(dr => dr[columnFilter].ToString().IndexOf(expression, StringComparison.OrdinalIgnoreCase) >= 0);
}

foreach (var item in productsQuery)
{
dt.ImportRow(item);
}
dt.AcceptChanges();
}
return dt;
}
}

}
//dt.Rows.Add("203456879", "Smith", "12 Main Street, Newyork, NY", 45);
//dt.Rows.Add("203456880", "SAM", "345 Main Ave, Dayton, OH", 55);
//dt.Rows.Add("203456881", "Sue", "32 Cranbrook Rd, Newyork, NY", 65);
//dt.Rows.Add("203456882", "Winston", "1208 Alex St, Newyork, NY", 65);
//dt.Rows.Add("203456883", "Mac", "126 Province Ave, Baltimore, NY", 85);
//dt.Rows.Add("203456884", "SAM", "126 Province Ave, Baltimore, NY", 95);

//var test = 1;
//string sample = ",liger, unicorn, snipe";
//sample = sample.TrimStart(','); // to remove just the first comma

//sample = sample.Trim().TrimStart(',');

//var myProftype = profiles.First(p => p.Id == StUserSet.utoken);
//string sProftype = myProftype.profile_accomtypes;
//string[] sTypes = sProftype.Split(',');
//var myTAccomtypes = propTypes.Where(r => sTypes.Contains(r.Field<int>("Id").ToString()));
//StringBuilder sb = new StringBuilder(0);

//foreach (PropType s in myTAccomtypes)
//{
// sb.Append("<dd>" + s.Description + "</dd>");
//}

//myList = myList.Where(item => indexList.Split(',').Contains(item.IndexID.ToString())).ToList();

//var colsExps = new List<Tuple<string, string>>();
//colsExps.Add(new Tuple<string, string>("Name", "John"));
//colsExps.Add(new Tuple<string, string>("AGE", "95"));

//dataTableToPage = dataTableToPage.AsEnumerable().Where(dr => dr[columnFilter].ToString().IndexOf(expression,
// StringComparison.OrdinalIgnoreCase) >= 0).CopyToDataTable();
//productsQuery = from dr in dataTableToPage.AsEnumerable()
// where dr[columnFilter].ToString().IndexOf(expression, StringComparison.OrdinalIgnoreCase) >= 0
// select dr;
//dataTableToPage = (from dr in dataTableToPage.AsEnumerable()
// where dr[columnFilter].ToString().Contains(expression)
// select dr).CopyToDataTable();

//public static int AddTwoNumbers(int a,int b)
//{
// return a + b;
//}

Important Links

Monday, September 29, 2014

LINQ filter Datatable by n Columns

No comments:

Post a Comment