locked
A straight forward way to construct a dynamic query/dynamic where clause in LightSwitch RRS feed

  • General discussion

  • Lightswitch is great, until you need to construct a linq dynamic query or where clause. All the feedback I got was convoluted code - hard to follow.

    I found that one can use either LinqKit and Dynamic Query to do this.

    It took me awhile to track this down so I figured I'd share it.

    Note that I just copied the code files from the downloaded projects into my LightSwitch project rather than linking to DLL's.

    The following is from a working example.

    using LinqKit;                  // Download code from: http://www.albahari.com/nutshell/linqkit.aspx
    using System.Linq.Dynamic;      // Download code from: http://code.msdn.microsoft.com/DynamicQuery-f65f6a4d
    
    ...
    
            // ------------------
            partial void PositionCodesFilteredByTypes_PreprocessQuery(bool? AreaCodeParam, bool? DistrictCodeParam, bool? GroupCodeParam, bool? OtherCodeParam, bool? AllParam, ref IQueryable<PositionCode> query)
            {
                const int useVersion = 3;   // Set to 1, 2 or 3: depending on the version to use
    
                // - - - - - - - - - - - - - -
                if (useVersion == 1)
                {
                    // Non-dynamic version
                    query = query.Where((x) => (AllParam == true ||
                                                ((x.AreaCode == true && AreaCodeParam == true) ||
                                                (x.DistrictCode == true && DistrictCodeParam == true) ||
                                                (x.GroupCode == true && GroupCodeParam == true) ||
                                                (x.OtherCode == true && OtherCodeParam == true))));
                    return;
                }
    
                if (AllParam != null && AllParam == true)
                {
                    return;
                }
    
                // - - - - - - - - - - - - - -
                if (useVersion == 2)
                {
                    /////////////////////////
                    // LinqKit:  http://www.albahari.com/nutshell/linqkit.aspx
                    var predicate = PredicateBuilder.False<PositionCode>();
    
                    if (AreaCodeParam != null && AreaCodeParam == true)
                    {
                        predicate = predicate.Or(p => (p.AreaCode == true));
                    }
    
                    if (DistrictCodeParam != null && DistrictCodeParam == true)
                    {
                        predicate = predicate.Or(p => (p.DistrictCode == true));
                    }
    
                    if (GroupCodeParam != null && GroupCodeParam == true)
                    {
                        predicate = predicate.Or(p => (p.GroupCode == true));
                    }
    
                    if (OtherCodeParam != null && OtherCodeParam == true)
                    {
                        predicate = predicate.Or(p => (p.OtherCode == true));
                    }
    
                    query = query.AsExpandable().Where(predicate);
                    return;
                }
    
                // - - - - - - - - - - - - - -
                if (useVersion == 3)
                {   
                    /////////////////////////
                    // System.Linq.Dynamic;  http://code.msdn.microsoft.com/DynamicQuery-f65f6a4d
                    string whereString = "1=2";
    
                    if (AreaCodeParam != null && AreaCodeParam == true)
                    {
                         whereString = whereString + " OR AreaCode = true";
                    }
    
                    if (DistrictCodeParam != null && DistrictCodeParam == true)
                    {
                        whereString = whereString + " OR DistrictCode = true";
                    }
    
                    if (GroupCodeParam != null && GroupCodeParam == true)
                    {
                        whereString = whereString + " OR GroupCode = true";
                    }
    
                    if (OtherCodeParam != null && OtherCodeParam == true)
                    {
                        whereString = whereString + " OR OtherCode = true";
                    }
    
                    query = query.Where(whereString);
                }
            }


    Bruce

    Saturday, February 18, 2012 2:38 AM

All replies

  • Hi Bruce,

    This looks like exactly what I've been looking for, thanks for posting!

    How do you include the LinqKit.dll in your LightSwitch project?

    Thanks,

    Dustin

    Saturday, February 25, 2012 7:12 AM
  • In case you prefer not to use a third-party lib , the following utility class will do pretty much the same:

    public static class Utility
        {
            public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
            {
                // build parameter map (from parameters of second to parameters of first)
                var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);
     
                // replace parameters in the second lambda expression with parameters from the first
                var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);
     
                // apply composition of lambda expression bodies to parameters from the first expression
                return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
            }
     
            public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
            {
                return first.Compose(second, Expression.And);
            }
     
            public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
            {
                return first.Compose(second, Expression.Or);
            }
         }
        public class ParameterRebinder : ExpressionVisitor
        {
            private readonly Dictionary<ParameterExpression, ParameterExpression> map;
     
            public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
            {
                this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
            }
     
            public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
            {
                return new ParameterRebinder(map).Visit(exp);
            }
     
            protected override Expression VisitParameter(ParameterExpression p)
            {
                ParameterExpression replacement;
                if (map.TryGetValue(p, out replacement))
                {
                    p = replacement;
                }
                return base.VisitParameter(p);
            }
        }


    paul van bladel

    Saturday, February 25, 2012 7:43 AM
  • I'm sure you can just add it as a reference. Not what I did though. I downloaded the source combined the files into one file, (LinqKit.cs) and added that to my project in Server\UserCode.

    Bruce

    Saturday, February 25, 2012 2:01 PM
  • Paul:

    Do you have any examples on how to use the utility class?


    Bruce

    Saturday, February 25, 2012 2:55 PM
  •  Expression<Func<Table1, bool>> StartWith = r => false;
     Expression<Func<Table1, bool>> predicate1 = r => r.field1== "abc";
     Expression<Func<Table1, bool>> predicate2 = r => r.field2== "xyz";
             
     
     Expression<Func<Table1, bool>> predicate =
              StartWith
              .Or(predicate1)
              .Or(predicate2);
     query = query.Where(predicate);

    The above can be inserted in a preprocessQuery method on Table1 entity.

    So, same style as the predicatebuilder.


    paul van bladel


    Saturday, February 25, 2012 7:38 PM
  • I test this with my application and it works good. Thanks for the info.

    Bruce

    Monday, February 27, 2012 8:58 PM
  • You are welcome Bruce !

    paul van bladel

    Tuesday, February 28, 2012 4:36 PM
  • Good Job! Thanks alot!
    Greatings from Berlin/Germany

    Friday, March 30, 2012 12:02 PM
  • You are welcome. Greatings from Brussels :)

    paul van bladel

    Friday, March 30, 2012 12:40 PM
  • Monday, December 2, 2013 10:52 PM