LINQ could not be translated-CodePudding

I'm getting this error with trying to sort by customer.name or customer.legalname. But if i sort via title, it works

 public async Task<ActionResult<ResultSet<Registry>>> 
        GetRegistries([FromQuery]ListOptions<RegistryFilter> options = null) {

        var query = DbContext.Registries.AsNoTracking()
            query = select new Registry {
                        Id = registry.Id,
                        Alias = registry.Alias,
                        Customer = new Customer {
                            Id = registry.Customer.Id,
                            LegalName = registry.Customer.LegalName,
                            Name = registry.Customer.Name,
                        },
                        Title = registry.Title,
                        Membership = membership.Role
                    };
        }

        if (!string.IsNullOrWhiteSpace(options.Search)) {
            var term = options.Search.Trim().ToLowerInvariant();
            query = query.Where(x => 
                x.Title.ToLower().Contains(term) || 
                x.Customer.LegalName.ToLower().Contains(term) || 
                x.Customer.Name.ToLower().Contains(term));
        }
        return await query.ToResultSetAsync(options);

and i am getting the following error

System.InvalidOperationException: The LINQ expression 'DbSet .LeftJoin( outer: DbSet, inner: d => EF.Property<Nullable>(d, "CustomerId"), outerKeySelector: d0 => EF.Property<Nullable>(d0, "Id"), innerKeySelector: (o, i) => new TransparentIdentifier<DbRegistry, DbCustomer>( Outer = o, Inner = i )) .OrderBy(d => EF.Property<Nullable>(d.Inner, "Id") == null ? null : new Customer{ Id = d.Inner.Id, LegalName = d.Inner.LegalName, Name = d.Inner.Name } .Name)' 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().

CodePudding user response：

Try applying filtering before the projection. Something along this lines:

query = DbContext.Registries.AsNoTracking() 
                    
if (!string.IsNullOrWhiteSpace(options.Search)) {
    var term = options.Search.Trim().ToLowerInvariant();
    query = query.Where(x => 
                x.Title.ToLower().Contains(term) || 
                x.Customer.LegalName.ToLower().Contains(term) || 
                x.Customer.Name.ToLower().Contains(term)); 
}


return await query
    .Select(registry => new Registry 
    {
         Id = registry.Id,
         Alias = registry.Alias,
         Customer = new Customer 
         {
             Id = registry.Customer.Id,
             LegalName = registry.Customer.LegalName,
             Name = registry.Customer.Name,
             Notes = registry.Customer.Notes
         },
         Title = registry.Title,
         Schema = registry.Schema
    })
   .ToResultSetAsync(options);