Handle Nullable bool in linq query

Question

I have a situation where I am passing nullable bool to the method and then in linq query if that parameter is null then I need to fetch all the records otherwise do comparison and return relative values.

Here is what I have tried (Simplifing the method to just ask relavent question)

public List<Something> Fetch(bool? allocated = null){
   return (from x in DbContext.Something 
            where x.Active && (allocated == null || x.Allocated == allocated.Value)
            select x).ToList();
}

I have also checked for allocated.HasValue but same problem is occuring every time.

The exception which I get is:

System.InvalidOperationException: 'Nullable object must have a value.'

Answer 1

It's not immediately clear to me why this is failing, but I tend to try to simplify the query when I run into problems like this. In particular, the less work the "expression tree to SQL" conversion code has to do, the more likely it is to work.

Given that allocated == null won't change in the course of the query, I'd be tempted to change the code to only conditionally query that part.

public List<Something> Fetch(bool? allocated = null)
{
     var query = DbContext.Something.Where(x => x.Active);
     if (allocated != null)
     {
         // Do this outside the lambda expression, so it's just bool in the expression tree
         bool allocatedValue = allocated.Value;
         query = query.Where(x => x.Allocated == allocatedValue);
     }
     return query.ToList();
}

Answer 2

Voted for Jon Skeet response.

But there are some explanations why that happens, Linq has no problem dealing with nulls.

But LINQ to SQL or Linq to entities (EF) projections will behave differently! Not sure if is a bug or a feature but it can't translate to T-SQL. Google search: linq to entities or linq to sql compare nullable types for answers.

To avoid NULL = 1 comparisons that cannot be translated into T-SQL I usually do this

var allocated = (bool?)null;

That wont work on your method optional parameter defaulted to "null"

Small test to prove the point bellow:

private void MyTest()
{
    var result = FetchListLinq(true);
    result = FetchDbContextLinq(true);
    result = FetchListLinq();
    result = FetchDbContextLinq();
}

private List<object> FetchListLinq(bool? allocated = null)
{
    var myList = new List<dynamic>() { new { Id = 1, Allocated = true, Active = true }, new { Id = 2, Allocated = false, Active = true }, new { Id = 3, Allocated = true, Active = false } };
   return (from x in myList
                   where x.Active && (allocated == null || x.Allocated == allocated.Value)
                   select x).ToList();
}

private List<object> FetchDbContextLinq(bool? allocated = null)
{
    // allocated = allocated ?? (bool?)null; // fix for Linq to SQL or Linq to Entity
    var notWorking = (from x in DbContext.Something
                      where x.Active && (allocated == null || x.Allocated == allocated.Value)
                      select x).ToList();
}