GroupBy isn't working correct on IQueryable

Question

I have class :

class Entity
{
    public string Name { get; set; }
    public bool IsMain { get; set; }
}

I'm getting data from DB using GroupBy:

var grouppedEntitiesByName = context.GetEntities<Entity>().GroupBy(en => en.Name);

I have some data in DB:

IsMain = true, Name = "entity"  
IsMain = false, Name = "entity"
IsMain = true, Name = "Entity"

I'm using grouped entities like this:

 foreach (var entity in grouppedEntitiesByName)
    {
        var mainEntity = entity.Single(a => a.IsMain);
    }

Here I'm getting Sequence contains more than one matching element exception. Grouped entities has one key entity and three items:

  IsMain = true, Name = "entity"  
    IsMain = false, Name = "entity"
    IsMain = true, Name = "Entity"

Then I tried this:

var grouppedEntitiesByName = context.GetEntities<Entity>().ToList().GroupBy(en => en.Name);

Here grouped entities has two key: entity and Entity Why group by is working incorrect in IQueryable and how to fix this? (I'm using EF)

grouppedEntitiesByName should have two keys 'entity' and 'Entity'. First item which key is 'entity' should have two entity with IsMain = true and IsMain = false (each name is 'entity'). Second item which key is 'Entity' should have one entity with name 'Entity' and IsMain = true — Dilshod K, Jan 04 '19 at 10:00
Possible duplicate [link](https://stackoverflow.com/questions/3843060/linq-to-entities-case-sensitive-comparison) — Pablo notPicasso, Jan 04 '19 at 10:06
Can it be related to teh DB you are using? The only difference in the name is the capital E, so when it is grouped by DB ignoring case, it is still one group. In the list example it is pulled in memory first and the groupby is done in code. Maybe for testing purpose change name to Entity2.. — Aldert, Jan 04 '19 at 10:18
I'm using SQL Server. I tried execute group by query with SQL Server Management studio, it seems SQL Server doesn't take care of about lower and upper case — Dilshod K, Jan 04 '19 at 10:27

Yennefer · Accepted Answer · 2019-01-04T19:28:34.973

By default SQL Server has a a case insentive collation. EF will issue SQL statement, therefore the grouping result will be affected by your database settings.

If you need to group case sensitive, you need to do it by hand, for example by adding a new column with a case insensitive key, this way you are still using the database. Another option which will partially run in memory would be to use LINQ to perform the grouping with a custom sorter.

score 0 · Answer 2 · answered Jan 04 '19 at 10:56

It's because IEnumerable GroupBy is querying data from in-memory collection while IQueryable GroupBy query from out-memory collection. (source: https://www.c-sharpcorner.com/UploadFile/a20beb/ienumerable-vs-iqueryable-in-linq/)

In you example

IQueryable:

var grouppedEntitiesByName = context.GetEntities<Entity>().GroupBy(en => en.Name);

the statement is translated to sql query - select whatever from Entity group by Name which is a case insensitive group by.

IEunumerable:

var grouppedEntitiesByName = context.GetEntities<Entity>().ToList().GroupBy(en => en.Name);

The ToList() part loads the data in to memory and your GroupBy now is IEnumerable.GroupBy which is case sensitive.

GroupBy isn't working correct on IQueryable

2 Answers2