0

I need to perform a query from DB using EF 6, but i would like to filter a string ignoring accents or removing diacritics.

For example: in the DB I have

tblPerson{ Id = 1, Name = "José" }

I maded the next query:

var result = _dbContext.Person
.Where(x => x.Name.Contains(filter))
.OrderByDescending(x => x.Id)
.Take(20)
.ToList();

The query above throw the next exception:

Exception has occurred: CLR/System.InvalidOperationException An exception of type 'System.InvalidOperationException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code: 'The LINQ expression 'DbSet<Contacto>
    .Where(c => (Nullable<int>)c.Deleted == (Nullable<int>)0 && c.Nombres + " " + c.PrimerApellido + " " + c.SegundoApellido
        .RemoveDiacritics().Contains(__busqueda_0))' 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(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
Pac0
  • 21,465
  • 8
  • 65
  • 74
Linx056
  • 11
  • 2
  • that's typically something I would check on the db side first, but we need to know which DBMS you're using. For instance, in MS SQL Server, this is setup by configuring the "collation" of your db. Searching through strings with diactrics is not a trivial problem. – Pac0 Apr 14 '22 at 23:21
  • Also, seems to me that the error message does not correspond to the code you showed. Typically, you're trying to make use of a custom function you made, and EF is complaining that it doesn't know how to translate this function to SQL (which is, again, dependent on the specific DB you're using). Maybe converting this function to an Expression<> instead woudl work, but you've not given enough details to help you debug this. – Pac0 Apr 14 '22 at 23:22
  • Hi, im using MySql and the function that i used is in the next link https://stackoverflow.com/questions/7418624/linq-where-ignore-accentuation-and-case – Linx056 Apr 14 '22 at 23:28
  • I forget write the custom function in the example code. Its name is RemoveDiacritics(). Is an extencion method – Linx056 Apr 14 '22 at 23:37
  • Not sure about your function, but to elaborate on the db configuration, for mysql you can try to dig the `utf8-unicode-ci` collation. Can start by watching this : https://stackoverflow.com/questions/36628283/how-can-i-set-mysqls-default-collation-for-utf8-to-utf8-unicode-ci – Pac0 Apr 14 '22 at 23:41
  • https://dev.mysql.com/doc/refman/8.0/en/full-text-adding-collation.html - https://www.mysqltutorial.org/mysql-collation/ – Pac0 Apr 14 '22 at 23:43
  • ("full text" search / index is also something to consider to significantly boost searching in db text) – Pac0 Apr 14 '22 at 23:43
  • Hi Pac0, i will go to see the info in the link. Later i will let you know. Thank so much – Linx056 Apr 15 '22 at 00:39
  • @Linx056 edit the question and include the *actual* code. You need to set up your database collation correctly though. This isn't an EF Core issue. If you don't, even if you manage to make the query work, it won't be able to use any indexes and scan the entire table. On the other hand, a `Contains` translates to '%abc%` which doesn't use any indexes. If you have 100K rows, your query will have to check all 100K values. If you want to search for words in a text column it would be better to use the database's full-text-search indexes or a separate search engine. – Panagiotis Kanavos Apr 15 '22 at 06:54
  • Check MySQL's [Full Text Search](https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html) functions. If you create a FULLTEXT index over a column you could use the FTS functions with `FromSqlRaw` as a starting point to build a larger LINQ query – Panagiotis Kanavos Apr 15 '22 at 06:58
  • Thank you all very much, I made the mistake of posting my question on the English version of the website and I'm trying to understand you by translating your advice. I also want to correct that I am using mariadb and not mysql server as I said before. – Linx056 Apr 18 '22 at 16:44

1 Answers1

1

I want to thank everyone for the collaboration. I have already solved the problem using a static EntityFramework method which was provided to me by a friend called EF.Functions.Like(). This allows raw queries to be made to the DB.

The Like method returns a boolean.

var result = _dbContext.Person
.Where(x => !x.Deleted && EF.Functions.Like(x.Nombres, $"%{filter}%")
.OrderByDescending(x => x.Id)
.Take(20)
.ToList();

Use this method together with the advice to change the collaction of the DB, since this way it is the one that is in charge of ignoring the accents.

Linx056
  • 11
  • 2