EF Core integer field contains substring efficiently

entity-framework entity-framework-core sql sql-server

Question

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?

1
1
9/4/2019 9:49:21 PM

Accepted 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!

3
9/5/2019 7:13:56 PM

Popular Answer

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.

LIKE



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow