How to search a text using entity framework after split?

Question

I have a entity model

public class User{
   public string FirstName {get;set;}
   public string LastName {get;set;}
   public string Department {get;set;}
}

So I want to search a text like "john smith" in database using entity framework core 3.1.

I am splitting the text before.

    public async Task<IEnumerable<UserListViewModel>> Search(string search)
    {
        var terms = search.Split(" ");

        var queryable = _context.Users.Where(s => terms.All(m => s.Department.ToLower().Contains(m)) ||
                                                  terms.All(m => s.FirstName.ToLower().Contains(m)) ||
                                                  terms.All(m => s.LastName.ToLower().Contains(m))).AsQueryable();

        ...........
        ...........
        ...........
    }

but does it not work.

So how can I do it?

Answer 1

EF Core 3.x doesn't really support translation of All and Any in most cases, and your code is slightly wrong, I think what you really want is:

var queryable = _context.Users.Where(u => terms.All(m => u.Department.Contains(m) ||
                                                         u.FirstName.Contains(m) ||
                                                         u.LastName.Contains(m)));

Since this can't be translated, you need to reformat it into code that can.

With LINQKit you can use PredicateBuilder to create an extension that will remap the query into a series of && tests for each term:

// searchTerms - IEnumerable<TKey> where all must be in a row's key
// testFne(row,searchTerm) - test one of searchTerms against a row
// dbq.Where(r => searchTerms.All(s => testFne(r,s)))
public static IQueryable<T> WhereAll<T,TKey>(this IQueryable<T> dbq, IEnumerable<TKey> searchTerms, Expression<Func<T, TKey, bool>> testFne) {
    var pred = PredicateBuilder.New<T>();
    foreach (var s in searchTerms)
        pred = pred.And(r => testFne.Invoke(r, s));

    return dbq.Where((Expression<Func<T,bool>>)pred.Expand());
}

which you would use like:

var queryable = _context.Users
                    .WhereAll(terms,
                              (u,m) => u.Department.Contains(m) ||
                                       u.FirstName.Contains(m) ||
                                       u.LastName.Contains(m));

For "john smith", the extension method would create the equivalent of:

var queryable = _context.Users
                    .Where(u => (u.Department.Contains("john") ||
                                 u.FirstName.Contains("john") ||
                                 u.LastName.Contains("john")) &&
                                (u.Department.Contains("smith") ||
                                 u.FirstName.Contains("smith") ||
                                 u.LastName.Contains("smith"))
                           );

Answer 2

I think you don't need terms.All. as "john smith" is a full name all of it is not gonna be found in a first or last name field.

I'm not sure if the following is possible.

var queryable = _context.Users.Where(s => terms.Contains(m => s.Department.ToLower().Contains(m)) &&
                                                  terms.Contains(m => s.FirstName.ToLower().Contains(m)) ||
                                                  terms.All(m => s.LastName.ToLower().Contains(m))).AsQueryable();

Though it's not quite accurate, it'd return "john john" as well this way, but it's rare.

Answer 3

How about the following.

  void Main()
  {
    var users = new List<User>
    {
        new User { FirstName = "John", LastName = "Smith", Department = "Web" },
        new User { FirstName = "Aaliyah", LastName = "Lin", Department = "Warehouse" },
        new User { FirstName = "Cristian", LastName = "Stone", Department = "Cleaning" },
        new User { FirstName = "Kierra", LastName = "Davidson", Department = "Mobile" },
        new User { FirstName = "Lizbeth", LastName = "Gregory", Department = "Web" }
    };
    
    var search = "Lizbeth Gregory";
    var terms = search.ToLower().Split(' ');
    users.Where(s => terms.All(m => s.Department.ToLower().Contains(m)) ||
                                                      (terms.Any(m => s.FirstName.ToLower().Contains(m))) ||
                                                      terms.Any(m => s.LastName.ToLower().Contains(m)))
                                                      .Dump();
}

public class User
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Department { get; set; }
}

Answer 4

An alternative solution would be to aggregate your search terms:

var terms = search
    .Split(' ', StringSplitOptions.RemoveEmptyEntries);
query = terms
    .Aggregate(query, (current, term) =>
         current.Where(x =>
             x.FirstName.Contains(term)
             || x.LastName.Contains(term)
             || x.Department.Contains(term)
         )
    );