Entity Framework query sql

Ever tried to build complex joins using Linq with Entity Framework and finally ending up with something you just suspect might be a hell of a lot less efficient than possible? In that moment it might prove useful to get the clean actual SQL your Linq query actually outputs.

Convert linq query to sql

var childNamesQuery = context.ParentSet
.Where(p => p.Name == "Parent name")
.Join(context.ChildSet,
outerKey => outerKey.Id,
innerKey => innerKey.ParentId,
(p, c) => c.ChildName);

var sqlString = ((System.Data.Objects.ObjectQuery) childNamesQuery).ToTraceString();

This give´s us the SQL below:

SELECT
[Extent2].[ChildName] AS [ChildName]
FROM  [dbo].[ParentSet] AS [Extent1]
INNER JOIN [dbo].[ChildSet] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ParentId]
WHERE N'Parent name' = [Extent1].[Name]

Neat huh?

So just to clarify, the IQuerable you will get from the linq used on the context will actually be derived from System.Data.Objects.ObjectQuery on which we can call the ToTraceString which actually give us the derived SQL.