Suppose I have database table named Items that have a Column called Name. Inside Items I have a chair, which name is 'Black wooden chair'.
Because there will be millions of Items, I have created a Full-Text index on Name, to be able to search efficiently. I want a search so that every word of search phrase is contained in the Name. For example:
Searching for 'chair wooden' should return an item with 'Black wooden chair' Name, but 'chair wooden whatever' should not return it. Therefore I use a function called 'Contains' that supports Full-Text search:
var userInput = "chair wooden";
var searchQuery = userInput.Split().Aggregate((s1, s2) => "\"" + s1 + "\"" + " AND " + "\"" + s2 + "\"");
// searchQuery = "chair" AND "wooden"
var result = dbCtx.Items.FirstOrDefault(i => EF.Functions.Contains(i.Name, searchQuery));
This just however calls for an SQL injection if user types input such as 'test" AND test'. Is there any other way that EF Core could handle such query building for me?