score:2

Accepted answer

Due to you having lots of rows in your Document table, you are doing a non-sargable scan of the whole table! which is not ideal. Ideally you want to make it sargable so remove the leading or trailing % signs.

e.g. if you remove the leading % then it will be able to use an index on the FirstName column (as long as you create one). It can then seek to matches and just read that data and not the whole table.

You can try something like this in your linq code

YourDocuments.Where(x => EF.Functions.Like(x.FileName, $"{yourSearchString}%")) 

If you want more control consider a stored procedure, but if this does the job then all good :)

If you just created an index on the FileName and kept the %...% syntax, It may use the index as mentioned by others above, it would still be a full scan of that index. The index will be smaller than the clustered index table, hence why SQL Server would use it, because it will do less logical page reads.


Related Articles