EF Core integer field contains substring efficiently

In a SQL Server database table, I have an integer ID column. This is not a primary key, more like a serial number. I don’t have the ability to change the table definition.

I have a requirement that users be able to search for partial IDs. For example, “ID contains ‘801’”.

I can execute the following query directly:

select * from items
where item_num like '%801%'

In EF Core, I have the following:

Items.Where(x => x.ItemNumber.ToString().Contains(idPartString))

Which gets translated to:

WHERE ((CHARINDEX(@__idPart_2, CONVERT(VARCHAR(11), [x].[item_num])) > 0) OR (@__idPart_2= N''))

Is there a better (more performant) way to achieve this without resorting to raw SQL queries?

Answer

I believe you can use Items.Where(x => EF.Functions.Like(x.ItemNumber.ToString(), idPartString);.

There are few methods in DbFunctions class that should leverage SQL operators and functions.

More about how things are translated according to your question in older question(not EF Core, but still seems valid).

UPDATE

Valuable information added in the comment by SouthShoreAK.

Also, using Functions.Like allows you to use wildcards in the middle of the string, which String.Contains does not.

Thanks for sharing!