c# - Selecting the grouping Key in an Entity Framework query that groups by anonymous type ends up returning one key per grouped object -

i have table

id        name        city    ... more columns ---------------------------------------------- 1         nate        boston  ... 2         john        boston  ... 2         john        boston  ... 3         sam         austin  ... 

(for reasons beyond control, id duplicated in cases)

and have entity framework model setup this, in general working pretty well. having issue while trying unique list.

var result = db.table.groupby(t => new     {         id = t.id,         name = t.name,         city = t.city     }).select(g => g.key) 

problem is, query returns following:

id        name        city     ----------------------------- 1         nate        boston 2         john        boston 2         john        boston  3         sam         austin 

i thought going crazy, fired linqpad, ran same query , got expected results:

id        name        city    ----------------------------- 1         nate        boston 2         john        boston 3         sam         austin 

i realized linqpad connected database linq-to-sql, not using entityframework providers linqpad. when connect linqpad through assembly, using entityframework, same results in real project.

what missing causing entity framework , linq-to-sql return different results same query , how can same results linq-to-sql?

i should point out in linqpad, if remove .select(g => g.key); results displayed in quick view expected (key unique per grouping, , 2, john, boston record has 2 child elements).

---

for reference, generated sql.

this generated sql linq-to-sql:

select [t0].[id], [t0].[name], [t0].[city] [table] [t0] group [t0].[id], [t0].[name], [t0].[city] 

this generated sql entity framework:

select  1 [c1],  [extent1].[id] [id],  [extent1].[name] [name],  [extent1].[city] [city] (select  [table].* -- changed .* because ef code listed column in table explicitly [dbo].[table] [table]) [extent1] 

@gusman lead me solution. entitykey issue got me thinking must entity framework thing having comparison of id going wrong since there duplicates.

i re-wrote query follows, using linq-to-objects , expected results. key here perform .groupby(...) after .tolist() values compared in memory, c# rules used instead of database or entity framework comparison rules.

var result = db.table .select(t => {     id = t.id,     name = t.name,     city = t.city })        // project used columns, reduce data db => web server .tolist() // convert linq-to-entities, linq-to-objects .groupby(t => new {     id = t.id,     name = t.name,     city = t.city }) .select(g => g.key) 

i'm presuming sort of entity framework optimization (that linq-to-sql not do) in case of duplicate ids firing.

since needed enumerate returned data anyway, .tolist() call not bad me. table pretty wide though, performed additional .select(t => new { ... }) reduce number of columns returned database server, since wont benefit lazy loading after .tolist().