Why do LINQ-to-Entities place this query in a subselect?

Question

I have the following LINQ query:

var queryGroups = (from p in db.cl_contact_event select new Groups { inputFileName = p.input_file_name }).Distinct(); 

What happens during execution:

 SELECT [Distinct1].[C1] AS [C1], [Distinct1].[input_file_name] AS [input_file_name] FROM ( SELECT DISTINCT [Extent1].[input_file_name] AS [input_file_name], 1 AS [C1] FROM [mel].[cl_contact_event] AS [Extent1] ) AS [Distinct1] 

Now I'm sure that the reason is that there is sub-selection, because I have a basic LINQ query surrounded by () and then executed by .Distinct (), but I don't know enough about LINQ to be sure of that. If this is true, is there a way to restructure / encode my request so that there is no sub-selection?

I know that it probably seems like I'm just going here, but I'm just curious.

Answer 1

I suspect that the actual underlying reason for the subquery is the anonymous type constructor. Since you do not select a known object, but rather an arbitrary object constructed from other values ​​of entities, the EF parser must make sure that it can create an exact set of fields - be it from one table, joined tables, calculated fields, another subqueries, etc. The tree expression parser is very good at writing SQL statements from LINQ queries whenever possible, but it is not omniscient. It processes requests systematically, which will always produce the correct results (in the sense that you will get what you requested), although not always the optimal results.

As for rewriting the query in order to exclude the sub-selection, first turn it off: I see no obvious way to do this, which eliminates the anonymous type and gives the correct results. More importantly, I would not bother . Modern SQL servers such as Sybase are very smart - often smarter than the developer - and very well create the optimal query plan from the query. In addition, EF loves subqueries because they are very good ways to automatically write complex queries. You often find them, even if your LINQ query did not appear. Trying to eliminate them all from your queries will quickly turn into a futile exercise.

Answer 2

I would not worry about this particular situation at all. SQL Server (and most likely any corporate database) optimizes the external Select statement anyway. I would suggest that the reason this SQL statement is generated is because it is the most common and reusable statement. In my experience, this always happens on Distinct() .