GroupBy Expression failed to translate

Question

//Model
public class Application
{
    [Key]
    public int ApplicationId { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime ConfirmedDate { get; set; }
    public DateTime IssuedDate { get; set; }
    public int? AddedByUserId { get; set; }
    public virtual User AddedByUser { get; set; }
    public int? UpdatedByUserId { get; set; }
    public virtual User UpdatedByuser { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string LastName { get; set; }
    public string TRN { get; set; }
    public string EmailAddress { get; set; }
    public string Address { get; set; }
    public int ParishId { get; set; }
    public Parish Parish { get; set; }
    public int? BranchIssuedId { get; set; }
    public BranchLocation BranchIssued { get; set; }
    public int? BranchReceivedId { get; set; }
    public BranchLocation BranchReceived {get; set; }
}

public async Task<List<Application>> GetApplicationsByNameAsync(string name)
{
    if (string.IsNullOrEmpty(name))
        return null;
    return await _context.Application
        .AsNoTracking()
        .Include(app => app.BranchIssued)
        .Include(app => app.BranchReceived)
        .Include(app => app.Parish)
        .Where(app => app.LastName.ToLower().Contains(name.ToLower()) || app.FirstName.ToLower()
        .Contains(name.ToLower()))
        .GroupBy(app => new { app.TRN, app })
        .Select(x => x.Key.app)
        .ToListAsync()
        .ConfigureAwait(false);
}

The above GroupBy expression fails to compile in VS Studio. My objective is to run a query filtering results by name containing a user given string and then it should group the results by similar TRN numbers returning a list of those applications to return to the view. I think I am really close but just cant seem to figure out this last bit of the query. Any guidance is appreciated.

Error being presented

InvalidOperationException: The LINQ expression 'DbSet<Application>
.Where(a => a.LastName.ToLower().Contains(__ToLower_0) || a.FirstName.ToLower().Contains(__ToLower_0))
.GroupBy(
source: a => new {
TRN = a.TRN,
app = a
},
keySelector: a => a)' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()

UPDATE Seems it is definitely due to a change in how .net core 3.x and EF core play together since recent updates. I had to change it to client evaluation by using AsEnumerable() instead of ToListAsync(). The rest of the query given by Steve py works with this method. I was unaware even after reading docs how the groupby really worked in LINQ, so that has helped me a lot. Taking the query to client side eval may have performance issues however.

Answer 1

The query below works in EF6 but fails in EF Core with the error "The LINQ expression ... could not be translated":

var duplicates = db.Orders
    .GroupBy(x => x.ExternalId)
    .Where(g => g.Count() > 1)
    .ToList();

In fact, EF6 could not translate the whole query to SQL too, but instead, it silently translates to SQL only the first part: db.Orders.GroupBy(x => x.ExternalId), runs it to read everything into memory and then executes the Where(g => g.Count() > 1) filtering there. Of course, this might cause performance and memory issues. Because of this the EF Core simply does not allow such implicit database reading (and offers to do this explicitly in the exception message).

To get the given LINQ fully translated to SQL for EF Core we need to use one extra select and unfortunately don't read all group items. If we still need to know group items we can return all their Ids in one field:

var duplicates = db.Orders
    .GroupBy(x => x.ExternalId)
    .Select(x => new { x.Key, Count = x.Count(), Ids = string.Join(",", x.Select(y => y.Id))})
    .Where(g => g.Count > 1)
    .ToList();

Answer 2

The GroupBy support in EF core is a joke.

This worked perfectly on the server in EF6

var nonUniqueGroups2 = db.Transactions.GroupBy(e => new { e.AccountId, e.OpeningDate })
    .Where(grp => grp.Count() > 1).ToList();

In EF core it causes an exception "Unable to translate the given 'GroupBy' pattern. Call 'AsEnumerable' before 'GroupBy' to evaluate it client-side." The message is misleading, do not call AsEnumerable because this should be handled on the server.

I have found a workaround here. An additional Select will help.

                var nonUniqueGroups = db.Transactions.GroupBy(e => new { e.AccountId, e.OpeningDate })
                    .Select(x => new { x.Key, Count = x.Count() })
                    .Where(x => x.Count > 1)
                    .ToList();

The drawback of the workaround is that the result set does not contain the items in the groups.

There is an EF Core issue. Please vote on it so they actually fix this.

Answer 3

I experience a similar issue where I find it interesting and stupid at the same time. Seems like EF team prohibits doing a WHERE before GROUP BY hence it does not work. I don't understand why you cannot do it but this seems the way it is which is forcing me to implement procedures instead of nicely build code.

LMK if you find a way.

Note: They have group by only when you first group then do where (where on the grouped elements of the complete table => does not make any sense to me)

Answer 4

Based on this:

I want to group by TRN which is a repeating set of numbers eg.12345, in the Application table there may be many records with that same sequence and I only want the very latest row within each set of TRN sequences.

I believe this should satisfy what you are looking for:

return await _context.Application
    .AsNoTracking()
    .Include(app => app.BranchIssued)
    .Include(app => app.BranchReceived)
    .Include(app => app.Parish)
    .Where(app => app.LastName.ToLower().Contains(name.ToLower()) || app.FirstName.ToLower()
    .Contains(name.ToLower()))
    .GroupBy(app => app.TRN)
    .Select(x => x.OrderByDescending(y => y.CreatedAt).First())
    .ToListAsync()
    .ConfigureAwait(false);

The GroupBy expression should represent what you want to group by. In your case, the TRN. From there when we do the select, x represents each "group" which contains the Enumarable set of Applications that fall under each TRN. So we order those by the descending CreatedAt date to select the newest one using First.

Give that a shot. If it's not quite what you're after, consider adding an example set to your question and the desired output vs. what output / error this here produces.