Linq to SQL query not returning expected results

Question

Can anyone explain to me why these two are different functionally?

The first returns null on my sample data, and the second returns the expected result. I put the + @"" part in to avoid calling replace on a null string.

Code always returns null on my sample data:

to_follows_contact = db.CONTACTs.Where(n =>
    n.ACCOUNTID == account.ACCOUNTID
    && (
        (n.FIRSTNAME + n.MIDDLENAME + n.LASTNAME + @"").Replace(@" ", @"") 
                                                            == @"TOFOLLOW"
        ||
        (n.FIRSTNAME + n.MIDDLENAME + n.LASTNAME + @"").Replace(@" ", @"") 
                                                            == @"FOLLOWTO"
    )
).FirstOrDefault();

Code returns correct contact on my sample data:

foreach (CONTACT n in db.CONTACTs.Where(n => n.ACCOUNTID == account.ACCOUNTID))
{
    string ContactName = (n.FIRSTNAME + n.MIDDLENAME + n.LASTNAME + @"")
                         .Replace(@" ", @"");
    if (ContactName == @"TOFOLLOW" || ContactName == @"FOLLOWTO")
        to_follows_contact = n;
}

So far, I'm as puzzled with your problem as you are... I just wanted to point out that the @ signs for C# verbatim strings are not necessary in this case. — Thorarin
– Thorarin, Commented Jan 19, 2011 at 8:47
Try taking a look at the generated SQL, might be an issue there. — SWeko
– SWeko, Commented Jan 19, 2011 at 9:00
@Thorarin: I just got into the habit of always using @ in front of strings unless I explicitly wanted escape characters. That way, I don't have to worry about windows paths stored as strings (which I do a lot). — Jonathan
– Jonathan, Commented Jan 19, 2011 at 10:50

Thorarin · Accepted Answer · 2011-01-19 09:14:07Z

5

What's happening, is that the first code sample actually solves the problem entirely in T-SQL. Unfortunately, the translation from C# to SQL isn't entirely transparent.

SQL handles null values differently* from .NET. SELECT 'Foo' + NULL + 'Bar' will return NULL. You will need to use COALESCE or something like it. In LINQ to SQL, that translates to:

(n.FIRSTNAME ?? "" + n.MIDDLENAME ?? "" + n.LASTNAME ?? "").Replace(" ", "")

If you use that, it should work fine.

If you're interested, you can use db.GetCommand(IQueryable<T>) to get the generated SQL statement. It's also available when you hover over the IQueryable variable in a piece of source code while debugging.

*: this acually depends on some database options that you can set, but this is the default behavior.

edited Jan 19, 2011 at 9:14

answered Jan 19, 2011 at 9:06

Thorarin

48.7k12 gold badges77 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonathan Over a year ago

Nice. I haven't come across the ?? operator in C# before.

Thorarin Over a year ago

@Jonathan: it's aptly named the null-coalescing operator: msdn.microsoft.com/en-us/library/ms173224.aspx

Paddy · Accepted Answer · 2011-01-19 09:07:09Z

1

At a guess, the generated SQL will concatenate the various strings, however, if one of them is null, then you'll always get NULL returned from the concatenation, which will fail to pass your equality test.

In the code, the strings will always be empty, which will then work.

answered Jan 19, 2011 at 9:07

Paddy

34k15 gold badges82 silver badges121 bronze badges

Collectives™ on Stack Overflow

Linq to SQL query not returning expected results

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related