Nesting Linq-to-Objects query within Linq-to-Entities query –what is happening under the covers?

Posted by carewithl on Programmers See other posts from Programmers or by carewithl
Published on 2012-11-05T18:27:13Z Indexed on 2012/11/05 23:18 UTC
Read the original article Hit count: 392

Filed under:
|
   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

© Programmers or respective owner

Related posts about entity-framework

Related posts about LINQ