Linq Distinct SQL Statementet

Question

I have tried to use Distinct() with some queryable as below peice of code:

var finalWhere = Expression.Lambda<Func<Data.DataModel.TrialSummary, bool>>(containsMethod, parameter);

queryableTrialSummary = queryableTrialSummary.Where(finalWhere).Distinct();

var trials = queryableTrialSummary.Select(x => x.Trial_Code).Distinct();

But I have found that the SQL Statement Generated for the distinct is as below;

SELECT 
    [Distinct1].[Trial_Code] AS [Trial_Code]
    FROM ( SELECT DISTINCT 
        [Extent1].[Trial_Code] AS [Trial_Code]
        FROM [OBAR].[TrialSummary] AS [Extent1]
        WHERE [Extent1].[Trial_MasterProtocolId] IN (N'15NOPEPYT512')
    )  AS [Distinct1]

My Question is: why there are two select statements, one inner and one outer in the above SQL statement.

Did I do something wrong, because I think this should produce something wrong here because I think this should produce SQL statement as below

SELECT DISTINCT 
        [Extent1].[Trial_Code] AS [Trial_Code]
        FROM [OBAR].[TrialSummary] AS [Extent1]
        WHERE [Extent1].[Trial_MasterProtocolId] IN (N'15NOPEPYT512')

Could you please help.

Answer 1

My Question is: why there are two select statements, one inner and one outer in the above SQL statement.

You used two distinct command so the generated sql will containing two distinct as well. Change your linq to:

var trials = queryableTrialSummary.Where(finalWhere).Select(x => x.Trial_Code).Distinct();

You can use linqPad to check you generated SQL.