Using Linq to Entities to Return Records that Match a List of Possible Strings

Question

I have a table of Somethings. Those Somethings have a Name field. A User of my website can enter a forward or backward combination of that name and I need to return the correct something.

Example:

User enters: "name the".

I have a Something.Name of "the name".

I return that Something.

Now, the SQL would look like this:

SELECT * FROM Somethings
Where Name LIKE '%name the%'
OR Name LIKE '%the name%'

As you can see, I need to get all permutations of the string the user enters and then search for each one. I tried doing this in a loop, using Linq to Entities, like so:

var string[] arr = {"name the", "the name"};

foreach (var str in arr)
{
    var matchingSomethings = db.Somethings
                .Where(e => e.Name.IndexOf(str , StringComparison.InvariantCultureIgnoreCase) >= 0);
}

This worked really well, when the user entered two words. But, with five words (120 permutations), it became cumbersome and the query timed out for the user.

I then tried:

var matchingSomethings = db.Somethings
                .Where(e => arr.Contains(e.Name));

You can clearly see the obvious flaw there, being that the a Something that had a Name of "the name of the Something" would not be matched in this query, because that name is not included in the snippet the user entered of "the name".

So how would I write that Linq to Entities query to get all possible Somethings where any permutation of the snippet the user entered is contained in the Name of the Something?

Answer 1

Look for each word individually and return the item that had all matching words. This should do it:

public void MatchSomethings(string input)
{
    var data = new List<something>();
    var wordsInName = input.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
    var match = data.Where(s => wordsInName.All(word => s.Name.Contains(word)));
}