Could anyone explain me the difference in the following 2 queries?

Question

I'm working with SqlMetal (linq to sql) in order to retrieve stuff from the database. However, im getting different results using the following code:

var record1 = Enumerable.FirstOrDefault(_dataContext.StaticPageLangs, 
              r => r.selected_partial_url_id == partialUrlid 
                && r.selected_last_part_url_id == lastPartUrlId 
                && r.language_id == languageId);

var record2 = _dataContext.StaticPageLangs.FirstOrDefault(
              r => r.selected_partial_url_id == partialUrlid
                && r.selected_last_part_url_id == lastPartUrlId
                && r.language_id == languageId);

After this record1 is filled, but record2 is NULL where i expect them to be the same.

Could anyone explain the difference?

Edit:

On a sidenote:

r.selected_partial_url_id is a nullable int and so is the property being compared r.selected_last_part_url_id is of the type int and so is the property being compared r.language_id is of the type int and so is the property being compared

Maarten · Accepted Answer · 2012-07-25T13:30:47.137

5

The first query is done in-memory since you are using Enumerable.FirstOrDefault(...). The second query is translated to SQL by the data-context, and is executed by the database.

You would have to check the sql profiler to see what the actual query is that is executed to see what the difference is. Any nullable columns in your database that could be the problem?

UPDATE

If you compare nullable properties in an expression which gets translated to a SQL statement, be aware this can go wrong if both values of the comparison are null. Example:

_dataContext.StaticPageLangs
    .FirstOrDefault(r => r.selected_partial_url_id == partialUrlid)

will not yield any records if partialUrlid == NULL and a record exists with selected_partial_url_id == NULL. Reason: the translated sql contains 'selected_partial_url_id == NULL' and not 'selected_partial_url_id IS NULL'.

edited Jul 25 '12 at 13:30

answered Jul 25 '12 at 12:31

Maarten

22,527
3
47
68

oh, indeed. Nullables are a huge pain for this. I would go as far as to say that the LINQ-to-SQL provider simply gets this wrong. – Marc Gravell Jul 25 '12 at 12:33
EF also still doesn't handle where-expression '(a nullable property) == (nullable variable)' correctly. – Maarten Jul 25 '12 at 12:37
i edited the data types into my question and there is a nullable int which needs to be compared. I have to look at whats being executed – Robin Jul 25 '12 at 13:23

score 1 · Answer 2 · answered Jul 25 '12 at 12:32

Note: the following answer applies to record1 being null and record2 being not null, which is the opposite of the question, but may be useful to other readers with similar scenarios.

If I had to guess, at least one of those == is on a string, and this is a case-sensitivity issue.

record1 is coming from LINQ-to-Objects, so it is pulling all the rows from the server, and testing them locally. Those == are then case-sensitive (C# / .NET == on string is always case-sensitive).

record2 looks like it is using the DB implementation via IQueryable<>. That means it is being translated to TSQL. A SQL-Server database can be either case-sensitive or case-insensitive. I'm guessing you have it set to case-insensitive.

Now imagine, say, that languageId is "en-us" in the C#, and 'EN-US' in the database.

That could lead to record1 being a null, but record2 being filled.

I think OP is about a difference between `Enumerable.FirstOrDefault(whatever, predicate)` and `whatever.FirstOrDefault(predicate)` — abatishchev, Jul 25 '12 at 12:35
I edited the type of the parameters im comparing and none of them are strings. — Robin, Jul 25 '12 at 13:21
I seem to have updated your answer my mistake (the intention was to update my own answer). Reviewer, please reject my update! — Maarten, Jul 25 '12 at 13:31

Could anyone explain me the difference in the following 2 queries?

2 Answers2