title: Queryable<T>.Union behaviour in EFCore
body: <p>In order to achieve the equivalent to TSQL <code>UNION</code>:</p>
  
  <pre><code>SELECT Blogid, Url
  FROM Blogs
  UNION (SELECT Blogid, Url
          FROM Blogs)
  ORDER BY url
  </code></pre>
  
  <p>I've tried to use <code>Queryable.Union&lt;T&gt;</code> (<a href="https://msdn.microsoft.com/en-us/library/bb156049(v=vs.110).aspx" rel="noreferrer">doc</a>) and the output to the "end user" is the same, yet when using the <code>SQL Server Profiler</code> I can see <strong>two queries</strong> to the same table.</p>
  
  <p>eg (output from SQL Profiler):</p>
  
  <p>Using EFCore</p>
  
  <pre><code>SELECT [b1].[BlogId], [b1].[Url]
  FROM [Blogs] AS [b1]
  go
  SELECT [b2].[BlogId], [b2].[Url]
  FROM [Blogs] AS [b2]
  go
  </code></pre>
  
  <p>'vs'</p>
  
  <p>Query on Management Studio</p>
  
  <pre><code>SELECT Blogid, Url
  FROM Blogs
  UNION (SELECT Blogid, Url
          FROM Blogs)
  ORDER BY url
  </code></pre>
  
  <p>According to <a href="https://weblogs.asp.net/dixin/entity-framework-core-and-linq-to-entities-4-query-methods" rel="noreferrer">this blog post </a></p>
  
  <blockquote>
    <p>EF translates Union to Union ALL with SELECT DISTINCT, so eventually each result is unique.</p>
  </blockquote>
  
  <p>The Core for the query in the EF is the following:</p>
  
  <pre><code>DbSet&lt;Blog&gt; set = db.Set&lt;Blog&gt;();
  List&lt;Tuple&lt;string,int&gt;&gt; blogs = set.Union(set).OrderBy(blog =&gt; blog.Url).ToList();
  </code></pre>
  
  <p>Notice that <code>Union</code> is not translated, nor is <code>GroupBy</code>.
  Using the <code>GroupBy</code> without <code>Union</code>, makes the query be translated to </p>
  
  <pre><code>SELECT [blog].[Url] , [blog].[BlogId] 
  FROM [Blogs] AS [blog]
  ORDER BY [Url]
  </code></pre>
  
  <p>which is the expected behaviour.
  Why isn't <code>Union</code> being translated correctly?</p>