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