Selecting from a view in Entity Framework

Question

I was trying to select some columns from a view with the direct way like the below code snippet

var q = new TDSViewConnection();
var trials = q.progressive_filtering_lookup_decoded_v
              .Where(z => z.source == "") 
              .Select(z => z.trial_id);

The SQL statement generated for the above expression is like as below

SELECT 
    [Extent1].[trial_id] AS [trial_id]
FROM 
    (SELECT 
         [progressive_filtering_lookup_decoded_v].[master_protocol_id] AS [master_protocol_id], 
         [progressive_filtering_lookup_decoded_v].[trial_id] AS [trial_id], 
         [progressive_filtering_lookup_decoded_v].[source] AS [source], 
         [progressive_filtering_lookup_decoded_v].[discipline_code] AS [discipline_code], 
         [progressive_filtering_lookup_decoded_v].[crop_team_code] AS [crop_team_code], 
         [progressive_filtering_lookup_decoded_v].[crop_name] AS [crop_name], 
         [progressive_filtering_lookup_decoded_v].[pest_name] AS [pest_name], 
         [progressive_filtering_lookup_decoded_v].[country_code] AS [country_code], 
         [progressive_filtering_lookup_decoded_v].[year] AS [year]
     FROM 
         [dbo].[progressive_filtering_lookup_decoded_v] AS [progressive_filtering_lookup_decoded_v]) AS [Extent1]
WHERE 
    N'' = [Extent1].[source]

The question is why are there two select statements? I think it should be only one statement.

Answer 1

With Entity Framework, you will get a SQL generation under the covers of what your LINQ statement expresses. It appears to me that your LINQ statement is performing it's first select based on the preliminary portion of your code, and then when you once again use the '.Select' keyword, you are essentially performing a sub-query on the result set that you previously retrieved.

In other words: var trials = q.progressive_filtering_lookup_decoded_v.Where(z => z.source == "") Is the initial query, which will return a result set.

Then you are running .Select(z => z.trial_id); which runs a separate query using your first result set as a starting point.

This is why you are seeing two select statements in your generated SQL.