EF Core integer field contains substring efficiently

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?

dropoutcoder · Accepted Answer · 2019-09-05T19:13:56.277

5

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!

edited Sep 05 '19 at 19:13

answered Sep 04 '19 at 22:11

dropoutcoder

2,627
2
14
32

1

It appears that both `x => EF.Functions.Like(x.ItemNumber.ToString(), idPartString` and `x => x.ItemNumber.ToString().Contains(idPartString)` resolve to an identical SQL execution plan with the same performance. But, this is more explicit, and answers my question. Thank you! – SouthShoreAK Sep 05 '19 at 15:52
3

Also, using `Functions.Like` allows you to use wildcards in the middle of the string, which `String.Contains` does not. – SouthShoreAK Sep 05 '19 at 18:41

score 2 · Answer 2 · answered Sep 04 '19 at 22:16

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

EF Core integer field contains substring efficiently

2 Answers2