LINQ - Can't use predicate when joining two or more tables

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);
    }
}