Hi!
Scenario
Imagine that you're trying (like me) to write a "dynamic" where to your select
var myTerritories = (from te in lObjDC.Territories
join re in lObjDC.Regions
on te.RegionID equals re.RegionID
select new
{
TerritoryID = te.TerritoryID,
TerritoryDescription = te.TerritoryDescription,
RegionDescription = re.RegionDescription
}).Where(w => w.TerritoryDescription.Contains(pStrSearchString));
To accomplish such thing you'll have to use predicate concept.
var predicate = PredicateBuilder.True<Territory>();
The problem is:
When you execute join using link, it's gonna result in a anonymous type of object. But to build a predicate you need to reference an object. So, if I try something like that:
var predicate = PredicateBuilder.True<Territory>();
predicate = predicate.And(e => e.TerritoryDescription.ToLower().Contains(pStrSearchString.ToLower().Trim()));
var myTerritories = (from te in lObjDC.Territories
join re in lObjDC.Regions
on te.RegionID equals re.RegionID
select new
{
TerritoryID = te.TerritoryID,
TerritoryDescription = te.TerritoryDescription,
RegionDescription = re.RegionDescription
}).Where(predicate );
pStrSearchString - it's a parameter variable from my code which I borrow and wrote here.
I'm gonna receive a design mode error. Why? Because the predicate is using Territory as reference. But the result of the select is anonymous.
Solution
So if I can't use predicate when it comes to join, what can I do? Well, why don't you create a view v_Territory . The view will resolve the problem. With that in mind, the select will result in something like taht:
var predicate = PredicateBuilder.True<Territory>();
predicate = predicate.And(e => e.TerritoryDescription.ToLower().Contains(pStrSearchString.ToLower().Trim()));
var myTerritories = lObjDC.Territories.Where(predicate);
Remember that your view will be something like that
select a.TerritoryID, a.TerritoryDescription, b.RegionDescription
from Territories a
join Region b
on a.RegionID = b.RegionID
Why all of this explanation about predicate? If you are a more or less experienced programmer, you already know that dynamic where will be eventually necessary.
If you don't have the class PredicateBuilder built-in. Here is the code:
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);
}
}
