Four years back (wow) I wrote a post about how to show SQL command created by Entity Framework. The information there still holds. But now it’s 2012, Entity Framework progressed. Now you’re probably using DbContext and IDbSet<T> APIs, it’s actually recommended.

There you don’t have the ObjectQuery. As the new API is simpler and more focused, also getting the command is simple. Simply call ToString() method and you’re done.

class Program
{
	static void Main(string[] args)
	{
		Database.SetInitializer<MyContext>(null);
		using (var ctx = new MyContext())
		{
			Console.WriteLine(ctx.FooBars.Where(x => x.Id == -1).ToString());
		}
	}
}

class MyContext : DbContext
{
	public IDbSet<FooBar> FooBars { get; set; }
}

class FooBar
{
	public int Id { get; set; }
	public string Baz { get; set; }
}

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 3^rd 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.