CODEWITHSUNDEEP
entity-frameworkentity-framework-6linq-to-entities
Eric
Eric

Reputation: 2390

Entity Framework 6 Ignoring Distinct in SQL Query when Selecting Multiple Columns

I am not seeing the expected SQL generated with regard to Distinct when selecting multiple columns. In particular the Distinct is being left out of the generated SQL. Strangely, this seems to work properly when my Select clause only has 1 column. How do I achieve a Distinct SQL query using Entity Framework when selecting multiple columns? Group By also does not work when the key is multiple columns.

In these examples, "e" is my System.Data.Entity.DbContext.

Query that selects multiple columns ignores distinct:

var distinct1 = e.MyViews
                 .Select(a => new { a.Col1, a.Col2, a.Col3, })
                 .Distinct()
                 .ToList();

Generated SQL is missing Distinct:

SELECT 
    [Extent1].[Col1] AS [Col1], 
    [Extent1].[Col2] AS [Col2], 
    [Extent1].[Col3] AS [Col3]
FROM 
    (SELECT 
         [MyView].[Col1] AS [Col1], 
         [MyView].[Col2] AS [Col2], 
         [MyView].[Col3] AS [Col3], 
         [MyView].[UserId] AS [UserId]
     FROM 
         [MySchema].[MyView] AS [MyView]) AS [Extent1]

Query that selects single column uses Distinct:

var distinct2 = e.MyViews
                 .Select(a => new { a.Col1, })
                 .Distinct()
                 .ToList();

The generated SQL correctly includes Distinct:

SELECT 
    [Distinct1].[C1] AS [C1], 
    [Distinct1].[Col1] AS [Col1]
FROM 
    (SELECT DISTINCT 
         [Extent1].[Col1] AS [Col1], 
         1 AS [C1]
     FROM 
         (SELECT 
              [MyView].[Col1] AS [Col1], 
              [MyView].[Col2] AS [Col2], 
              [MyView].[Col3] AS [Col3], 
              [MyView].[UserId] AS [UserId]
          FROM 
              [MySchema].[MyView] AS [MyView]) AS [Extent1]) AS [Distinct1]

Note that this is running on .Net Framework 4.8 and is the original EF 6, not EF Core.

Running against SQL Server (Azure SQL Database Managed Instance).

I have confirmed that EF Core correctly injects Distinct in the generated SQL with multiple columns in the Select, but that doesn't really help me with my .Net Framework project.

Update

I am now finding that some combinations of columns in the Select clause do work, while others do not, but I cannot determine what the differentiating factor is.

These combinations of columns in Select do produce Distinct in SQL:
Col1
Col2
Col3
Col1, Col2
Col2, Col3

These combinations of columns in Select do not produce Distinct in SQL:
Col1, Col3
Col1, Col2, Col3

Upvotes: 1

Views: 78

Answers (1)

Roberto Russo
Roberto Russo

Reputation: 853

I had the same problem Wwe changed the view where we were executing the distinct from EF, making the nullable colums not nullable. This made the new non nullable colums "primary keys" of the view, making the distinct working. Our idea is that since EF thought that the columns we were selecting were the all primay keys, wasn't applying the distinct correctly.

Upvotes: 0

Related Questions