CODEWITHSUNDEEP
c#linq-to-sql
Tom Squires
Tom Squires

Reputation: 9296

LINQ to SQL with null values

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;

Upvotes: 3

Views: 2499

Answers (1)

Douglas
Douglas

Reputation: 54907

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

Upvotes: 1

Related Questions