LINQ query not returning results when WHERE clause has special character

Question

In my script I am dynamically building a string and then passing the string into the WHERE clause of a LINQ TO ENTITIES statement. When the statement is run it fails to find any results and I can't figure out why. If I explicitly hard code a value into the variable that gets passed into the statement it works fine, but if I let the system build it for me it fails. Why is this failing?

for (int i = 1; i <= TotalUsers; i++)
{
    var CurrentUser = db.Users.Where(u => u.ID == i).Select(u => u.ADUserName).First();
    UserPrincipalData = UserPrincipal.FindByIdentity(Context, CurrentUser);
    var UserDirectoryData = UserPrincipalData.GetUnderlyingObject() as DirectoryEntry;

    var Manager = (string)UserDirectoryData.Properties["manager"].Value;

    if (String.IsNullOrEmpty(Manager))
    {
        Manager = @"Company\Matthew.Verstraete";
    }
    else
    {
        var StartIndex = Manager.IndexOf("=") + 1;
        var EndIndex = Manager.IndexOf(",", StartIndex);
        var Length = EndIndex - StartIndex;
        Manager = Manager.Substring(StartIndex, Length);
        Manager = @"Company\" + Manager.Replace(" ", ".");
    }

    var ManagerID = db.Users.Where(u => u.ADUserName == Manager).Select(u => u.ID).FirstOrDefault();
    var UpdatedUser = db.Users.Find(i);
    UpdatedUser.ManagerID = ManagerID;
    db.SaveChanges();
}

Working via DeBug if the IF statement is true and the variable is hardcoded to me then the ManagerID is set correctly from the query, if it fails and goes to the ELSE clause it will fail even if the Manager variable is dynamically set to me as well.

Edit: The code does not throw any errors in debug. The variable Manager always gets a the proper value in form of Company\\First.Last (C# seems to be escaping the backslash). So I can't figure out why the LINQ query fails when the name is set dynamically but not statically. I need to be able to dynamically set the Manager name and pass it to the query so I can associate an employee to there manager correctly.

I'm sorry but where do you pick up all this bad naming conventions from. `var CurrentUser...` should be `var currentUser`. It makes your example code SUPER hard to read. — Phill, Mar 04 '16 at 17:05
What's the value of `Manager`? Did you try debugging it? Is it possible that there isn't an equal sign in the "manager" property? — juharr, Mar 04 '16 at 17:13
@juharr I have debugged it and no errors are thrown and `Manager` always gets set to a proper value in the format of `Company\\First.Last` (C# seems to be escaping the backslash) — Matthew Verstraete, Mar 04 '16 at 18:04
@MatthewVerstraete When you view a string in the debugger it displays escape sequences. In the case of backslash it will display two. — juharr, Mar 04 '16 at 18:36
@juharr Ok, was not 100% sure if that was the case or C# was doing it. Good to know though, thanks. — Matthew Verstraete, Mar 04 '16 at 18:37
Are you sure the name that is being set dynamically is actually in your DB? — juharr, Mar 04 '16 at 18:40
@juharr Yes, I can go into debug, inspect the value of the variable, copy it into a direct `SELECT` statement ran against the DB directly and the single result I would expect to get. — Matthew Verstraete, Mar 04 '16 at 19:01

score 0 · Accepted Answer · edited May 23 '17 at 12:23

It would help to wrap your code in a try catch block and to give us the exception that was being thrown.

But my guess is that you might be getting an ArgumentOutOfRangeException

If this is the issue, then for your StartIndex or your EndIndex you might be getting -1, which means that the string searched for does not exist.

This would make your Length variable a negative number, and when using substring on a negative value would lead to the exception.

I would step through and see the values of your start and end index.

IndexOf: https://msdn.microsoft.com/en-us/library/7cct0x33(v=vs.110).aspx

Substring: https://msdn.microsoft.com/en-us/library/aka44szs(v=vs.110).aspx

Ok, I think I found the issue.

So you're using the @ sign in your else statement. However, you said that the value is Company\\First.Last. With the @ sign, the string gets considered as literal, so it's searching for "Company\\First.Last"

If you want to search for "Company\First.Last" you should remove the @ sign from the else statement.

I read this post to help me understand the issue: What's the @ in front of a string in C#?

The code runs without errors, I have debugged it and can watch it step through everything and no errors are thrown and `Manager` always gets set to a proper value in the format of `Company\\First.Last` (C# seems to be escaping the backslash). This has nothing to do with the `IndexOf` or `Substring` commands. — Matthew Verstraete, Mar 04 '16 at 18:05
Sorry it has taken a bit to get back to this I was pulled into another project. I just tested this by removing the `@` symbel so now the code reads `Manager = "Company\\" + Manager.Replace(" ", ".");` and it is working now. — Matthew Verstraete, Mar 15 '16 at 13:08

LINQ query not returning results when WHERE clause has special character

1 Answers1