Queryable<T>.Union behaviour in EFCore

Question

In order to achieve the equivalent to TSQL UNION:

SELECT Blogid, Url
FROM Blogs
UNION (SELECT Blogid, Url
        FROM Blogs)
ORDER BY url

I've tried to use Queryable.Union<T> (doc) and the output to the "end user" is the same, yet when using the SQL Server Profiler I can see two queries to the same table.

eg (output from SQL Profiler):

Using EFCore

SELECT [b1].[BlogId], [b1].[Url]
FROM [Blogs] AS [b1]
go
SELECT [b2].[BlogId], [b2].[Url]
FROM [Blogs] AS [b2]
go

'vs'

Query on Management Studio

SELECT Blogid, Url
FROM Blogs
UNION (SELECT Blogid, Url
        FROM Blogs)
ORDER BY url

According to this blog post

EF translates Union to Union ALL with SELECT DISTINCT, so eventually each result is unique.

The Core for the query in the EF is the following:

DbSet<Blog> set = db.Set<Blog>();
List<Tuple<string,int>> blogs = set.Union(set).OrderBy(blog => blog.Url).ToList();

Notice that Union is not translated, nor is GroupBy. Using the GroupBy without Union, makes the query be translated to

SELECT [blog].[Url] , [blog].[BlogId] 
FROM [Blogs] AS [blog]
ORDER BY [Url]

which is the expected behaviour. Why isn't Union being translated correctly?

Answer 1

The linked article describes this behavior, you just overlooked it.

EF Core supports Union for entity and primitive types locally.

The EMPHASIS lies on the word locally. Many of the EF Core Linq operators such as GroupBy etc. are not yet translated to SQL as of EF Core 2.0. That being said, any group by operation will be performed in memory in EF Core 2.0 or lower.

The translated query in the article is about EF (the old .NET Framework based EntityFramework 6.x). Also please note the MSDN link in your question is for the old EntityFramework (up to 6.x). EF Core is usually on docs.microsoft.com domain.

EF Core 2.1 will improve many of the missing translations. Have a look at the EF Core 2.1 Roadmap and of course at 2.1 Milestone on GitHub to see the improvements in detail.

Also see this issue which covers the server-sided translation of Union/Except/Intersect/Concat Linq Operators.

Its labeled as "punted-for-2.1". So it may come to EF Core 2.1, if the time allows it to be completed in the timeframe before EF Core 2.1 gets released otherwise it will be in one of the next releases.