Nesting Linq-to-Objects query within Linq-to-Entities query –what is happening under the covers?
- by carewithl
var numbers = new int[] { 1, 2, 3, 4, 5 };
var contacts = from c in context.Contacts
where c.ContactID == numbers.Max() | c.ContactID == numbers.FirstOrDefault()
select c;
foreach (var item in contacts) Console.WriteLine(item.ContactID);
Linq-to-Entities query is first translated into Linq expression tree, which is then converted by Object Services into command tree. And if Linq-to-Entities query nests Linq-to-Objects query, then this nested query also gets translated into an expression tree.
a) I assume none of the operators of the nested Linq-to-Objects query actually get executed, but instead data provider for particular DB (or perhaps Object Services) knows how to transform the logic of Linq-to-Objects operators into appropriate SQL statements?
b) Data provider knows how to create equivalent SQL statements only for some of the Linq-to-Objects operators?
c) Similarly, data provider knows how to create equivalent SQL statements only for some of the non-Linq methods in the Net Framework class library?
EDIT:
I know only some Sql so I can't be completely sure, but reading Sql query generated for the above code it seems data provider didn't actually execute numbers.Max method, but instead just somehow figured out that numbers.Max should return the maximum value and then proceed to include in generated Sql query a call to TSQL's build-in MAX function. It also put all the values held by numbers array into a Sql query.
SELECT CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN '0X0X'
ELSE '0X1X'
END AS [C1],
[Extent1].[ContactID] AS [ContactID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Title] AS [Title],
[Extent1].[AddDate] AS [AddDate],
[Extent1].[ModifiedDate] AS [ModifiedDate],
[Extent1].[RowVersion] AS [RowVersion],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[CustomerTypeID]
END AS [C2],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[InitialDate]
END AS [C3],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[PrimaryDesintation]
END AS [C4],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[SecondaryDestination]
END AS [C5],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[PrimaryActivity]
END AS [C6],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[SecondaryActivity]
END AS [C7],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[Notes]
END AS [C8],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[RowVersion]
END AS [C9],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[BirthDate]
END AS [C10],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[HeightInches]
END AS [C11],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[WeightPounds]
END AS [C12],
CASE
WHEN (([Project1].[C1] = 1)
AND ([Project1].[C1] IS NOT NULL)) THEN [Project1].[DietaryRestrictions]
END AS [C13]
FROM [dbo].[Contact] AS [Extent1]
LEFT OUTER JOIN (SELECT [Extent2].[ContactID] AS [ContactID],
[Extent2].[BirthDate] AS [BirthDate],
[Extent2].[HeightInches] AS [HeightInches],
[Extent2].[WeightPounds] AS [WeightPounds],
[Extent2].[DietaryRestrictions] AS [DietaryRestrictions],
[Extent3].[CustomerTypeID] AS [CustomerTypeID],
[Extent3].[InitialDate] AS [InitialDate],
[Extent3].[PrimaryDesintation] AS [PrimaryDesintation],
[Extent3].[SecondaryDestination] AS [SecondaryDestination],
[Extent3].[PrimaryActivity] AS [PrimaryActivity],
[Extent3].[SecondaryActivity] AS [SecondaryActivity],
[Extent3].[Notes] AS [Notes],
[Extent3].[RowVersion] AS [RowVersion],
cast(1 as bit) AS [C1]
FROM [dbo].[ContactPersonalInfo] AS [Extent2]
INNER JOIN [dbo].[Customers] AS [Extent3]
ON [Extent2].[ContactID] = [Extent3].[ContactID]) AS [Project1]
ON [Extent1].[ContactID] = [Project1].[ContactID]
LEFT OUTER JOIN (SELECT TOP (1) [c].[C1] AS [C1]
FROM (SELECT [UnionAll3].[C1] AS [C1]
FROM (SELECT [UnionAll2].[C1] AS [C1]
FROM (SELECT [UnionAll1].[C1] AS [C1]
FROM (SELECT 1 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable1]
UNION ALL
SELECT 2 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable2]) AS [UnionAll1]
UNION ALL
SELECT 3 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable3]) AS [UnionAll2]
UNION ALL
SELECT 4 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable4]) AS [UnionAll3]
UNION ALL
SELECT 5 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable5]) AS [c]) AS [Limit1]
ON 1 = 1
LEFT OUTER JOIN (SELECT TOP (1) [c].[C1] AS [C1]
FROM (SELECT [UnionAll7].[C1] AS [C1]
FROM (SELECT [UnionAll6].[C1] AS [C1]
FROM (SELECT [UnionAll5].[C1] AS [C1]
FROM (SELECT 1 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable6]
UNION ALL
SELECT 2 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable7]) AS [UnionAll5]
UNION ALL
SELECT 3 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable8]) AS [UnionAll6]
UNION ALL
SELECT 4 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable9]) AS [UnionAll7]
UNION ALL
SELECT 5 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable10]) AS [c]) AS [Limit2]
ON 1 = 1
CROSS JOIN (SELECT MAX([UnionAll12].[C1]) AS [A1]
FROM (SELECT [UnionAll11].[C1] AS [C1]
FROM (SELECT [UnionAll10].[C1] AS [C1]
FROM (SELECT [UnionAll9].[C1] AS [C1]
FROM (SELECT 1 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable11]
UNION ALL
SELECT 2 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable12]) AS [UnionAll9]
UNION ALL
SELECT 3 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable13]) AS [UnionAll10]
UNION ALL
SELECT 4 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable14]) AS [UnionAll11]
UNION ALL
SELECT 5 AS [C1]
FROM (SELECT 1 AS X) AS [SingleRowTable15]) AS [UnionAll12]) AS [GroupBy1]
WHERE [Extent1].[ContactID] IN ([GroupBy1].[A1], (CASE
WHEN ([Limit1].[C1] IS NULL) THEN 0
ELSE [Limit2].[C1]
END))
Based on this, is it possible that Linq2Entities provider indeed doesn't execute non-Linq and Linq-to-Object methods, but instead creates equivalent SQL statements for some of them ( and for others it throws an exception )?
Thank you in advance
