.ToString() and EF LINQ to Entities/SQL Server

While working on a project recently, I had the requirement to make a numeric field searchable with only a part of the number, e.g., having the number 123456 and searching for 34 should return it. Since the number field in the database is an actual number, it must be converted to a string value before we can search it.

You would be tempted to do this in EF:

var result = await _dbContext.MyRecords.Where(x => x.NumberField.ToString().Contains("34")).ToListAsync();

And as we all do like responsible developers, we write a unit test around this statement and use the InMemory Provider for EF. Running your tests will mark them as passed since the InMemory Provider has no issue with this.

Next, you deploy the new code and integrate it into your application. But then it does not work. You are not running the InMemory Provider anymore but are working against an actual SQL database.

The issue is that EF with SQL Server Provider does not know how to translate your above statement to an actual SQL statement.

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

The solution is to rewrite the LINQ statement in a way that can be translated:

var result = await _dbContext.MyRecords.Where(x => Convert.ToString(x.NumberField).Contains("34")).ToListAsync();

Notice the Convert.ToString() here as this is what makes it possible for LINQ to Entities to create a SQL statement that will use the CONVERT()SQL function.

If you want a list of which LINQ statements are translated into SQL statements, have a look at this website: https://learn.microsoft.com/en-us/ef/core/providers/sql-server/functions

As you can see, there are various ways to use SQL server built-in functions this way.