How to write dynamic where clause for join range varible

Question

I‘ve been working on a query using LINQ but I’ve run into a snag with a dynamic where clause. I want to check for a condition and if true then add that where to my query. The problem is that the where is using one of my range variables from one of my joins. My working query is below:

var query = from project in db.ProjMasters
             join pd in db.ProjDetails on project.ProjMasterID equals pd.ProjMasterID
             join dc in db.DivCodes on project.DivisionCode equals dc.DivCode1
             join ec in db.EmpCodes on project.ProjManager equals ec.UserNm
             join ptc in db.ProjTypeCodes on pd.ProjTypeCode equals ptc.ProjTypeCode1
             join psc in db.ProjStatusCodes on pd.ProjStatusCode equals psc.ProjStatusCode1
             where pd.ProjDeleteDate == null
             orderby project.Title
             select new
             {
                  project.ProjMasterID,
                  project.Title,
                  pd.ProjDesc,
                  pd.ContractNum,
                  pd.ProjDetailID,
                  dc.DivNm,
             }
             if (sTitle != null)
             {
                 query = query.Where(x => x.Title.Contains(sTitle));
             }

TypeDesc is a type in db.ProjTypeCodes, so I want to say

if (sProjType != null)
{
    query = query.Where(x => x.TypeDesc==sProjType);
}

But I can only use where with types in project; "AnonymousType#1 does not contain a definition for 'TypeDesc'..." How can I use a dynamic where on ptc.TypeDesc?

Answer 1

the problem is your order, at the select you are discarding all data not in the select statement, if you use a Inline if statement then you can easily do it in the first where clause or you can separate the select out after though you may have to convery from a Linq query to linq statments

var query = from project in db.ProjMasters
             join pd in db.ProjDetails on project.ProjMasterID equals pd.ProjMasterID
             join dc in db.DivCodes on project.DivisionCode equals dc.DivCode1
             join ec in db.EmpCodes on project.ProjManager equals ec.UserNm
             join ptc in db.ProjTypeCodes on pd.ProjTypeCode equals ptc.ProjTypeCode1
             join psc in db.ProjStatusCodes on pd.ProjStatusCode equals psc.ProjStatusCode1
             where pd.ProjDeleteDate == null
                && sProjType!= null ? ptc.TypeDesc==sProjType): true
             orderby project.Title
             select new
             {
                  project.ProjMasterID,
                  project.Title,
                  pd.ProjDesc,
                  pd.ContractNum,
                  pd.ProjDetailID,
                  dc.DivNm,
             }
             if (sTitle != null)
             {
                 query = query.Where(x => x.Title.Contains(sTitle));
             }

Answer 2

Use IQueryable here is more info why link

IQueryable<{CreateNewSelectObjects}> query = ... ;
if (sProjType != null)
{
    query = query.Where(x => x.TypeDesc==sProjType);
}

Answer 3

Your query is good candidate for trying my recent custom extension method posted to "Nullable object must have a value" exception after checking for null on a non-primitive/non-struct object.

The idea is simple. You build your dynamic criteria directly inside the query, and the method converts it as if you used ifs:

var query = (from project in db.ProjMasters
             join pd in db.ProjDetails on project.ProjMasterID equals pd.ProjMasterID
             join dc in db.DivCodes on project.DivisionCode equals dc.DivCode1
             join ec in db.EmpCodes on project.ProjManager equals ec.UserNm
             join ptc in db.ProjTypeCodes on pd.ProjTypeCode equals ptc.ProjTypeCode1
             join psc in db.ProjStatusCodes on pd.ProjStatusCode equals psc.ProjStatusCode1
             where pd.ProjDeleteDate == null
                 && (sProjType == null || ptc.TypeDesc == sProjType)
                 && (sTitle == null || project.Title.Contains(sTitle))
             orderby project.Title
             select new
             {
                  project.ProjMasterID,
                  project.Title,
                  pd.ProjDesc,
                  pd.ContractNum,
                  pd.ProjDetailID,
                  dc.DivNm,
             }).ReduceConstPredicates();

