LINQ to SQL with null values

Question

Can anyone help me figure this out?

The below code works fine and gets inside the if statument

foreach (var m in msg)
{
    if (string.IsNullOrEmpty(m.PhoneNumber))
    {
        m.PhoneNumber = (from c in db.Customers
                            where c.CustomerID == m.CustomerID
                            select c.PhoneNumber).Single();
    }
}

However in the below code phoneNumber is never set

foreach (var m in msg.Where(z => (z.PhoneNumber == null || z.PhoneNumber == "")))
{
     m.PhoneNumber = (from c in db.Customers
                      where c.CustomerID == m.CustomerID
                      select c.PhoneNumber).Single();
}

I'm presuming its because the top code actually evaluates the expression whereas the below dosent. If that is the case then how can you check for null on an unevaluated LINQ query?

EDIT Just to stop confusion here is how msg is poplated in both cases

    var msg = from m in db.Messages
              where (m.StatusID == (int)MessageStatus.Submitted && m.MessageBoxTypeID == (int)MessageBoxType.Outbox)
              select m;

Are you using the same code in both cases for creating the msg sequence? (The two snippets seem equivalent to me, unless I’m missing something.) — Douglas
– Douglas, Commented Feb 4, 2012 at 13:50
I suggest setting the Log on your DataContext to Console.Out (or a file) and seeing what the actual queries being generated are. Run those same queries by hand to see what you get. The code looks like it ought to work the same, but obviously something else is going on. — tvanfosson
– tvanfosson, Commented Feb 4, 2012 at 14:28

Douglas · Accepted Answer · 2012-02-04 14:41:03Z

1

I’m somewhat baffled by this one, but I have a wild guess. If the msg sequence is an IQueryable<T> which translates to an SQL query, then the behavior of the two snippets may vary. Suppose you have:

var msg = 
    from m in dataContext.MyTable
    select m;

Your first snippet would cause the entire msg sequence to be enumerated, thereby issuing an unfiltered SELECT…FROM command to the database and fetching all the rows within your table.

foreach (var m in msg)

On the other hand, your second snippet applies a filter to your sequence before it is enumerated. Thus, the command issued to the database is a SELECT…FROM…WHERE.

foreach (var m in msg.Where(z => (z.PhoneNumber == null || z.PhoneNumber == "")))

There are various cases where the behavior of a filter applied in .NET would differ from its translation to Transact-SQL. For one, case-sensitivity. In your case, I assume that the mismatch is caused by entries whose PhoneNumber consists of whitespace, as these may match the empty string in SQL Server.

To test this possibility, check what happens if you change your second snippet to:

foreach (var m in msg.ToList().Where(z => (z.PhoneNumber == null || z.PhoneNumber == "")))

Edit: Your issue might be that your query is being executed again during subsequent access (when you check whether PhoneNumber was set).

If you execute:

foreach (var m in msg.Where(z => (z.PhoneNumber == null || z.PhoneNumber == "")))
{
    m.PhoneNumber = …
}

bool stillHasNulls = msg.Any(z => z.PhoneNumber == null || z.PhoneNumber == "");

You will find that stillHasNulls might still evaluate to true, since your assignment to m.PhoneNumber is being lost when you re-evaluate the msg sequence (in the above case, when you execute msg.Any, which issues an EXISTS command to the database).

For your m.PhoneNumber assignments to be preserved, you need to either persist them to the database (if that’s what you want), or else make sure that you’re accessing the same sequence elements each time. One way to do this would be to pre-populate the sequence as a collection, using ToList.

msg = msg.Where(z => (z.PhoneNumber == null || z.PhoneNumber == "")).ToList();
foreach (var m in msg)
{
    m.PhoneNumber = …
}

In the above code, the filter still gets issued to the database as a SELECT…FROM…WHERE, but the result is evaluated eagerly, and then stored as a list within msg. Any subsequent queries on msg would be evaluated against the pre-populated in-memory collection (which would contain any new values you assign to its elements).

edited Feb 4, 2012 at 14:41

answered Feb 4, 2012 at 14:06

Douglas

55.1k14 gold badges145 silver badges193 bronze badges

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

3 Comments

Tom Squires Over a year ago

The ToList() would evaluate the statment and would work (be the same as the second snippit. I more wanted to know how to check if null without evaluating the expression.

Douglas Over a year ago

How are you checking whether PhoneNumber is set? Are you setting a breakpoint on your assignment statement, or are you checking the values of the elements of msg after the loop?

Douglas Over a year ago

If you just want to check for null as part of the query (without eager evaluation), then your second snippet is correct.

Collectives™ on Stack Overflow

LINQ to SQL with null values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related