3

I'm trying to create a query similar to this:

select randomId 
from myView
where ...
group by randomId

NOTE: EF doesn't support the distinct so I was thinking of going around the lack of it with the group by (or so I think)

randomId is numeric

Entity Framework V.6.0.2

This gives me the expected result in < 1 second query

When trying to do the same with EF I have been having some issues.

If I do the LINQ similar to this:

context.myView
.Where(...)
.GroupBy(mt => mt.randomId)
.Select({ Id = group.Key, Count = group.Count() } )

I will get sort of the same result but forcing a count and making the query > 6 seconds

The SQL EF generates is something like this:

SELECT 
1 AS [C1],
[GroupBy1].[K1] AS [randomId],
[GroupBy1].[A1] AS [C2]
FROM ( 
SELECT 
     [Extent1].[randomId] AS [K1],
     COUNT(1) AS [A1]
     FROM [dbo].[myView] AS [Extent1]
     WHERE (...)
     GROUP BY [Extent1].[randomId]
)  AS [GroupBy1]

But, if the query had the count commented out it would be back to < 1 second

If I change the Select to be like:

.Select({ Id = group.Key} )

I will get all of rows without the group by statement in the SQL query and no Distinct whatsoever:

SELECT 
[Extent1].[anotherField] AS [anotherField], -- 'this field got included automatically on this query and I dont know why, it doesnt affect outcome when removed in SQL server'
[Extent1].[randomId] AS [randomId]
FROM [dbo].[myView] AS [Extent1]
WHERE (...)

Other failed attempts:

query.GroupBy(x => x.randomId).Select(group => group.FirstOrDefault());

The query that was generated is as follows:

SELECT 
    [Limit1].ALL FIELDS,...
    FROM  (SELECT 
        [Extent1].[randomId] AS [randomId]
        FROM [dbo].[myView] AS [Extent1]
        WHERE (...) AS [Project1]
    OUTER APPLY  (SELECT TOP (1) 
        [Extent2].ALL FIELDS,...
        FROM [dbo].[myView] AS [Extent2]
        WHERE (...) AS [Limit1] -- same as the where above

This query performed rather poorly and still managed to return all Ids for the where clause.

Does anyone have an idea on how to force the usage of the group by without an aggregating function like a count?

In SQL it works but then again I have the distinct keyword as well...

Cheers, J

JSilva
  • 31
  • 2
  • Just verified - if only grouping key is selected, then DISTINCT query is generated with default EF provider for MS SQL – Sergey Berezovskiy Apr 22 '14 at 10:37
  • @SergeyBerezovskiy I have that currently with **query.GroupBy(x => x.randomId).Select(group => new { ID = group.Key }).ToList()** And I get duplicated "randomId" values in the output. Also, glimpse query does not show the *Distinct* nor the *group by* – JSilva Apr 22 '14 at 10:44
  • Maybe you think that values are duplicated? E.g. check white spaces. Also *distinct* should exist in generated query – Sergey Berezovskiy Apr 22 '14 at 10:46
  • Look [here](http://stackoverflow.com/a/8560928/3411327): use Select( new { Id = group.FirstOrDefault()} ) – user3411327 Apr 22 '14 at 10:48
  • The randomId is a numeric column, therefore I cannot have spaces and the query still doesn't have the distinct. Editing post to say the EF version I am using! – JSilva Apr 22 '14 at 10:50
  • @user3411327 that made the query even worse. Will update post with the outcome so everyone is made aware – JSilva Apr 22 '14 at 11:07
  • @JSilva well, I have EF version 6.0.0.0 but I don't think thats the issue. Also including `[anotherField]` in result looks strange to me. Make sure grouping is applied. – Sergey Berezovskiy Apr 22 '14 at 11:29
  • @SergeyBerezovskiy That is why I had to come here with my question. It's getting to bizarre. I might create a sample project at home and try to reproduce it there and post case its also _broken_ – JSilva Apr 22 '14 at 11:45
  • Why do you say there is no `Distinct`? `query.Select(o => o.randomId).Distinct()` should work just fine. – felipe May 06 '14 at 21:33
  • Hello guys, sorry for the delay but here is the problem/answer. While I was only working on the server side of things, the view was re-created and some inner / left joins got changed around. That made the query to be faster but since I wasn't aware, most of the mappings done in code where **WRONG** (required, nullable, etc) and that triggered the EF to not create the query correctly. Now, the fields didn't change but the problem got fixed after reviewing the property mappings and the distinct is now on the query. Didn't investigate any further why EF was working all funny when this happens. – JSilva May 21 '14 at 11:47

1 Answers1

0
var query = from p in TableName 
        select new {Id = p.ColumnNameId};
var distinctItems = query.Distinct().ToList();

Here is the linq query however you should be able to write an equivalent from EF dbset too. If you have issues let me know.

Cheers!

user3660227
  • 21
  • 1
  • I have traced the SQL it generates it is like SELECT DISTINCT [t0].[ColumnNameId] AS [Id] FROM [TableName] AS [t0] – user3660227 May 21 '14 at 13:49