GroupBy exp. failed to translate

Programming
, , ,
1

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.

//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; }
}

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

2

Answer

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.