locked
Strange results using LINQ in PreprocessQuery RRS feed

  • Question

  • Hi there,

    I am building a lightswitch application that is using the results of a WCF RIA service to filter a query on another related entity.

    The WCF RIA service entity, called AllowedUsers, runs a stored procedure and returns the results as a list of salesrep_ids that the logged in user is allowed to see customer information for. We've been using the stored proc behind AllowedUsers (uses a CTE query) for quite some time now and it works well (the problem is not here as you will see).

    So I then need to take the AllowedUsers entity and filter another entity called tblCustomers where tblCustomers.SalesRep_ID contains any of the list of salesrep_ids (usernames) from AllowedUsers and show the resulting customers on a screen.  I'm attempting to do this with a query called AllowedCustomers on the tblCustomers entity.

    Its working correctly for the most part but I am getting some rows back from tblCustomers that I would not expect and I don't know where the problem is...I'm thinking it is in the LINQ I'm using in the AllowedCustomers_Preprocessquery.  This is what I'm using to filter the customers:

            partial void AllowedCustomers_PreprocessQuery(ref IQueryable<tblCustomer> query)
            {
                // Filter tblSalesEng for only allowed users
                var AllowedSalesReps = from AllowedUsers in this.DataWorkspace.AllowedUsers.AllowedUsers1
                                       select AllowedUsers;
                string userName;
                string stringWhere = null;
                int x = 0;
                foreach (var user in AllowedSalesReps)
                {
                    userName = user.ToString();
                    if (x == 0)
                    {
                        stringWhere = "'" + userName + "'";
                    }
                    else
                    {
                        stringWhere += ",'" + userName + "'";
                    }
                    x += 1;
                }
                query = query.Where(t => (stringWhere.Contains(t.SALESREP_ID)) & !t.ID.StartsWith("99"));
            }

    In the example of what I'm seeing, stringWhere = "'BILREI','BILSHU','BRISMI','BRUNIT','DAVGRI','DAVQUI','DEBOSB','DENWEI','DONNIK','EDMJAC','EXPETE','GARBAT','JIMMIL','JONGOB','JOSRAL','KENSCH','LORSTE','LUASHO','PYRO-USA','SCOFEL','SCOWHI','TABWIL'"

    So based on my query, I expect that my query would return only results where the tblCustomer.SALESREP_ID property would contain one of those usernames/salesrep_ids above.  What is strange is I am getting customers returned where SALESREP_ID = 'Pyro'.

    Other than these extra 'Pyro' customers the results are what I'd expect.  I'm a bit clueless on the LINQ stuff but cobbled that together with help from people on this forum and elsewhere.

    Can anyone spot a problem with that statement that would cause these 'Pyro' customers to be included in the result set?  I would only expect 'Pyro-USA' customers as that is one of the salesrep_ids in my list.

    Thanks!


    Dustin Dauphin

    Monday, August 20, 2012 7:06 PM

Answers

  • If you want complete equality between the SALESREP_ID and some given user name, then you shouldn't construct a long string and use containment. You can create a list of strings that are the allowable user names and then test for containment on that list, something like:

    IEnumerable<string> userNames = this.DataWorkspace.ApplicationData.AllowedUsers.GetQuery().Execute().Select(user => user.UserName).ToList();
    
    query = query.Where(t => userNames.Contains(t.SALESREP_ID) && !t.Id.StartsWith("99"));


    Justin Anderson, LightSwitch Development Team

    Monday, August 20, 2012 9:14 PM
    Moderator

All replies

  • Update...

    I modified the LINQ to leave out the 'Pyro-USA' userName from the list and I'm not getting the customers where SALESREP_ID = 'Pyro'

    if (userName == "PYRO-USA")
                    {
                        //Do not add to stringWhere;
                    }
                    else
                    {
                        if (x == 0)
                        {
                            stringWhere = "'" + userName + "'";
                        }
                        else
                        {
                            stringWhere += ",'" + userName + "'";
                        }
                    }

    So I guess its the LINQ that isn't doing what I would expect and returning results containing 'Pyro' and not looking specifically for 'Pyro-USA'.  Can someone help me rewrite this LINQ to get the result set I'm after?

    Thanks!


    Dustin Dauphin

    Monday, August 20, 2012 7:30 PM
  • I would expect that if a SALESREP_ID is "Pyro" then it would be returned because the string "PYRO-USA" does definitely contain "Pyro" (albeit, using case-insensitive equality comparison).


    Justin Anderson, LightSwitch Development Team

    Monday, August 20, 2012 9:07 PM
    Moderator
  • If you want complete equality between the SALESREP_ID and some given user name, then you shouldn't construct a long string and use containment. You can create a list of strings that are the allowable user names and then test for containment on that list, something like:

    IEnumerable<string> userNames = this.DataWorkspace.ApplicationData.AllowedUsers.GetQuery().Execute().Select(user => user.UserName).ToList();
    
    query = query.Where(t => userNames.Contains(t.SALESREP_ID) && !t.Id.StartsWith("99"));


    Justin Anderson, LightSwitch Development Team

    Monday, August 20, 2012 9:14 PM
    Moderator
  • That is what its doing, but not what I want.  I only want where SALESREP_ID contains 'PYRO-USA', not just 'Pyro' regardless of case. 

    This is what i really want to accomplish in the LINQ:

    Select * from tblCustomer where SALESREP_ID in ('PYRO-USA','etc.','etc.')

    but what I seem to be getting is something more like:

    Select * from tblCustomer where SALESREP_ID like '%Pyro%'


    Dustin Dauphin


    • Edited by Dr. Dauphin Monday, August 20, 2012 9:17 PM
    Monday, August 20, 2012 9:15 PM
  • See my other reply.

    Justin Anderson, LightSwitch Development Team

    Tuesday, August 21, 2012 1:25 AM
    Moderator
  • Sweet!  Thanks Justin.

    Here is what I used and it seems to work perfectly.

                IEnumerable<string> userNames = this.DataWorkspace.AllowedUsers.AllowedUsers1.GetQuery().Execute().Select(user => user.UserNameShort).ToList();
                query = query.Where(t => (userNames.Contains(t.SALESREP_ID)) & !t.ID.StartsWith("99"));


    Dustin Dauphin

    Tuesday, August 21, 2012 8:39 PM