none
Is this the best pattern to avoid System.InvalidOperationException: Sequence contains no elements RRS feed

  • Question

  • I often find myself wanting to write code like this:

    var matchingTimeObject = (from to in DataContext.TimeObjects

                                where to.id == timeObjectIdIn

                                select to).First();

     

    But if the select statement returns no matching elements this code throws a System.InvalidOperationException Sequence contains no elements. So instead I write this:

    var matchingTimeObjects = from to in DataContext.TimeObjects

                                where to.id == timeObjectIdIn

                                select to;

    if (matchingTimeObjects.Count() > 0)

    {

        var matchingTimeObject = matchingTimeObjects.First();

    }

     

    Is this the best pattern for what must be a very common task? I’m worried that both the Count() and the First() calls separately cause the LINQ statement to query the database. Is that the case?

    Tuesday, August 23, 2011 7:52 PM

Answers

  • Use .FirstOrDefault() instead of .First(). Then check if (matchingTimeObject == null).

     

    Actually it would be more proper to check "if (matchingTimeObject == default(TimeObject))" but the reality is that default(TimeObject) is null.


    Tuesday, August 23, 2011 8:47 PM
  • Is this the best pattern for what must be a very common task? I’m worried that both the Count() and the First() calls separately cause the LINQ statement to query the database. Is that the case?


    No that's not the case but it is still inefficient because you are probably returning many records unnecessarily.

    The actual type of matchingTimeObjects in the second query will be an IQueryable<TimeObject> - it basically forms the query but doesn't execute it until the first time that you perform an operation on it - such as .Count() or .First().

    So actually matchingTimeObjects.First() is effectively the same as your first statement exept that the query is held is a specific variable rather than the anonymous "(from ... select to)".

    When you use .Count() you are not limiting the underlying SQL. But when you use .First() the SQL will include "TOP 1" - so much less work for the SQL server.

    Tuesday, August 23, 2011 8:55 PM

All replies

  • Use .FirstOrDefault() instead of .First(). Then check if (matchingTimeObject == null).

     

    Actually it would be more proper to check "if (matchingTimeObject == default(TimeObject))" but the reality is that default(TimeObject) is null.


    Tuesday, August 23, 2011 8:47 PM
  • Is this the best pattern for what must be a very common task? I’m worried that both the Count() and the First() calls separately cause the LINQ statement to query the database. Is that the case?


    No that's not the case but it is still inefficient because you are probably returning many records unnecessarily.

    The actual type of matchingTimeObjects in the second query will be an IQueryable<TimeObject> - it basically forms the query but doesn't execute it until the first time that you perform an operation on it - such as .Count() or .First().

    So actually matchingTimeObjects.First() is effectively the same as your first statement exept that the query is held is a specific variable rather than the anonymous "(from ... select to)".

    When you use .Count() you are not limiting the underlying SQL. But when you use .First() the SQL will include "TOP 1" - so much less work for the SQL server.

    Tuesday, August 23, 2011 8:55 PM
  • Wow, two perfect answrs. Thanks John.
    Tuesday, August 23, 2011 10:06 PM