P.S. Here is the source code of the used method in case something happens with the link:

public static class QueryableExtensions
{
    public static IQueryable<T> ReduceConstPredicates<T>(this IQueryable<T> source)
    {
        var reducer = new ConstPredicateReducer();
        var expression = reducer.Visit(source.Expression);
        if (expression == source.Expression) return source;
        return source.Provider.CreateQuery<T>(expression);
    }

    class ConstPredicateReducer : ExpressionVisitor
    {
        private int evaluateConst;
        private bool EvaluateConst { get { return evaluateConst > 0; } }
        private ConstantExpression TryEvaluateConst(Expression node)
        {
            evaluateConst++;
            try { return Visit(node) as ConstantExpression; }
            catch { return null; }
            finally { evaluateConst--; }
        }
        protected override Expression VisitUnary(UnaryExpression node)
        {
            if (EvaluateConst || node.Type == typeof(bool))
            {
                var operandConst = TryEvaluateConst(node.Operand);
                if (operandConst != null)
                {
                    var result = Expression.Lambda(node.Update(operandConst)).Compile().DynamicInvoke();
                    return Expression.Constant(result, node.Type);
                }
            }
            return EvaluateConst ? node : base.VisitUnary(node);
        }
        protected override Expression VisitBinary(BinaryExpression node)
        {
            if (EvaluateConst || node.Type == typeof(bool))
            {
                var leftConst = TryEvaluateConst(node.Left);
                if (leftConst != null)
                {
                    if (node.NodeType == ExpressionType.AndAlso)
                        return (bool)leftConst.Value ? Visit(node.Right) : Expression.Constant(false);
                    if (node.NodeType == ExpressionType.OrElse)
                        return !(bool)leftConst.Value ? Visit(node.Right) : Expression.Constant(true);
                    var rightConst = TryEvaluateConst(node.Right);
                    if (rightConst != null)
                    {
                        var result = Expression.Lambda(node.Update(leftConst, node.Conversion, rightConst)).Compile().DynamicInvoke();
                        return Expression.Constant(result, node.Type);
                    }
                }
            }
            return EvaluateConst ? node : base.VisitBinary(node);
        }
        protected override Expression VisitConditional(ConditionalExpression node)
        {
            if (EvaluateConst || node.Type == typeof(bool))
            {
                var testConst = TryEvaluateConst(node.Test);
                if (testConst != null)
                    return Visit((bool)testConst.Value ? node.IfTrue : node.IfFalse);
            }
            return EvaluateConst ? node : base.VisitConditional(node);
        }
        protected override Expression VisitMember(MemberExpression node)
        {
            if (EvaluateConst || node.Type == typeof(bool))
            {
                var expressionConst = node.Expression != null ? TryEvaluateConst(node.Expression) : null;
                if (expressionConst != null || node.Expression == null)
                {
                    var result = Expression.Lambda(node.Update(expressionConst)).Compile().DynamicInvoke();
                    return Expression.Constant(result, node.Type);
                }
            }
            return EvaluateConst ? node : base.VisitMember(node);
        }
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (EvaluateConst || node.Type == typeof(bool))
            {
                var objectConst = node.Object != null ? TryEvaluateConst(node.Object) : null;
                if (objectConst != null || node.Object == null)
                {
                    var argumentsConst = new ConstantExpression[node.Arguments.Count];
                    int count = 0;
                    while (count < argumentsConst.Length && (argumentsConst[count] = TryEvaluateConst(node.Arguments[count])) != null)
                        count++;
                    if (count == argumentsConst.Length)
                    {
                        var result = Expression.Lambda(node.Update(objectConst, argumentsConst)).Compile().DynamicInvoke();
                        return Expression.Constant(result, node.Type);
                    }
                }
            }
            return EvaluateConst ? node : base.VisitMethodCall(node);
        }
    }
}