LINQ to SQL — Beware of optimizations (orderby clause)

I find it very helpful to be familiar with the actual SQL that is generated by your LINQ to SQL queries. You’ll find all sorts of performance (and other) quirks and oddities.  And, many times, those quirks and oddities can result in bugs.  One such quirk that I’ve seen happen is with orderby clauses.  Though you might not run into this very often, the idea is that you need to look into all of the SQL generated by your queries, because you never know just what might happen under the covers (enter LINQPad, as usual).

(This is an arbitrary example, but I’ve extracted it from a real instance I encountered in the wild.)

Let’s just say you need to select employees, conditionally ordered by employee id, except if they are not salaried. If they are not salaried, you want those employees to come first, ordered by Last Name.  And, if ordering is not by employee id, you want everything ordered by Last Name. 

The following appears to work:

bool orderByEmployeeId = true;

var contacts = (from c in Contacts
				join e in Employees on c.ContactID equals e.ContactID

				orderby

				(orderByEmployeeId && e.SalariedFlag ? e.EmployeeID : 0),
				c.LastName

				select new
				{
					c.FirstName,
					c.LastName,
					c.EmailAddress,
					c.ModifiedDate
				}).ToList();

It generates the following SQL (but notice that the orderByEmployeeId parameter has been optimized out).  It’s true in the above case, so the generated SQL just checks the SalariedFlag.

-- Region Parameters
DECLARE @p0 Int = 0
-- EndRegion
SELECT [t0].[FirstName], [t0].[LastName], [t0].[EmailAddress], [t0].[ModifiedDate]
FROM [Person].[Contact] AS [t0]
INNER JOIN [HumanResources].[Employee] AS [t1] ON [t0].[ContactID] = [t1].[ContactID]
ORDER BY
    (CASE
        WHEN [t1].[SalariedFlag] = 1 THEN [t1].[EmployeeID]
        ELSE @p0
     END), [t0].[LastName]

That should make you curious to see what happens when the orderByEmployeeId is false:

bool orderByEmployeeId = false;

var contacts = (from c in Contacts
				join e in Employees on c.ContactID equals e.ContactID

				orderby

				(orderByEmployeeId && e.SalariedFlag ? e.EmployeeID : 0),
				c.LastName

				select new
				{
					c.FirstName,
					c.LastName,
					c.EmailAddress,
					c.ModifiedDate
				}).ToList();

Now, the entire case is known to be false. So, the SQL ends up just generating a false-evaluating clause 0 = 1.

-- Region Parameters
DECLARE @p0 Int = 0
-- EndRegion
SELECT [t0].[FirstName], [t0].[LastName], [t0].[EmailAddress], [t0].[ModifiedDate]
FROM [Person].[Contact] AS [t0]
INNER JOIN [HumanResources].[Employee] AS [t1] ON [t0].[ContactID] = [t1].[ContactID]
ORDER BY
    (CASE
        WHEN 0 = 1 THEN [t1].[EmployeeID]
        ELSE @p0
     END), [t0].[LastName]

But, that’s invalid SQL syntax for the conditional sorting we’re trying to accomplish:

The SELECT item identified by the ORDER BY number 1 contains
a variable as part of the expression identifying a column position. 

Variables are only allowed when ordering by an expression referencing a column name.

So, the original LINQ query isn’t going to work in that form. You’d have to restructure the query or move it to a stored procedure in order to get it to work.

Thorough review of the SQL generated by your LINQ queries (and unit testing) will catch this error before it goes live.

Happy querying.

Page 1 of 712345...Last »
Rss Feed