Robin Robin - 1 year ago 61
SQL Question

Linq SQL error with one-to-many relationship and orderby complex expression

I'm using Entity Framework v6.1.3. This Linq statement:

return _context.Postings
.Where(p => p.dateToBeRemoved >= asOf)
.Include(p => p.Region)
.Include(p => p.Positions)
.OrderByDescending(p => p.oldVacancyId ?? int.MaxValue)
.ThenByDescending(p => p.postingNumber);

Produces this SQL:

[Project1].[postingId] AS [postingId],
[Project1].[regionId1] AS [regionId1],
<--- lines removed for brevity -->
[Project1].[C2] AS [C1]
CASE WHEN ([Extent1].[oldVacancyId] IS NULL) THEN 2147483647 ELSE [Extent1].[oldVacancyId] END AS [C1],
[Extent1].[postingId] AS [postingId],
[Extent2].[regionId] AS [regionId1],
<--- lines removed for brevity -->
CASE WHEN ([Extent3].[postingPositionId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
FROM [dbo].[Postings] AS [Extent1]
LEFT OUTER JOIN [dbo].[Regions] AS [Extent2] ON [Extent1].[regionId] = [Extent2].[regionId]
LEFT OUTER JOIN [dbo].[PostingPositions] AS [Extent3] ON [Extent1].[postingId] = [Extent3].[postingId]
WHERE [Extent1].[dateToBeRemoved] >= '2016/6/3'
) AS [Project1]
ORDER BY [Project1].[C1] DESC, [Project1].[postingNumber] DESC, [Project1].[postingId] ASC, [Project1].[regionId1] ASC, [Project1].[C2] ASC

Which throws this error when run:

A column has been specified more than once in the order by list.
Columns in the order by list must be unique.

The issue is with the alias given to Project1.C2 in the top level select. It is aliased to C1 which is an alias for another column in the Project1 sub-query.
Removing either the first or the fifth ordery by field allows the query to run, but sorted incorrectly. Renaming the alias to something else allows the query to run and the sort is correct. Is this a bug in Linq? Any suggestions for how to work around this?


Here is a minimal demonstration of my issue. Given these three tables:

dogId int NOT NULL
dogName varchar NOT NULL
groomerId int NULL

groomerId int NOT NULL
groomerName varchar NOT NULL

fleaId int NOT NULL
fleaName varchar NOT NULL
hostDogId int NOT NULL

A dog may or may not have a groomer and can have 0 to many fleas.

Then a linq query like:

.Include(d => d.Fleas)
.OrderBy(d => d.groomerId.HasValue);

The SQL generated will contain an inner query with 2 complex expressions - one to indicate if the dog has a groomer, the other to indicate if it has any fleas. Both these expressions are given an alias (C1 and C2 respectively). That inner query is wrapped with an outer query which references C2 and gives it the alias C1. Then both C1 and C2 are put into the order by clause, but SQL gets confused about which C1 resulting the error mentioned above.

Answer Source

I couldn't get around this issue in a single linq statement. My work-around for now is to run the query without my order by clauses and output a List. Then in a second statement, apply the order by clauses to that list.

var dogs = _context.Dogs.Include(d => d.Fleas).ToList();
dogs = dogs.OrderBy(d => d.groomerId.HasValue).ToList();