How to identify duplicates in EF Core 3.1

Question

The problem is as follow:

I have updated project to EF Core 3.1 My code to show duplicate email addresses in companies data base stopped working. This code no longer works after update:

cos = cos.Where(x => !string.IsNullOrEmpty(x.Email)).GroupBy(c => c.Email).Where(c => c.Count() > 1).SelectMany(grp => grp);

return View(await cos.ToListAsync());

I thinks this is the problem:

Before EF Core 3.0, this was done implicitly, so EF downloaded all result rows and then applied the LINQ GroupBy. However, this implicit behavior might let the programmer expect that the entire LINQ query is executed in SQL, with potentially enormous performance impact when the result set is rather large. For this reason, implicit client side evaluation of GroupBy was disabled completely in EF Core 3.0.

Do you know how to modify a.m. code to work with EF Core 3.1? Thank you for your help.

Answer 1

You can replace group -> count -> ungroup query operations

.GroupBy(c => c.Email).Where(c => c.Count() > 1).SelectMany(grp => grp)

which can't be translated to SQL currently (and also has no good potential SQL translation in the future) with self subquery counting the "key" matches, e.g.

.Where(c => cos.Count(c1 => c1.Email == c.Email) > 1)