Avoid "Nullable object must have a value." in Linq-To-Sql

Question

I have a method query like this:

public IList<BusinessObject> GetBusinessObject(Guid? filterId)
{
    using (var db = new L2SDataContext())
    {
        var result = from bo in db.BusinessObjects
                     where (filterId.HasValue)
                               ? bo.Filter == filterId.value
                               : true
                     orderby bo.Name
                     select SqlModelConverters.ConvertBusinessObject(bo);
        return result.ToList();
    }
}

At runtime, this throws a System.InvalidOperationException: Nullable object must have a value.

Looking at the Debugger, the problem is my Where Clause: Linq To SQL tries to convert the entire thing to SQL, so even if filterId is NULL, it will still try to access filterId.value.

I thought/hoped the C# compiler/CLR would evaluate that where clause as a code block and only send one of the two branches to Linq To SQL, but that's not how it works.

My refactored version works, but is not very elegant:

public IList<BusinessObject> GetBusinessObject(Guid? filterId)
{
    using (var db = new L2SDataContext())
    {
        var temp = from bo in db.BusinessObjects select bo;
        if(filterId.HasValue) temp = temp.Where(t => t.Filter == filterId.Value);
        var result = from t in temp
                     orderby t.Name
                     select SqlModelConverters.ConvertBusinessObject(bo);
        return result.ToList();
    }
}

I know that Lazy-Evaluation will make sure that only one query is really sent, but having that temp object in there isn't that great really.

Mr. TA · Accepted Answer · 2010-12-15 20:24:36Z

16

Did you try:

where filterId == null || t.Filter == filterId

answered Dec 15, 2010 at 20:24

Mr. TA

5,3771 gold badge31 silver badges37 bronze badges

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

1 Comment

Michael Stum Over a year ago

Oh wow, that works perfectly! It saves me from using the slightly unreadable tertiary operator, and it solves the actual Exception which is caused by unnecessarily accessing filterId.Value instead of just filterId.

cdhowie · Accepted Answer · 2010-12-15 20:24:11Z

1

Your fix is exactly correct. You are effectively trying to build up a query dynamically, based on your function input. It's a good idea to omit the where clause instead of supplying WHERE TRUE anyway. If I were writing this query, I would go with your fixed version myself.

It's not as pretty as using the language keywords, but it's still the right way to approach the query in my opinion.

answered Dec 15, 2010 at 20:24

cdhowie

172k25 gold badges303 silver badges324 bronze badges

Collectives™ on Stack Overflow

Avoid "Nullable object must have a value." in Linq-To-Sql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related