Geek asks and answers

String.Replace in LINQ to Entities

I have an Asp.Net MVC 5 site and I want to search for objects using LINQ. I currently have a job search function. However, I want to add a function that replaces characters in strings before starting the search. This is due to the fact that in Farsi there are two similar representations of the same character, and I want to start searching for them.

Working code is a very simplified version:

var model = db.Restaurants.Where(r => r.Name.ToUpper().Contains(query));

what i want to do is:

query = query.Replace('آ', 'ا'); //couple of other fixes too...
var model = db.Restaurants.Where(r => r.Name.ToUpper().Replace('آ', 'ا').Contains(query));

Obviously this gives me an error:

LINQ to Entities does not recognize the method 'System.String Replace(Char, Char)' method, and this method cannot be translated into a store expression

Currently, the only thing that comes to my mind is to store the replaced rows in the database and query for these rows. In my opinion, this is not a clean approach. Another option is to run the request in code (the request Restaurantone by one), which is inefficient at all. Caching these values ​​will help, but then again, I think there is a better way. So I asked this question to find out if there is a way to pass this request to the database.

+3
source share
1 answer

In Entity Framework 6, you can use command hooks. It sounds complicated, but they made it as light as a pie.

First create a class that implements System.Data.Entity.Infrastructure.Interception.IDbCommandInterceptor. Only one implemented method matters; the rest can just be stubs:

public class MyCommandInterceptor : IDbCommandInterceptor
{
    public void ReaderExecuting(DbCommand command,
                DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        command.CommandText = command.CommandText.Replace("a", "b");
    }

    public void NonQueryExecuting(DbCommand command, 
                DbCommandInterceptionContext<int> interceptionContext)
    { }

    ... lots of stubs
}

And you activate the interceptor, causing

DbInterception.Add(new MyCommandInterceptor());

somewhere in the initialization of your application.

+4
source

More articles:


All Articles