GroupBy exp. failed to translate

Rewritten Issue

I am trying to run a query that filters results by name containing a user given string and groups the results by similar TRN numbers. I am getting an InvalidOperationException error with the code given below.

public class Application
    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; }

I am using .NET Core 3.x and Entity Framework Core. How can I resolve the error and run the query?


The InvalidOperationException error is due to group by not being supported in EF Core for string manipulations. You can resolve this issue by using a subquery to filter the results before grouping. Replace the LINQ query with the one below.

var results = from app in _context.Applications
              where app.FirstName.Contains(name) || app.MiddleName.Contains(name) || app.LastName.Contains(name)
              group app by app.TRN into trnGroup
              select new {
                  TRN = trnGroup.Key,
                  Applications = trnGroup.Where(app => app.TRN == trnGroup.Key).ToList()

This query filters the results by name before grouping by TRN. A subquery is then used to select all the applications with matching TRN numbers for each group.