Write WHERE conditions with Enum values using AsQueryable in Entity Framework

Question

Error generated for warning

'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'where Convert([p].Status, Enum).GetDisplayName().Contains(__searchBy_8)' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

I am saving the value of status enum to the database table Complaints

public enum Status
{
    Pending = 0,        
    Error = 1,
    Cancelled = 2,
    Delayed = 3,        
    Resolved = 4
}

I have to apply filtering on a query against the search string along with some other filtering and I am trying to do this in following way

// Apply Relevant SearchModel filters first
var query = context.Complaints
                   .Include(s => s.Messages)
                   .ThenInclude(p => p.User).AsQueryable();
        
if (dtParams.StartDate != null && dtParams.EndDate != null)
{
    query = query.Where(s => s.CreatedAt >= dtParams.StartDate.Value.Date && 
                             s.CreatedAt <= dtParams.EndDate.Value.Date);
}

string searchBy = dtParams.Search?.Value;

if (!string.IsNullOrEmpty(searchBy))
{
    query = query.Where(r => r.ComplaintNo.Contains(searchBy) ||                                                                                      
                             r.CreatorUsername.Contains(searchBy) ||                                                                                      
                             (r.CreatedAt != null &&  r.CreatedAt.ToString().Contains(searchBy)) ||                                                                                                                                    
                             (r.Status.GetDisplayName().Contains(searchBy)) ||
                             r.Messages.Any(p => p.StatusDescription.Contains(searchBy))
                        );
}

// Convert the Db context to Custom ViewModel which will then be rendered on to a DataTable
var dtQuery = query.SelectMany(x => x.Messages, (complaint, message) => new { complaint, message })
                       .Select(p => new ListTableViewModel
                       {
                           ComplaintNo = $"<a href=\"{Url.Action("GetLabels", new { orderNo = p.complaint.ComplaintNo })}\" target=\"_blank\"> {p.complaint.ComplaintNo}</a>",                               
                            
                           Tracking = GenerateTrackingUrl(p.complaint),                               
                           Creator = p.complaint.CreatorUsername,
                           CreatedAt = p.complaint.CreatedAt.ToString("dd/MM/yy"),                               
                           Status = $"<span class=\"badge label-{p.complaint.Status}\">{p.complaint.Status.GetDisplayName()}</span>",
                           Info = p.message.StatusDescription
                       }).ToList(); 
                       

I was getting an exception like below

Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning:
The LINQ expression 'where Convert([p].Status, Enum).GetDisplayName().Contains(__searchBy_8)' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.

The line causing this exception is where I am comparing the search text with value of enum

 (r.Status.GetDisplayName().Contains(searchBy))
 
 

How can i compare display name of enum to the Search string IF someone is searching for text Resolved , i have to fetch all records of Status Resolved from db

Answer 1

This warning occurs because GetDisplayName() is a custom method implemented in your code and cannot be accessed by the database or translated to any sort of sql statement. Therefore EF needs to load all entities from the database and performs filtering in memory (which you should avoid because filtering on the database is much faster).

What you need to to is "Reverse engineer" the term the user searched for to the actual enum value e.g. like so

var filteredStatus = Enum.GetValues<Status>()
                         .Where(value => value.GetDisplayName().Contains(searchBy))
                         .ToList();

and then in your query instead of using r.Status.GetDisplayName().Contains(searchBy) use filteredStatus.Contains(r.Status)