http://stackoverflow.com/questions/10451015/linq-filter-datatable-by-n-columns
http://www.albahari.com/nutshell/predicatebuilder.aspx
Code
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
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$#FormDate:9/23/2014$#ToDate:9/27/2014$#Distribution:,Client-Approved#Internal$#GoalsBasedSegment:,Segment5#Segment8";
string[] controlSplit = SelectonCriteria.Split(new string[] { "$#" }, StringSplitOptions.None);
var colsExpsTest = new List<Tuple<string, string>>();
string fromDate = string.Empty;
string toDate = string.Empty;
string dateRange = string.Empty;
foreach (var item in controlSplit)
{
var coloumnSplit = item.Split(':');
if (coloumnSplit.ElementAt(0) == "FormDate")
{
fromDate = coloumnSplit.ElementAt(1);
}else if(coloumnSplit.ElementAt(0) == "ToDate")
{
toDate = coloumnSplit.ElementAt(1);
}
else
{
colsExpsTest.Add(new Tuple<string, string>(coloumnSplit.ElementAt(0), coloumnSplit.ElementAt(1).TrimStart(',')));
}
dateRange = fromDate + "$#" + toDate;
}
if (fromDate != null && toDate != null)
{
colsExpsTest.Add(new Tuple<string, string>("DateRange", dateRange));
}
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;
//test
var query = (from dr in dataTableToPage.AsEnumerable()
select dr);
var predicate = PredicateBuilder.False<DataRow>();
foreach (var pair in columnToExpression)
{
string columnFilter = pair.Item1;
string expression = pair.Item2;
if (columnFilter == "DateRange")
{
var dateRange = expression.Split(new string[] { "$#" }, StringSplitOptions.None);
//var filteredData = db.TABLE.Where(t => t.DATE > startDate && t.DATE < endDate);
//predicate = predicate.Or(dr => dr[columnFilter].ToString().Contains(expression));
predicate = predicate.Or(dr => dr.Field<DateTime>("PublishDate") > Convert.ToDateTime(dateRange.ElementAt(0)) && dr.Field<DateTime>("PublishDate") < Convert.ToDateTime(dateRange.ElementAt(1)));
}
else
{
predicate = predicate.Or(dr => dr[columnFilter].ToString().Contains(expression));
}
//predicate = predicate.Or(dr => dr[columnFilter].ToString() == expresion);
//predicate = predicate.And(dr => dr[columnFilter].ToString() == expression);
}
query = query.Where(predicate.Compile());
if (query != null)
{
var check = query.CopyToDataTable();
}
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 if (columnFilter == "DateRange")
{
var dateRange = expression.Split(new string[] { "$#" }, StringSplitOptions.None);
productsQuery = dataTableToPage.AsEnumerable().Where(dr => dr.Field<DateTime>("PublishDate") > Convert.ToDateTime(dateRange.ElementAt(0)) && dr.Field<DateTime>("PublishDate") < Convert.ToDateTime(dateRange.ElementAt(1)));
}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;
}
}
public static class PredicateBuilder
{
public static Expression<Func<T, bool>> True<T>() { return f => true; }
public static Expression<Func<T, bool>> False<T>() { return f => false; }
public static Expression<Func<T, bool>> True<T>(IQueryable<T> query) { return f => true; }
public static Expression<Func<T, bool>> False<T>(IQueryable<T> query) { return f => false; }
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
}
}
}
//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;
//}
Extras
Dynamically Composing Expression Predicates
Suppose you want to write a LINQ to SQL or Entity Framework query that implements a keyword-style search. In other words, a query that returns rows whose description contains some or all of a given set of keywords.
We can proceed as follows:
IQueryable<Product> SearchProducts (params string[] keywords)
{
IQueryable<Product> query = dataContext.Products;
foreach (string keyword in keywords)
{
string temp = keyword;
query = query.Where (p => p.Description.Contains (temp));
}
return query;
}
The temporary variable in the loop is required to avoid the outer variable trap, where the same variable is captured for each iteration of the foreach loop.
So far, so good. But this only handles the case where you want to match all of the specified keywords. Suppose instead, we wanted products whose description contains any of the supplied keywords. Our previous approach of chaining Where operators is completely useless! We could instead chain Union operators, but this would be inefficient. The ideal approach is to dynamically construct a lambda expression tree that performs an or-based predicate.
Of all the things that will drive you to manually constructing expression trees, the need for dynamic predicates is the most common in a typical business application. Fortunately, it’s possible to write a set of simple and reusable extension methods that radically simplify this task. This is the role of our PredicateBuilder class.
Using PredicateBuilder
Here's how to solve the preceding example with PredicateBuilder:
IQueryable<Product> SearchProducts (params string[] keywords)
{
var predicate = PredicateBuilder.False<Product>();
foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or (p => p.Description.Contains (temp));
}
return dataContext.Products.Where (predicate);
}
If querying with Entity Framework, change the last line to this:
return objectContext.Products.AsExpandable().Where (predicate);
The AsExpandable method is part of
LINQKit (see below).
The easiest way to experiment with PredicateBuilder is with
LINQPad. LINQPad lets you instantly test LINQ queries against a database or local collection and has direct support for PredicateBuilder (press F4 and check 'Include PredicateBuilder').
PredicateBuilder Source Code
Here's the complete source:
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
public static class PredicateBuilder
{
public static Expression<Func<T, bool>> True<T> () { return f => true; }
public static Expression<Func<T, bool>> False<T> () { return f => false; }
public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
return Expression.Lambda<Func<T, bool>>
(Expression.OrElse (expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
return Expression.Lambda<Func<T, bool>>
(Expression.AndAlso (expr1.Body, invokedExpr), expr1.Parameters);
}
}
PredicateBuilder is also shipped as part of
LINQKit, a productivity kit for LINQ to SQL and Entity Framework.
If you're using LINQ to SQL, you can use the PredicateBuilder source code on its own.
If you're using Entity Framework, you'll need the complete
LINQKit - for the
AsExpandable functionality. You can either reference LINQKit.dll or copy LINQKit's source code into your application.
How it Works
The True and False methods do nothing special: they are simply convenient shortcuts for creating anExpression<Func<T,bool>> that initially evaluates to true or false. So the following:
var predicate = PredicateBuilder.True <Product> ();
is just a shortcut for this:
Expression<Func<Product, bool>> predicate = c => true;
When you’re building a predicate by repeatedly stacking and/or conditions, it’s useful to have a starting point of either true or false (respectively). Our SearchProducts method still works if no keywords are supplied.
The interesting work takes place inside the And and Or methods. We start by invoking the second expression with the first expression’s parameters. An Invoke expression calls another lambda expression using the given expressions as arguments. We can create the conditional expression from the body of the first expression and the invoked version of the second. The final step is to wrap this in a new lambda expression.
Entity Framework's query processing pipeline cannot handle invocation expressions, which is why you need to call AsExpandable on the first object in the query. By calling AsExpandable, you activate LINQKit's expression visitor class which substitutes invocation expressions with simpler constructs that Entity Framework can understand.
More Examples
A useful pattern in writing a data access layer is to create a reusable predicate library. Your queries, then, consist largely ofselect and orderby clauses, the filtering logic farmed out to your library. Here's a simple example:
public partial class Product
{
public static Expression<Func<Product, bool>> IsSelling()
{
return p => !p.Discontinued && p.LastSale > DateTime.Now.AddDays (-30);
}
}
We can extend this by adding a method that uses PredicateBuilder:
public partial class Product
{
public static Expression<Func<Product, bool>> ContainsInDescription (
params string[] keywords)
{
var predicate = PredicateBuilder.False<Product>();
foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or (p => p.Description.Contains (temp));
}
return predicate;
}
}
This offers an excellent balance of simplicity and reusability, as well as separating business logic from expression plumbing logic. To retrieve all products whose description contains “BlackBerry” or “iPhone”, along with the Nokias and Ericssons that are selling, you would do this:
var newKids = Product.ContainsInDescription ("BlackBerry", "iPhone");
var classics = Product.ContainsInDescription ("Nokia", "Ericsson")
.And (Product.IsSelling());
var query =
from p in Data.Products.Where (newKids.Or (classics))
select p;
The And and Or methods in boldface resolve to extension methods in PredicateBuilder.
An expression predicate can perform the equivalent of an SQL subquery by referencing association properties. So, if Product had a child EntitySet called Purchases, we could refine our IsSelling method to return only those products that have sold a minimum number of units as follows:
public static Expression<Func<Product, bool>> IsSelling (int minPurchases)
{
return prod =>
!prod.Discontinued &&
prod.Purchases.Where (purch => purch.Date > DateTime.Now.AddDays(-30))
.Count() >= minPurchases;
}
Nesting Predicates
Consider the following predicate:
p => p.Price > 100 &&
p.Price < 1000 &&
(p.Description.Contains ("foo") || p.Description.Contains ("far"))
Let's say we wanted to build this dynamically. The question is, how do we deal with the parenthesis around the two expressions in the last line?
The answer is to build the parenthesised expression first, and then consume it in the outer expression as follows:
var inner = PredicateBuilder.False<Product>();
inner = inner.Or (p => p.Description.Contains ("foo"));
inner = inner.Or (p => p.Description.Contains ("far"));
var outer = PredicateBuilder.True<Product>();
outer = outer.And (p => p.Price > 100);
outer = outer.And (p => p.Price < 1000);
outer = outer.And (inner);
Notice that with the inner expression, we start with PredicateBuilder.False (because we're using the Or operator). With the outer expression, however, we start with PredicateBuilder.True (because we're using the And operator).
Generic Predicates
Suppose every table in your database has ValidFrom and ValidTo columns as follows:
create table PriceList
(
ID int not null primary key,
Name nvarchar(50) not null,
ValidFrom datetime,
ValidTo datetime
)
To retrieve rows valid as of DateTime.Now (the most common case), you'd do this:
from p in PriceLists
where (p.ValidFrom == null || p.ValidFrom <= DateTime.Now) &&
(p.ValidTo == null || p.ValidTo >= DateTime.Now)
select p.Name
Of course, that logic in bold is likely to be duplicated across multiple queries! No problem: let's define a method in the PriceListclass that returns a reusable expression:
public static Expression<Func<PriceList, bool>> IsCurrent()
{
return p => (p.ValidFrom == null || p.ValidFrom <= DateTime.Now) &&
(p.ValidTo == null || p.ValidTo >= DateTime.Now);
}
OK: our query is now much simpler:
var currentPriceLists = db.PriceLists.Where (PriceList.IsCurrent());
And with PredicateBuilder's And and Or methods, we can easily introduce other conditions:
var currentPriceLists = db.PriceLists.Where (
PriceList.IsCurrent().And (p => p.Name.StartsWith ("A")));
But what about all the other tables that also have ValidFrom and ValidTo columns? We don't want to repeat our IsCurrentmethod for every table! Fortunately, we can generalize our IsCurrent method with generics.
The first step is to define an interface:
public interface IValidFromTo
{
DateTime? ValidFrom { get; }
DateTime? ValidTo { get; }
}
Now we can define a single generic IsCurrent method using that interface as a constraint:
public static Expression<Func<TEntity, bool>> IsCurrent<TEntity>()
where TEntity : IValidFromTo
{
return e => (e.ValidFrom == null || e.ValidFrom <= DateTime.Now) &&
(e.ValidTo == null || e.ValidTo >= DateTime.Now);
}
The final step is to implement this interface in each class that supports ValidFrom and ValidTo. If you're using Visual Studio or a tool like SqlMetal to generate your entity classes, do this in the non-generated half of the partial classes:
public partial class PriceList : IValidFromTo { }
public partial class Product : IValidFromTo { }
