A bit of a context for the problem:

I have an entity (Person) in my database that has a one to many relationship with another (Phone).

    public class Person
    {
        [Key]
        public int Id { get; set; }
        .
        .
        .
        [ForeignKey("PersonId")]
        public virtual List<Phone> Phones { get; set; }
    }

    public class Phone
    {
        [Key]
        public int Id { get; set; }
        .
        .
        .
        public string PhoneNumber { get; set; }

        public int PersonId { get; set; }

        public virtual Person Person { get; set; }
    }

Secondly I'm receiving a filter that includes a list of strings.

The Problem:

I'm trying to get from the DB all the Persons that have at least a PhoneNumber that matches (by like) any of the filter's phone numbers.

So far I've tryed this:

var query = _dbContext.Person
                .Include(x => x.PhoneNumbers)
                .AsNoTracking();
                .Where(x => x.Phones
                             .Any(y => filter.Phones
                                             .Any(z => y.PhoneNumber.Contains(z))));

This query throws an error:

The LINQ expression {my expression here} 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information."

Thanks you!

CodePudding user response：

If you use a standardized format for your recorded phone numbers (I.e. strip all separators and include a standard area code etc.) then you can format your search list of phone numbers to conform to the format and use:

.Where(x => x.Phones.Any(y => filter.Phones.Contains(y.PhoneNumber)));

The issue is that you cannot apply a Linq2Entity Any against the in-memory collection. You can use Contains against the collection but the phone numbers would have to match exactly. (so no inner LIKE type comparison)

If you have a reasonable number of search phone numbers and want to do the LIKE type search across the phone numbers then you can leverage LinqKit's PredicateBuilder to prepare a suitable condition. This requires marking the query as "Expandable" so the dynamic predicate can be built.

var query = _dbContext.Person
    .Include(x => x.PhoneNumbers)
    .AsNoTracking()
    .AsExpandable();

if (filter.Phones.Any())
{
    var predicate = PredicateBuilder.New<Person>();
    foreach(var phone in filter.Phones)
        predicate = predicate.Or(x => x.Phones.Any(p => p.Contains(phone)));
    query = query.Where(predicate);
}