Why is this linq extension method hit the database twice?

Question

Hi folks,

I have an extension method called ToListIfNotNullOrEmpty(), which is hitting the DB twice, instead of once. The first time it returns one result, the second time it returns all the correct results.

I'm pretty sure the first time it hits the database, is when the .Any() method is getting called.

here's the code.

public static IList<T> ToListIfNotNullOrEmpty<T>(this IEnumerable<T> value)
{
    if (value.IsNullOrEmpty())
    {
        return null;
    }
    if (value is IList<T>)
    {
        return (value as IList<T>);
    }
    return new List<T>(value);
}

public static bool IsNullOrEmpty<T>(this IEnumerable<T> value)
{
    if (value != null)
    {
        return !value.Any();
    }
    return true;
}

I'm hoping to refactor it so that, before the .Any() method is called, it actually enumerates through the entire list.

If i do the following, only one DB call is made, because the list is already enumerated.

var pewPew = (from x in whatever
              select x)
             .ToList()   // This enumerates.
             .ToListIsNotNullOrEmpty();  // This checks the enumerated result.

I sorta don't really want to call ToList() then my extension method. Any ideas, folks?

Answer 1

You could stick the .ToList() call inside the extension, the effect is slightly different, but does this still work in the cases you have?

public static IList<T> ToListIfNotNullOrEmpty<T>(this IEnumerable<T> value)
{
    if(value == null)
    {
        return null;
    }
    var result = value.ToList();        
    return result.IsNullOrEmpty() ? null : result;
}

Answer 2

How about something like this?

public static IList<T> ToListIfNotNullOrEmpty<T>(this IEnumerable<T> value)
{
    if (value == null)
        return null;

    var list = value.ToList();
    return (list.Count > 0) ? list : null;
}

Answer 3

I confess that I see little point in this method. Surely if you simply do a ToList(), a check to see if the list is empty suffices as well. It's arguably harder to handle the null result when you expect a list because then you always have to check for null before you iterate over it.

I think that:

 var query = (from ...).ToList();
 if (query.Count == 0) {
     ...
 }

works as well and is less burdensome than

var query = (from ...).ToListIfNotNullOrEmpty();
if (query == null) {
   ...
}

and you don't have to implement (and maintain) any code.

Answer 4

To actually answer your question:

This method hits the database twice because the extension methods provided by the System.Linq.Enumerable class exhibit what is called deferred execution. Essentially, this is to eliminate the need for constructing a string of temporarily cached collections for every part of a query. To understand this, consider the following example:

var firstMaleTom = people
    .Where(p => p.Gender = Gender.Male)
    .Where(p => p.FirstName == "Tom")
    .FirstOrDefault();

Without deferred execution, the above code might require that the entire collection people be enumerated over, populating a temporary buffer array with all the individuals whose Gender is Male. Then it would need to be enumerated over again, populating another buffer array with all of the individuals from the first buffer whose first name is Tom. After all that work, the last part would return the first item from the resulting array.

That's a lot of pointless work. The idea with deferred execution is that the above code really just sets up the firstMaleTom variable with the information it needs to return what's being requested with the minimal amount of work.

Now, there's a flip side to this: in the case of querying a database, deferred execution means that the database gets queried when the return value is evaluated. So, in your IsNullOrEmpty method, when you call Any, the value parameter is actually being evaluated right then and there -- hence a database query. After this, in your ToListIfNotNullOrEmpty method, the line return new List<T>(value) also evaluates the value parameter -- because it's enumerating over the values and adding them to the newly created List<T>.