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

Answer 1

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'.

Answer 2

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.