EF group list doesn't group

Question

In a table I have a list of documents:

public class Document
{
    public int Id {get;set;}
    public string Slug {get;set;}
    public string Name {get;set;}
    public string LanguageId {get;set;}
}

The table can have multiple documents with the same 'Slug', but with different 'LanguageId's.

I fetch the data like this:

public async Task<List<T>> GetDocuments<T>(string languageId)
{
    var documents = await _dbContext.Documents
        .OrderByDescending(x => x.LanguageId == languageId)
        .ThenByDescending(x => x.LanguageId == "da")
        .ThenBy(x => x.Name)
        .AsNoTracking()
        .ProjectToType<T>()
        .ToListAsync();

    return documents;
}

The goal is to only get one document per 'Slug'. If it exists with the submitted 'languageId', that's what I want - if not, I want the one with LanguageId = "da".

It must be a GroupBy, but I can't get the desired result. My current approach is this:

public async Task<List<T>> GetDocuments<T>(string languageId)
{
    var documents = await _dbContext.Documents
        .OrderByDescending(y => y.LanguageId == languageId)
        .ThenByDescending(y => y.LanguageId == "da")
        .ThenBy(y => y.Name)
        .GroupBy(x => x.Slug)
        .Select(x => x.First())
        .AsNoTracking()
        .ProjectToType<T>()
        .ToListAsync();

    return documents;
}

But that gives me all documents in the table.

Please help.

ProjectToType maps to the type T I provide. Basically just from the domain model to a dto with the same properties. — Mads, Aug 27 '23 at 11:59
https://stackoverflow.com/questions/76987126/mapster-projecttotype-breaks-when-using-groupby-select — Mads, Aug 27 '23 at 12:33

score 1 · Accepted Answer · answered Aug 27 '23 at 11:22

1

Maybe try something like:

public async Task<List<T>> GetDocuments<T>(string languageId)
{
    var documents = await _dbContext.Documents
        .GroupBy(x => x.Slug)
        .Select(x => x
            .OrderByDescending(y => y.LanguageId == languageId)
            .ThenByDescending(y => y.LanguageId == "da")
            .ThenBy(y => y.Name)
            .First())
        .AsNoTracking()
        .ProjectToType<T>()
        .ToListAsync();

    return documents;
}

This way you form the groups way earlier. Then order each group by whatever you want to order it by. Then, select the first one.

answered Aug 27 '23 at 11:22

Özgür Güzeldereli

1,630
7
23

That seems like a good approach, thanks. Sadly it gives me an error: `System.Collections.Generic.KeyNotFoundException: The given key 'EmptyProjectionMember' was not present in the dictionary.`. It's probably not related to the GroupBy but to the mapper/projection. Right? – Mads Aug 27 '23 at 11:27
1

@Mads I am not sure that is due to the Linq but rather about the ProjectToType – Özgür Güzeldereli Aug 27 '23 at 11:56
I think so, but it's strange since it works perfectly without the GroupBy. It's the same properties with and without. – Mads Aug 27 '23 at 12:01
@Mads, remove the projectToType, try the code without it, if it works, try fixing the projection. – Özgür Güzeldereli Aug 27 '23 at 12:03
The query works without the ProjectToType – Mads Aug 27 '23 at 12:09
@Mads, if it does work as expected, I would say you should try to fix the projection error. If you are unable to do so you can ask another question on stackoverflow. Don't continue with this post, most people don't like 2 questions in the same post. Good Luck! – Özgür Güzeldereli Aug 27 '23 at 12:11
Thanks, I'll do that. You were first with a solution to the GroupBy, so I'll give you the points. – Mads Aug 27 '23 at 12:17
@Mads Happy to help – Özgür Güzeldereli Aug 27 '23 at 12:55

jdweng · Answer 2 · 2023-08-27T12:07:12.163

0

Here is complete code. If null doesn't work try DBNull.Value

var documents = await _dbContext.Documents
   .Select(x => new {doc = x, order = (x.LanguageId == null) ? 4 : (x.LanguageId == filter.PreferredLanguageId) ? 1 : (x.LanguageId == "da") ? 2 : 3})
   .OrderBy(x => x.order)
   .GroupBy(x => x.doc.Slug)
   .Select(x => x.First().doc)
   .AsNoTracking()
   .ProjectToType<T>()
   .ToListAsync();

edited Aug 27 '23 at 12:07

answered Aug 27 '23 at 11:43

jdweng

33,250
2
15
20

Thanks. Just tried it but I still get the error `System.Collections.Generic.KeyNotFoundException: The given key 'EmptyProjectionMember' was not present in the dictionary.` – Mads Aug 27 '23 at 11:51
The error has nothing to do with the linq statement. Did you update properties in the class? You need to do a migration to get the c# classes in sync with the database. You database has a primary key EmptyProjectionMember which is not a key in the database. – jdweng Aug 27 '23 at 11:57
The property may be null. Try modified code. – jdweng Aug 27 '23 at 12:01
Thanks, but it didn't fix it. :( – Mads Aug 27 '23 at 12:04
Try DBNull.Value instead of null. – jdweng Aug 27 '23 at 12:07
I don't think that's it. Code works without projection, so I'll have to look there. maybe ask a new question. Thanks anyway! – Mads Aug 27 '23 at 12:19

EF group list doesn't group

2 Answers2