c# - EntityFramework Group by not included in SQL statement -

i'm trying create query similar this:

select randomid  myview ... group randomid 

note: ef doesn't support distinct thinking of going around lack of group by (or think)

randomid numeric

entity framework v.6.0.2

this gives me expected result in < 1 second query

when trying same ef have been having issues.

if linq similar this:

context.myview .where(...) .groupby(mt => mt.randomid) .select({ id = group.key, count = group.count() } ) 

i sort of same result forcing count , making query > 6 seconds

the sql ef generates this:

select  1 [c1], [groupby1].[k1] [randomid], [groupby1].[a1] [c2] (  select       [extent1].[randomid] [k1],      count(1) [a1]      [dbo].[myview] [extent1]      (...)      group [extent1].[randomid] )  [groupby1] 

but, if query had count commented out < 1 second

if change select like:

.select({ id = group.key} ) 

i of rows without group by statement in sql query , no distinct whatsoever:

select  [extent1].[anotherfield] [anotherfield], -- 'this field got included automatically on query , dont know why, doesnt affect outcome when removed in sql server' [extent1].[randomid] [randomid] [dbo].[myview] [extent1] (...) 

other failed attempts:

query.groupby(x => x.randomid).select(group => group.firstordefault()); 

the query generated follows:

select      [limit1].all fields,...      (select          [extent1].[randomid] [randomid]         [dbo].[myview] [extent1]         (...) [project1]     outer apply  (select top (1)          [extent2].all fields,...         [dbo].[myview] [extent2]         (...) [limit1] -- same above 

this query performed rather poorly , still managed return ids clause.

does have idea on how force usage of group by without aggregating function count?

in sql works again have distinct keyword well...

cheers, j

var query = p in tablename          select new {id = p.columnnameid}; var distinctitems = query.distinct().tolist(); 

here linq query should able write equivalent ef dbset too. if have issues let me know.

cheers!