Keep an eye open on what LINQ does

Today I was profiling an application. The timings pointed to a piece of code that executed a query using LINQ. I opened an SQL profiler and saw what queries were performed. Here they are:

SELECT [t3].[value] AS [Key]
FROM (
SELECT (
SELECT [t2].[Role]
FROM (
SELECT TOP (1) [t1].[Role]
FROM [dbo].[Membership] AS [t1]
WHERE ([t1].[UserID] = @p0) AND ([t1].[SocialID] = [t0].[Id])
) AS [t2]
) AS [value], [t0].[Id], [t0].[IsPrivate]
FROM [dbo].[Social] AS [t0]
) AS [t3]
WHERE (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[Membership] AS [t4]
WHERE ([t4].[UserID] = @p1) AND ([t4].[SocialID] = [t3].[Id])
)) AND ((NOT ([t3].[IsPrivate] = 1)) OR (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[Membership] AS [t5]
WHERE ([t5].[UserID] = @p2) AND ([t5].[SocialID] = [t3].[Id])
)))
GROUP BY [t3].[value]

SELECT [t0].[Id], [t0].[SocialName], [t0].[Title], [t0].[Description], [t0].[IsPrivate], [t0].[CreationDate], [t0].[LastModified], [t0].[Logo], [t0].[LogoUrl], [t0].[CustomSidebar], [t0].[AuthenticatedUserRole], [t0].[Activity]
FROM [dbo].[Social] AS [t0]
WHERE (((@x1 IS NULL) AND (((
SELECT [t2].[Role]
FROM (
SELECT TOP (1) [t1].[Role]
FROM [dbo].[Membership] AS [t1]
WHERE ([t1].[UserID] = @p0) AND ([t1].[SocialID] = [t0].[Id])
) AS [t2]
)) IS NULL)) OR ((@x1 IS NOT NULL) AND (((
SELECT [t4].[Role]
FROM (
SELECT TOP (1) [t3].[Role]
FROM [dbo].[Membership] AS [t3]
WHERE ([t3].[UserID] = @p0) AND ([t3].[SocialID] = [t0].[Id])
) AS [t4]
)) IS NOT NULL) AND (@x1 = ((
SELECT [t6].[Role]
FROM (
SELECT TOP (1) [t5].[Role]
FROM [dbo].[Membership] AS [t5]
WHERE ([t5].[UserID] = @p0) AND ([t5].[SocialID] = [t0].[Id])
) AS [t6]
))))) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[Membership] AS [t7]
WHERE ([t7].[UserID] = @p1) AND ([t7].[SocialID] = [t0].[Id])
)) AND ((NOT ([t0].[IsPrivate] = 1)) OR (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[Membership] AS [t8]
WHERE ([t8].[UserID] = @p2) AND ([t8].[SocialID] = [t0].[Id])
)))
ORDER BY [t0].[Title]

It doesn't matter what the LINQ code that generated that query was. It looked "nice". What matters is that I was able to change that LINQ code to do the same thing but using a different expression (instead of using EntitySets in the query, I used context.GetTable<...> and made the joins myself). Here's the resulting query:

SELECT [t0].[Id], [t0].[SocialName], [t0].[Title], [t0].[Description], [t0].[IsPrivate], [t0].[CreationDate], [t0].[LastModified], [t0].[Logo], [t0].[LogoUrl], [t0].[CustomSidebar], [t0].[AuthenticatedUserRole], [t0].[Activity], [t1].[Role]
FROM [dbo].[Social] AS [t0], [dbo].[Membership] AS [t1]
WHERE ([t0].[Id] = [t1].[SocialID]) AND ([t1].[UserID] = @p0)

The timings were lower than with the previous queries.

So my recomendation is that you keep an eye open (preferably an SQL profiler open) to see how LINQ translates are queries.