Asked by:
A straight forward way to construct a dynamic query/dynamic where clause in LightSwitch

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
- Changed type Justin AndersonMicrosoft employee, Moderator Saturday, February 18, 2012 12:41 PM Not a question
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
- Edited by Paul Van Bladel Sunday, February 26, 2012 7:18 AM
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/GermanyFriday, March 30, 2012 12:02 PM -
You are welcome. Greatings from Brussels :)
paul van bladel
Friday, March 30, 2012 12:40 PM -
Vote to enable dynamic queries out-of-the-box:
Monday, December 2, 2013 10:52 PM