Entity Framework 6 query with Distinct filter

Question

I have a problem. When I run the code below:

var data = context.TableX.Where(w => w.userId == 9999&& w.id == 9999) .Distinct().ToList();

This is the query generated:

SELECT [Extent1].[id] AS [id], [Extent1].[name] AS [name], [Extent1].[companyId] AS [companyId], [Extent1].[userId] AS [userId] FROM [TableX] AS [Extent1] WHERE (9999 = [Extent1].[userId]) AND (9999= [Extent1].[id]) -- Executing at 01/06/2016 17:28:01 -03:00 -- Completed in 271 ms with result: SqlDataReader

I wonder if you can make the "Distinct" to run with the query as follows:

SELECT DISTINCT id, name, companyId AS type FROM TableX WHERE id=9999 AND userId=9999

Thanks.

Is `id` unique? (it normally would be) Because if it is then all results will be distinct with or without the `DISTINCT` keyword. Maybe EF is recognising that and not using it. — Andy Nichols, Jun 02 '16 at 14:54
Do you have multiple records with userid = 9999 and id = 9999 in your table? The Distinct keyword is generally computationally expensive in sql. If you don't have multilpe matching records, linq's version is probably better, and even so I would expect it to perform the distinct client side if there are multiple returns. — Travis, Jun 02 '16 at 14:57
The id is not unique, thats why i need a "DISTINCT" on the query. — Will, Jun 02 '16 at 15:19
Even if the id is not unique EF might think it is if you're using code-first. From https://msdn.microsoft.com/en-gb/data/jj679962.aspx "Code First infers that a property is a primary key if a property on a class is named “ID” (not case sensitive), or the class name followed by "ID". If the type of the primary key property is numeric or GUID it will be configured as an identity column." — Andy Nichols, Jun 02 '16 at 15:36

ocuenca · Accepted Answer · 2016-06-02T18:23:42.750

6

To obtain the query you want you need to call first a Select before call Distinctto get only the columns you need to apply a distinct:

var data = context.TableX.Where(w => w.userId == 9999&& w.id == 9999)
                         .Select(e=>new {e.id, e.name, e.companyId}) 
                         .Distinct()
                         .ToList();

Update 1

I'm pretty sure the first query should work but anyways another solution could be applying a group by:

var data = context.TableX.Where(w => w.userId == 9999&& w.id == 9999)
                         .GroupBy(e=>new {e.id, e.name, e.companyId}) 
                         .Select(g=>new{g.Key.id, g.Key.name, g.Key.companyId})
                         .ToList();

Update 2

Now I tested the first query in LinqPad in another context but using the same idea:

var query=Agencies.Where(a=>a.StatusId==1)
                  .Select(e=>new{e.StateId, e.AgencyName})
                  .Distinct()
                  .Dump();

And this is the sql that was generated:

-- Region Parameters
DECLARE @p0 Int = 1
-- EndRegion
SELECT DISTINCT [t0].[stateId] AS [StateId], [t0].[agencyName] AS [AgencyName]
FROM [Agencies] AS [t0]
WHERE [t0].[statusId] = @p0

As you can see, it should work, I don't really know what is happening in your case.

Repeating the same process to the second query to be executed via LinqPad:

var query=Agencies.Where(a=>a.StatusId==1)
                  .GroupBy(e=>new{e.StateId, e.AgencyName})
                  .Select(g=>new{g.Key.StateId, g.Key.AgencyName})
                  .Dump();

And this is the sql code:

-- Region Parameters

DECLARE @p0 Int = 1
-- EndRegion
SELECT [t0].[stateId] AS [StateId], [t0].[agencyName] AS [AgencyName]
FROM [Agencies] AS [t0]
WHERE [t0].[statusId] = @p0
GROUP BY [t0].[stateId], [t0].[agencyName]

edited Jun 02 '16 at 18:23

answered Jun 02 '16 at 15:08

ocuenca

38,548
11
89
102

See what the code generated: `SELECT [Extent1].[id] AS [id], [Extent1].[name] AS [name], [Extent1].[companyId] AS [companyId] FROM TableX AS [Extent1]` – Will Jun 02 '16 at 15:22
That's odd, also where is the `where`?. really, that's not make sense. Are you sure is the same query? – ocuenca Jun 02 '16 at 15:24
Sorry, i forgot the where clause: `SELECT [Extent1].[id] AS [id], [Extent1].[name] AS [name], [Extent1].[companyId] AS [companyId] FROM TableX AS [Extent1] WHERE (5093 = [Extent1].[userId]) AND (2698 = [Extent1].[id])` – Will Jun 02 '16 at 15:29
Now I found this post, it could be considered as a duplicate, it's exactly the same idea: [post](http://stackoverflow.com/questions/10719928/how-to-use-linq-distinct-with-multiple-fields) – ocuenca Jun 02 '16 at 15:32
The biggest problem is that the distinct is not running. It is returning six exactly the same records, and with distinct in the database returns 1. – Will Jun 02 '16 at 15:33
I don't understand why is ignoring the `Distinct`, it should work. Try with the second query – ocuenca Jun 02 '16 at 15:39
Octavio, i run this code: `var data = context.TabelaX.Where(w => w.userId == 5093 && w.id == 2698).Select(e => new { e.id }).Distinct().ToList();` And the result was: `SELECT [Distinct1].[C1] AS [C1], [Distinct1].[user_id] AS [user_id] FROM ( SELECT DISTINCT [Extent1].[usuarios_id] AS [usuarios_id], 1 AS [C1] FROM TabelaX AS [Extent1] WHERE (5093 = [Extent1].[userId]) AND (2698 = [Extent1].[id]) ) AS [Distinct1]` – Will Jun 02 '16 at 17:58
Maybe is something related with your Linq provider, What data source are your using? I'm using SQL Server. Did you try with the second query? – ocuenca Jun 02 '16 at 18:04
i'm using SqlServer too, i tried the second query but the group by doesn't work, like distinct. – Will Jun 02 '16 at 18:14
I think that i discover, in the class TableX.cs exists a property named by id, so the Entity understand that id is a key and that's why he did not put the distinct on the query.. When i put a field that is not a key on select method and with name different of id works. I'll try to discover how to make entity ignore the id property as Key. – Will Jun 02 '16 at 18:28
I don't think that is the problem, I tested now selecting the id of my entity (as part of the select in the first one and as a column to group in the second one) and they also worked. – ocuenca Jun 02 '16 at 18:36

Entity Framework 6 query with Distinct filter

1 Answers1

Update 1

Update 2

Linked