SQLException : Incorrect syntax near the keyword 'UNION'

[zendo21]

Linq query with Union and OrderBy does not translate correctly.

Simple:

_context.Subjects.Where(s => !(s is Group))
                  .OrderBy(o => o.Created) 
                   .Take(10)
                   .Union(_context.Subjects.Where(s => s is Group)
                                            .OrderBy(o => o.Created)
                                            .Take(10))
                    .ToList();

This will be translate to =>

Select 
Where
OrderBy  -Error ( select must be in subquery)
UNION 
Select
Where
Order by -Error ( select must be in subquery)

Incorrect syntax near the keyword 'UNION'

SELECT TOP(@__p_0) [s].[SubjectId], [s].[Address], [s].[Changed], [s].[Created]
FROM [Subjects] AS [s]
WHERE ([s].[Discriminator] IN (N'PhysicalPerson', N'Company', N'PrivateCompany', N'Group') AND (CASE
    WHEN @__ef_filter__p_0 = CAST(1 AS bit) THEN CAST(1 AS bit)
    ELSE CASE
        WHEN (CAST(1 AS bit) = CAST(0 AS bit)) AND ([s].[IsDeleted] <> CAST(1 AS bit)) THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END
END = CAST(1 AS bit))) AND ([s].[Discriminator] <> N'Group')
ORDER BY [s].[Created]
UNION
SELECT TOP(@__p_0) [s0].[SubjectId], [s0].[Address], [s0].[Changed], [s0].[Created]
FROM [Subjects] AS [s0]
WHERE ([s0].[Discriminator] IN (N'PhysicalPerson', N'Company', N'PrivateCompany', N'Group') AND (CASE
    WHEN @__ef_filter__p_0 = CAST(1 AS bit) THEN CAST(1 AS bit)
    ELSE CASE
        WHEN (CAST(1 AS bit) = CAST(0 AS bit)) AND ([s0].[IsDeleted] <> CAST(1 AS bit)) THEN CAST(1 AS bit)
        ELSE CAST(0 AS bit)
    END
END = CAST(1 AS bit))) AND ([s0].[Discriminator] = N'Group')
ORDER BY [s0].[Created]

EF Core version:
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET Core 3.0)
Operating system:
IDE: (e.g. Visual Studio 2019 16.3)

[roji]

First, as a workaround use client evaluation (since the operation is a UNION there's little difference):

_context.Subjects.Where(s => !(s is Group))
    .OrderBy(o => o.Created) 
    .Take(10)
    .AsEnumerable()
    .Union(_context.Subjects.Where(s => s is Group)
        .OrderBy(o => o.Created)
        .Take(10)
        .AsEnumerable())
    .ToList();

SQL Server and Sqlite don't support ORDER BY inside set operation clauses (PostgreSQL does but requires parentheses around the clause). Sqlite does allow ORDER BY in subqueries so we could push down:

SELECT * FROM (SELECT 1 AS x ORDER BY x LIMIT 1) UNION SELECT 2;

SQL Server doesn't allow ORDER BY in subqueries so there really doesn't seem to be a way to translate this unless I'm missing something.

Note that regardless, Sqlite also doesn't support LIMIT/OFFSET in the clause, SQL Server allows TOP but not LIMIT/OFFSET (because no ORDER BY). PostgreSQL again supports everything as long as the clause is surrounded by parentheses.

[zendo21]

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified

I specified Take(x) that translate to top(x), so i don't want two query, instead of 1 with union on Server

[roji]

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified

ORDER BY isn't supported in set operation clauses regardless of whether TOP is specified or not. For example, the following fails while the same thing without the ORDER BY works:

SELECT TOP(1) 1 AS x ORDER BY x UNION SELECT 2;

[zendo21]

Can you translate to this:
SELECT * FROM (SELECT TOP(1) 1 AS x ORDER BY x) as y UNION SELECT 2; // SQL Server
SELECT * FROM (SELECT 1 AS x ORDER BY x LIMIT 1) as y UNION SELECT 2; //Sqlite ,PostgreSQL

[roji]

Apologies, I mistested the above - in SQL Server ORDER BY is of course OK in subqueries as long as TOP is present as well. So it's indeed possible to translate this by pushing down to a subquery in the SQL Server and Sqlite case (again, in the PostgreSQL case an actual subquery isn't necessary, only parentheses).

[roji]

Test:

[ConditionalTheory]
[MemberData(nameof(IsAsyncData))]
public virtual Task OrderBy_Take_Union(bool isAsync)
{
    return AssertQuery(
        isAsync, ss => ss.Set<Customer>()
            .OrderBy(c => c.ContactName)
            .Take(1)
            .Union(ss.Set<Customer>()
                .OrderBy(c => c.ContactName)
                .Take(1)),
        entryCount: 1,
        assertOrder: true);
}

[smitpatel]

SELECT TOP(1) [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
FROM [Customers] AS [c]
ORDER BY [c].[ContactName]
UNION
SELECT TOP(1) [c0].[CustomerID], [c0].[Address], [c0].[City], [c0].[CompanyName], [c0].[ContactName], [c0].[ContactTitle], [c0].[Country], [c0].[Fax], [c0].[Phone], [c0].[PostalCode], [c0].[Region]
FROM [Customers] AS [c0]
ORDER BY [c0].[ContactName]

Above is invalid SQL but when you push down the whole thing it's valid.

SELECT *
FROM (
	SELECT TOP(1) [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
	FROM [Customers] AS [c]
	ORDER BY [c].[ContactName]
	UNION
	SELECT TOP(1) [c0].[CustomerID], [c0].[Address], [c0].[City], [c0].[CompanyName], [c0].[ContactName], [c0].[ContactTitle], [c0].[Country], [c0].[Fax], [c0].[Phone], [c0].[PostalCode], [c0].[Region]
	FROM [Customers] AS [c0]
	ORDER BY [c0].[ContactName]
) as t