1

I am trying to implement search and I am using entity framework core. I tried using the following query-

var users = await context.Users.Where(u => EF.Functions.FreeText(u.UserName, searchString)).ToListAsync();

This query is throwing the following error -

InvalidOperationException: The 'FreeText' method is not supported because the query has switched to client-evaluation.

Model

public class User
    {
        public int Id { get; set; }
        public string UserName{ get; set; }
    }

Controller

  [HttpGet("search/{searchString}")]
    public async Task<IActionResult> SearchUser(string searchString)
    {
        var users=await repository.Search(searchString);
        return Ok(users);
    }

I even tried switching u.UserName with "UserName" as some answers to similar questions suggested but got the same result. I'd be glad if someone could help me with this.

Nemesis
  • 135
  • 1
  • 1
  • 13
  • the docs say `This can happen if the query contains one or more expressions that could not be translated to the store.` – jazb Nov 27 '19 at 06:59
  • Yeah, although I do not understand why that must be happening. If instead of freetext I just use `u.UserName == "text"` it works properly as an exact search, which means at least that I am referencing the column properly, however I am not sure why the freetext method is not giving the required search query. – Nemesis Nov 27 '19 at 07:37
  • The log gives the error `The LINQ expression 'where __Functions_0.FreeText([a].UserName, "test")' could not be translated and will be evaluated locally.` – Nemesis Nov 27 '19 at 07:38
  • Are you running against SqlServer database? And not against InMemory for instance (for unit testing or something)? – Ivan Stoev Nov 27 '19 at 11:53
  • @IvanStoev Running against SqlLite. – Nemesis Nov 28 '19 at 12:04
  • @Nemesis That should explain the exception, because `FreeText` is SqlServer db provider specific functions extension, hence is not recognized (translated) by other db providers. – Ivan Stoev Nov 28 '19 at 12:06
  • @IvanStoev Thanks for the information. That's a bit disappointing. I guess then there's no general EF method to implement full search? Since while changing providers for deployment doesn't require changing any other methods. – Nemesis Nov 28 '19 at 13:08
  • @Nemesis Unfortunately. I also find strange the decision to allow db providers extending `EF.Functions` with their specific methods, which is against the "db agnostic" idea of ORM, but it is what it is. – Ivan Stoev Nov 28 '19 at 13:13

0 Answers0