Union and Concat in LINQ to Entities
Few years back I wrote about ”UNION and UNION ALL in LINQ to SQL“ and comment there today made me re-think it again. Everything that’s written there is correct. But it might be little bit confusing to see the query being generated from Entity Framework. Actually the query is generated by SqlClient and every 3rd party provider should do the same.
Basically, as written in above mentioned article, Concat generates query, that has the semantic of UNION ALL. Similarly the Union generates query with UNION semantic.
Let’s check that. I’ll use simple Entity Framework’s Code First base set up. And some queries.
class MyContext : DbContext
{
public MyContext()
: base(new SqlConnection(@"Initial Catalog=test;Data Source=(localdb)\mssql;Integrated Security=True;Pooling=false;"), true)
{
}
public IDbSet<Test1> Test1 { get; set; }
public IDbSet<Test2> Test2 { get; set; }
}
class Test1
{
public int Id { get; set; }
public int FooBar { get; set; }
}
class Test2
{
public int Id { get; set; }
public int FooBar { get; set; }
}
using (var ctx = new MyContext())
{
Console.WriteLine(ctx.Test1.Select(x => x.Id).Union(ctx.Test2.Select(x => x.Id)).ToString());
Console.WriteLine(ctx.Test1.Select(x => x.Id).Concat(ctx.Test2.Select(x => x.Id)).ToString());
}
Running this code, produces following queries.
SELECT
[Distinct1].[C1] AS [C1]
FROM ( SELECT DISTINCT
[UnionAll1].[Id] AS [C1]
FROM (SELECT
[Extent1].[Id] AS [Id]
FROM [dbo].[Test1] AS [Extent1]
UNION ALL
SELECT
[Extent2].[Id] AS [Id]
FROM [dbo].[Test2] AS [Extent2]) AS [UnionAll1]
) AS [Distinct1]
SELECT
[UnionAll1].[Id] AS [C1]
FROM (SELECT
[Extent1].[Id] AS [Id]
FROM [dbo].[Test1] AS [Extent1]
UNION ALL
SELECT
[Extent2].[Id] AS [Id]
FROM [dbo].[Test2] AS [Extent2]) AS [UnionAll1]
Both queries are using UNION ALL. What’s wrong? The key magic is in usage of DISTINCT keyword in first query. That basically turns UNION ALL into UNION. Because UNION is all rows from both sets without duplicates. And DISTINCT will remove these duplicates.
Yes, little bit hidden, but the semantic is kept.
