C#•15mo ago

❔ EF Core DistinctBy alternative

Hey guys, is there like no way to use a DistinctBy alternative with EF Core? I tried this:

public static IQueryable<TSource> DistinctByDb<TSource, TKey>(
            this IQueryable<TSource> source,
            Expression<Func<TSource, TKey>> keySelector
        ) => source.GroupBy(keySelector).Select(x => x.First());

public static IQueryable<TSource> DistinctByDb<TSource, TKey>(
            this IQueryable<TSource> source,
            Expression<Func<TSource, TKey>> keySelector
        ) => source.GroupBy(keySelector).Select(x => x.First());

But this throws an error:

using var db = _dbContextFactory.CreateDbContext();
        _mostCommonVehicles = await db.Vehicles.DistinctByDb(x => x.Name).Take(3).AsNoTracking().ToListAsync();

using var db = _dbContextFactory.CreateDbContext();
        _mostCommonVehicles = await db.Vehicles.DistinctByDb(x => x.Name).Take(3).AsNoTracking().ToListAsync();

System.AggregateException: One or more errors occurred. (The LINQ expression 'ROW_NUMBER() OVER(PARTITION BY v0.Name ORDER BY v0.Id ASC)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.)

System.AggregateException: One or more errors occurred. (The LINQ expression 'ROW_NUMBER() OVER(PARTITION BY v0.Name ORDER BY v0.Id ASC)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.)

19 Replies

Angius•15mo ago

What error?

ReseOP•15mo ago

the one from below

System.AggregateException: One or more errors occurred. (The LINQ expression 'ROW_NUMBER() OVER(PARTITION BY v0.Name ORDER BY v0.Id ASC)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.)

System.AggregateException: One or more errors occurred. (The LINQ expression 'ROW_NUMBER() OVER(PARTITION BY v0.Name ORDER BY v0.Id ASC)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.)

Angius•15mo ago

Ah, whoops, missed it somehow

ReseOP•15mo ago

no worries 😄 I extracted the GroupBy().Select(x => x.First()) to my actual code for testing - obviously same issue could it be something to do with the object/property itself?

Angius•15mo ago

Unless that Name is a navigation property or some complex object, unlikely No idea, .GroupBy(...).Select(x => x.First()) is the way to do it What db are you using?

ReseOP•15mo ago

MySQL (pomelo)

Angius•15mo ago

Lemme try if it works with Pg

ReseOP•15mo ago

but it's like an older version, 5.6.42

Angius•15mo ago

Maybe it's MySQL issue ah Makes me suspicious that might be it

ReseOP•15mo ago

ah welp sadly the hosting I'm using doesn't wanna upgrade the version oh welp, guess I just gotta stick with client-side eval

Angius•15mo ago

Or change hosting ¯\_(ツ)_/¯ Or use a cloud database, Supabase offers Postgres hosting with a free tier, for example

ReseOP•15mo ago

for most apps I'm using something different, I'm only using this one because of the cheap price coupled with .NET hosting, domain, e-mails and unlimited DBs I'll check supabase out welp, don't know whether 500 MB is enough but yeah, that's outside this question, so thanks for the help anyway

Angius•15mo ago

Well, someone already raised the issue on the EF Core repo: https://github.com/dotnet/efcore/issues/27470 A year ago So give it a year or two more and it might work KEKW

ReseOP•15mo ago

yeah in my search for an alternative to that I did come across that issue as well 😄 but alas

Angius•15mo ago

And it seems it doesn't work with Postgres either

Florian Voß•15mo ago

It seems to me the query works, it just needs to get evaluated by the client from what I understand. Try to add an execution method in the end such as AsEnumerable()

ReseOP•15mo ago

well that's exactly what I'm trying to avoid here

Florian Voß•15mo ago

Okay but it seems EF can’t translate this properly for your db provider so I don’t think that’s it’s possible to avoid. You could ofc use a different db but @ZZZZZZZZZZZZZZZZZZZZZZZZZ said it doesn’t work for his Postgress DB either

Accord•15mo ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ EF Core DistinctBy alternative