Linq-to-Objects nested queries in a Linq-to-Entities query - what happens under the covers?

Question

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); ; 

A Linq-to-Entities query is first converted to a Linq expression tree, which is then converted by Object Services to a command tree. And if the Linq-to-Entities query is nested in the Linq-to-Objects query, then this nested query will also be translated into the expression tree.

a) I believe that none of the Linq-to-Objects nested query statements actually execute, but the data provider for a particular database (or possibly object services) knows how to translate the logic of Linq-to-Objects statements into the corresponding SQL statements ?

b) Does the data provider know how to create equivalent SQL statements for only some Linq-to-Objects statements?

c) In the same way, the data provider knows how to create equivalent SQL statements for only some methods other than Linq in the Net Framework class library?

ANSWER MILLS RESPONSE:

1) I am a little confused by your answer. In response to b), you agreed that if the Linq2 Entities Data Provider statement for SQL Server supports a specific Linq-to-Objects statement, it will try to convert it to an equivalent SQL statement and in response to c) , you also agreed that if this the provider supports a specific non-Linq method, it converts it to an equivalent SQL statement (and if it does not support it, it will throw an exception). But for a) you answered exactly the opposite of what you said for c) , so this provider will not try to convert Max to the equivalent Sql operator, but will execute it instead and use the return value in the request?

2) In any case, I know only some Sql, so I can’t be absolutely sure, but reading the Sql request generated for the above code, it seems that the data provider did not actually execute the numbers.Max method, but instead it just somehow I realized that numbers.Max should return the maximum value, and then proceed to include the TSQL build-in MAX function call in the generated Sql request. It also puts all the values ​​contained in the numbers array in an 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 the Linq2Entities provider does not actually execute the Linq and Linq-to-Object methods, but instead creates equivalent SQL statements for some of them (and for others, it throws an exception)?

SECOND EDITING:

Ok, I did what you told me:

For b) I created the Linq-to-Objects extension method:

 public static class TEST_CLASS { public static int Testing<TSource>(this IEnumerable<TSource> source) { Console.WriteLine("Testing Called"); // here I've put a breakpoint return source.Count(); } } List<int> list = new List<int>() {1,2,3,4,5,6 }; var contact = (from c in context.Contacts where c.ContactID == list.Testing() select c).First(); 

When I run the code in debug mode, I immediately get the following exception (thus, the debugger does not go into the testing method before throwing the exception):

System.NotSupportedException: LINQ to Entities does not recognize the 'Int32 TestingInt32' method, and this method cannot be translated into a storage expression.

For c) I created a non-Linq method:

 public class Another_TEST_CLASS { public static int Testing_Again() { Console.WriteLine("Testing_Again called");// here I've put a breakpoint return 1000; } } var contact = (from c in context.Contacts where c.ContactID == Another_TEST_CLASS.Testing_Again() select c).First(); 

When I run the code in debug mode, I immediately get the following exception (therefore, the debugger does not go to the Testing_Again method before throwing the exception):

System.NotSupportedException: LINQ to Entities does not recognize the 'Int32 Testing_Again ()' method, and this method cannot be translated into a store expression. in System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.Default

Thank you in advance

Answer 1

I just tried this in LinqPad and the generated SQL looks like this:

 -- Region Parameters DECLARE @p0 Int = 5 DECLARE @p1 Int = 1 -- EndRegion SELECT [t0].[ContactID], [t0].[Name] FROM [Contacts] AS [t0] WHERE ([t0].[ContactID] = @p0) OR ([t0].[ContactID] = @p1) 

This uses Linq-to-Sql, but I don't think Linq-to-Entities will do anything else; the provider will execute Linq-to-Objects queries and insert the results into the expression tree.

---

Edit

It seems that Linq-to-Entities is generating a query to evaluate numbers.Max() and numbers.FirstOrDefault() on the server. It seems extremely inefficient to do it this way, and it seems like a mistake. I cannot come up with a single scenario in which L2E behavior would be preferable to L2S behavior.

You can force L2S behavior by retrieving the appropriate values ​​outside your request:

 var numbers = new int[] { 1, 2, 3, 4, 5 }; int max = numbers.Max(); int first = numbers.FirstOrDefault(); var contacts = from c in context.Contacts where c.ContactID == max !| c.ContactID == first select c; 

Answer 2

EDIT: My apologies, this answer is for Linq-To-SQL, which has a different data provider for Linq-To-Entities and other semantics.

The data provider is responsible for translating the expression tree into a format that the base store understands, in your case SQL, and it will determine its own rules.

See here a simple Linq-To-LDAP provider implementation

In the case of the Linq-Sql data provider, he knows that he needs a value for the where clause. Based on the expression, he knows which part of the predicate should come from the requested table using the predicate parameter (in your example c). The other side of the equality expression must be either a value that is passed as a parameter or SQL (function, query).

If the result type of the value expression is a known sql type, and it is obtained from the Reference expression of the object (or a method call by reference to the object), the expression is expanded (executed), and the value is passed to the request as a parameter.

If the expression is a reference to a direct method, it will try to match it with an SQL function or throw (i.e. it will call).

  where c.ContactID == Test() 

If the result of the expression is IQueryable, it will continue to convert it to Sql.

a) In your case, the number.Max () IS method is executed, and the return value is used for the request, test it using a special extension method and put a debug gap in it.

b) This is correct if, instead of a locally declared list of numbers, you did the following

 cts = from c in context.Contacts where c.ContactID == context.Numbers.Max() select c; 

It converts this to a subquery of the Numbers table (since the return type was IQueryable). If you used a method that was not supported by the provider, in this case you will receive a calculation. Again, use the special extension method for verification.

c) Fix, for example, the StartsWith strings method that is converted to SQL, for example