This Forum is ARCHIVE only
For LightSwitch questions,
please use the official forums at:

http://social.msdn.microsoft.com/forums/en-US/lightswitch/threads


Dynamic Query/Dynamic Where Clause
Last Post 17 Feb 2012 10:34 AM by brucevt. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
brucevtUser is Offline
New Member
New Member
Send Private Message
Posts:2
Avatar

--
17 Feb 2012 10:34 AM

    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" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx
    using System.Linq.Dynamic;      // Download code from: http://code.msdn.microsoft.com/Dyna...y-f65f6a4d" target="_blank" rel="nofollow">http://code.msdn.microsoft.com/Dyna...y-f65f6a4d

    ...

            // ------------------
            partial void PositionCodesFilteredByTypes_PreprocessQuery(bool? AreaCodeParam, bool? DistrictCodeParam, bool? GroupCodeParam, bool? OtherCodeParam, bool? AllParam, ref IQueryable 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" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx
                    var predicate = PredicateBuilder.False();

                    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/Dyna...y-f65f6a4d" target="_blank" rel="nofollow">http://code.msdn.microsoft.com/Dyna...y-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);
                }
            }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" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx
    using System.Linq.Dynamic;      // Download code from: http://code.msdn.microsoft.com/Dyna...y-f65f6a4d" target="_blank" rel="nofollow">http://code.msdn.microsoft.com/Dyna...y-f65f6a4d

    ...

            // ------------------
            partial void PositionCodesFilteredByTypes_PreprocessQuery(bool? AreaCodeParam, bool? DistrictCodeParam, bool? GroupCodeParam, bool? OtherCodeParam, bool? AllParam, ref IQueryable 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" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx" target="_blank" rel="nofollow">http://www.albahari.com/nutshell/linqkit.aspx
                    var predicate = PredicateBuilder.False();

                    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/Dyna...y-f65f6a4d" target="_blank" rel="nofollow">http://code.msdn.microsoft.com/Dyna...y-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);
                }
            }

    You are not authorized to post a reply.


    Microsoft Visual Studio is a registered trademark of Microsoft Corporation / LightSwitch is a registered trademark of Microsoft Corporation