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?
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).
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!
While the first expression is simpler, it's not likely more expensive. Both require conversion of all the
item_num values to varchar to perform the comparison. The first one simply has an implicit conversion, while the second one is explicit.
If any one of the arguments isn't of character string data type, the SQL Server Database Engine converts it to character string data type, if it's possible.