StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to write a FirstOrException() extension method compatible with LINQ to SQL (and high performing)?
text
Body
copied!<p>Quite often I find myself writing code like this:</p> <pre><code>Dim oRenewalOrder = (From c in dc.Orders _ Where c.CustomerID = iCustomerID _ And c.Type = "RENEWAL").FirstOrDefault() If oRenewalOrder Is Nothing Then Throw New Exception("Can't find renewal order for customer " & iCustomerID) ' or assert, if you prefer End If ' Continue processing... </code></pre> <p>I would prefer to use the First() method instead, since it already throws an exception when there is no element. But that exception tells you nothing about what caused it, making it harder to debug. So I want to write a FirstOrException() extension method that I can use like this:</p> <pre><code>Dim oRenewalOrder = (From c in dc.Orders _ Where c.CustomerID = iCustomerID _ And c.Type = "RENEWAL").FirstOrException("Can't find renewal order for customer " & iCustomerID) ' Continue processing... </code></pre> <p>The problem is that it's hard to write such a method in a generic way, to work with both LINQ to Objects <em>and</em> take advantage of the query optimizations present LINQ to SQL. The best I can come up with is this:</p> <pre><code> ''' <summary> ''' Returns the first element of a sequence. ''' If the sequence is empty, an InvalidOperationException is thrown with the specified message. <Extension()> _ Function FirstOrException(Of T)(ieThis As IEnumerable(Of T), sMessage As String) As T Try Return ieThis.First() Catch ex As InvalidOperationException Throw New InvalidOperationException(sMessage, ex) End Try End Function </code></pre> <p>I also wrote another equivalent extension method for IEnumerable(Of T), to cover the LINQ to Objects case. These work well, but it seems bad to have to catch an exception and rethrow it. I tried a different approach, using Take(1) and AsEnumerable(), but when I profiled it, it was running two separate SELECT TOP 1 statements:</p> <pre><code> <Extension()> _ Function FirstOrException(Of T)(iqThis As IQueryable(Of T), sMessage As String) As T Dim aFirst = iqThis.Take(1).AsEnumerable() If aFirst.Count() <= 0 Then Throw New InvalidOperationException(sMessage) Else Return aFirst(0) End If End Function </code></pre> <p>So I'm back to the exception handling method. I don't like it because there is a possibility that whatever LINQ provider is underlying the collection will throw an InvalidOperationException when there is a different problem -- not a lack of results, but something else. This would cause my code to mistakenly think there were no results, when in fact it was a different problem entirely.</p> <p>...</p> <p>Well, as so often happens when you type up a detailed question, I think I found a better solution, and I will post it in the answers below. But I'll leave the question open in case anyone finds something better :-)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload