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.

Why not join the newsletter?

Why not join the newsletter?

If you're not a fan of facebook or twitter, sign up to the newsletter and I'll send you a quick update every so often with what's going on and new posts coming out.

You have Successfully Subscribed!