5
\$\begingroup\$

First, I'm adding words entered into a search field to an array. Then, I'm getting results from a stored procedure on SQL Server using Database First approach with Entity Framework.

Finally, for each word in the array, I'm filtering the results using this query:

products = products.Where(item => (item.basedescription.ToLower().StartsWith(searchWords[x] + " ")
                     || item.basedescription.ToLower().StartsWith(service.Pluralize(searchWords[x]) + " ")
                     || item.basedescription.ToLower().StartsWith(service.Singularize(searchWords[x]) + " ")
                     || item.basedescription.ToLower().EndsWith(" " + searchWords[x])
                     || item.basedescription.ToLower().EndsWith(" " + service.Pluralize(searchWords[x]))
                     || item.basedescription.ToLower().EndsWith(" " + service.Singularize(searchWords[x]))
                     || item.basedescription.ToLower().Contains(" " + searchWords[x] + " ")
                     || item.basedescription.ToLower().Contains(" " + service.Pluralize(searchWords[x]) + " ")
                     || item.basedescription.ToLower().Contains(" " + service.Singularize(searchWords[x]) + " ")
                     || item.basedescription.ToLower().Contains(searchWords[x])
                     || item.basedescription.ToLower().Contains(service.Pluralize(searchWords[x]))
                     || item.basedescription.ToLower().Contains(service.Singularize(searchWords[x])))

                     || (item.info.ToLower().StartsWith(searchWords[x] + " ")
                     || item.info.ToLower().StartsWith(service.Pluralize(searchWords[x]) + " ")
                     || item.info.ToLower().StartsWith(service.Singularize(searchWords[x]) + " ")
                     || item.info.ToLower().EndsWith(" " + searchWords[x])
                     || item.info.ToLower().EndsWith(" " + service.Pluralize(searchWords[x]))
                     || item.info.ToLower().EndsWith(" " + service.Singularize(searchWords[x]))
                     || item.info.ToLower().Contains(" " + searchWords[x] + " ")
                     || item.info.ToLower().Contains(" " + service.Pluralize(searchWords[x]) + " ")
                     || item.info.ToLower().Contains(" " + service.Singularize(searchWords[x]) + " ")
                     || item.info.ToLower().Contains(searchWords[x])
                     || item.info.ToLower().Contains(service.Pluralize(searchWords[x]))
                     || item.info.ToLower().Contains(service.Singularize(searchWords[x])))

                     || (item.itemgroup.ToLower().StartsWith(searchWords[x] + " ")
                     || item.itemgroup.ToLower().StartsWith(service.Singularize(searchWords[x]) + " ")
                     || item.itemgroup.ToLower().EndsWith(" " + searchWords[x])
                     || item.itemgroup.ToLower().EndsWith(" " + service.Singularize(searchWords[x]))
                     || item.itemgroup.ToLower().Contains(" " + searchWords[x] + " ")
                     || item.itemgroup.ToLower().Contains(" " + service.Singularize(searchWords[x]) + " ")
                     || item.itemgroup.ToLower().Contains(searchWords[x])
                     || item.itemgroup.ToLower().Contains(service.Singularize(searchWords[x])))

                     || (item.itembrand.ToLower().StartsWith(searchWords[x] + " ")
                     || item.itembrand.ToLower().EndsWith(" " + searchWords[x])
                     || item.itembrand.ToLower().Contains(" " + searchWords[x] + " ")
                     || item.itembrand.ToLower().Contains(searchWords[x]))

                     || (item.itemtype.ToLower().StartsWith(searchWords[x] + " ")
                     || item.itemtype.ToLower().StartsWith(service.Pluralize(searchWords[x]) + " ")
                     || item.itemtype.ToLower().StartsWith(service.Singularize(searchWords[x]) + " ")
                     || item.itemtype.ToLower().EndsWith(" " + searchWords[x])
                     || item.itemtype.ToLower().EndsWith(" " + service.Pluralize(searchWords[x]))
                     || item.itemtype.ToLower().EndsWith(" " + service.Singularize(searchWords[x]))
                     || item.itemtype.ToLower().Contains(" " + searchWords[x] + " ")
                     || item.itemtype.ToLower().Contains(" " + service.Pluralize(searchWords[x]) + " ")
                     || item.itemtype.ToLower().Contains(" " + service.Singularize(searchWords[x]) + " ")
                     || item.itemtype.ToLower().Contains(searchWords[x])
                     || item.itemtype.ToLower().Contains(service.Pluralize(searchWords[x]))
                     || item.itemtype.ToLower().Contains(service.Singularize(searchWords[x])))

                     || (item.itemsubtype.ToLower().StartsWith(searchWords[x] + " ")
                     || item.itemsubtype.ToLower().StartsWith(service.Pluralize(searchWords[x]) + " ")
                     || item.itemsubtype.ToLower().StartsWith(service.Singularize(searchWords[x]) + " ")
                     || item.itemsubtype.ToLower().EndsWith(" " + searchWords[x])
                     || item.itemsubtype.ToLower().EndsWith(" " + service.Pluralize(searchWords[x]))
                     || item.itemsubtype.ToLower().EndsWith(" " + service.Singularize(searchWords[x]))
                     || item.itemsubtype.ToLower().Contains(" " + searchWords[x] + " ")
                     || item.itemsubtype.ToLower().Contains(" " + service.Pluralize(searchWords[x]) + " ")
                     || item.itemsubtype.ToLower().Contains(" " + service.Singularize(searchWords[x]) + " ")
                     || item.itemsubtype.ToLower().Contains(searchWords[x])
                     || item.itemsubtype.ToLower().Contains(service.Pluralize(searchWords[x]))
                     || item.itemsubtype.ToLower().Contains(service.Singularize(searchWords[x])))

                     ).ToList();

This query takes roughly 10 seconds to execute on the first word, and is pretty quick on the rest of the words. How can I significantly improve the performance of this LINQ query while maintaining flexibility of search results?

\$\endgroup\$
4
  • 1
    \$\begingroup\$ Just to be sure... with this query you are filtering the results that you get from the stored procedure, right? This is not EF, is it? \$\endgroup\$ Commented Sep 2, 2016 at 4:14
  • \$\begingroup\$ @t3chb0t it is a stored procedure imported into my model, so it's accessing my sql server stored procedure using EF \$\endgroup\$ Commented Sep 2, 2016 at 4:28
  • \$\begingroup\$ @t3chb0t I thought your "code of the month" joke was quite funny. :-P \$\endgroup\$ Commented Sep 2, 2016 at 4:34
  • \$\begingroup\$ Oh, Sorry about that ;-] I actually meant your code is a nice challange. it must be the moderators who removed it. The worse the code is the more challenging the optimization is and I like it ;-D \$\endgroup\$ Commented Sep 2, 2016 at 4:44

2 Answers 2

Reset to default
5
\$\begingroup\$

It seems weird or unusual that you're assigning products from products. products is your IQueryable<Product> source, and the result of the query is some List<Product>.

That said, this is one such case where the query syntax has an advantage over the method syntax - be it only to reduce the repetition by leveraging the let keyword and caching the service method calls' return values instead of re-computing them for every single comparison, for every single item in your source:

var value = searchWords[x];
var pluralized = service.Pluralize(value);
var singularized = service.Singularized(value);

var result = (from product in products
    let description = product.basedescription.ToLower()
    let info = product.info.ToLower()
    let itemGroup = product.itemgroup
    where description.StartsWith(pluralized)
       || description.StartsWith(singularized)
       || ...
       || info.StartsWith(pluralized)
       || info.StartsWith(singularized)
       || ...
       || itemGroup.StartsWith(pluralized)
       || itemGroup.StartsWith(singularized)
       || ...
    ).ToList();

The || operator is short-circuiting, so the whole expression is true as soon as one condition is true - by putting the .Contains checks first, the .StartsWith and .EndsWith checks would only be executed when .Contains is false... but then, these checks are redundant:

  • If .Contains(value) is true...
    • .Contains(" " + value + " ") is true.
    • .StartsWith(value) is also true.
    • .EndsWith(value) is true as well.

If .Contains(value) is false, then all of the above are false as well.

Consider extracting each "group" of conditions into their own, well-named, private function.

Remember that in C#, member names should be PascalCase, so basedescription should be BaseDescription, for example.

The final code could look like this:

var value = searchWords[x];
var pluralized = service.Pluralize(value);
var singularized = service.Singularized(value);

var result = (from product in products
    let description = product.basedescription.ToLower()
    let info = product.info.ToLower()
    let itemGroup = product.itemgroup.ToLower()
    where IsFound(value, description, pluralized, singularized)
       || IsFound(value, info, pluralized, singularized)
       || IsFound(value, itemGroup, pluralized, singularized)
    ).ToList();

Where IsFound is a little function that matches a Func<string,string,string,string,bool> delegate.

string.ToLower() and string.ToLowerInvariant() is probably a relevant read, too... as would be How can I do a case insensitive string comparison.

\$\endgroup\$
6
  • \$\begingroup\$ The OP tags the question with EntityFramework so I guess the ToLower is unnecessary anway. \$\endgroup\$ Commented Sep 2, 2016 at 3:55
  • \$\begingroup\$ I'm afraid that Consider extracting each "group" of conditions into their own, well-named, private function. this won't work with EF. It will fail to call the method ans not translatable to sql. You would need to get the entire table first and execute it localy as linq to objects instead of linq to entities/sql. \$\endgroup\$ Commented Sep 2, 2016 at 3:58
  • \$\begingroup\$ @t3chb0t gosh, and I even implemented a LINQ provider and gone through the pain of supporting a specific set of method calls... editing that out, thanks! \$\endgroup\$ Commented Sep 2, 2016 at 4:06
  • \$\begingroup\$ As for case-sensitivity, wouldn't that depend on the collation of the database being queried? \$\endgroup\$ Commented Sep 2, 2016 at 4:08
  • \$\begingroup\$ Yes, that's right. But the OP doesn't mention changing it - I also hardly know any case where it was changed ;-) I think the workflow needs more clarification because it looks like the OP could be working with linq to objects after all I'm getting results from a stored procedure using Entity Framework. Finally, for each word in the array, I'm filtering the results using this query: well, I'm really confused whether the Where is still EF or not. \$\endgroup\$ Commented Sep 2, 2016 at 4:12
3
\$\begingroup\$

I'd like to suggest an improved solution to what Mat's Mug♦ suggested.

You could create an interface like this:

interface ISearchable
{
    IEnumerable<string> SearchProperties { get; }
}

and implement it in your model - notice the partial (link to SO) keyword that will allow you to provide additional functionality for the model:

partial class Model : ISearchable 
{
    public string basedescription { get; set; }

    public string info { get; set; }

    public string itemgroup { get; set; }

    public IEnumerable<string> SearchProperties
    {
        get 
        {
            yield return basedescription;
            yield return info;
            yield return itemgroup;
            ...
        }
    }
}

then you adjust the where part of this query to use this new property:

var searchWord = searchWords[x];
var pluralized = service.Pluralize(value);
var singularized = service.Singularized(value);

var result = products.Where(p 
    => p.SearchProperties.Any(sp 
        => IsMatch(sp, searchWord, pluralized, singularized)
    )
).ToList();

where IsMatch implements your conditions:

bool IsMatch(string value, string searchWord, string pluralized, string singularized)
{
    ....
}

If you have another model that you want to search just create a method that works with this interface:

IList<T> Search<T>(IEnumerable<ISearchable> elements, ... search criteria)
{

    ...

    var result = elements.Where(x 
        => x.SearchProperties.Any(sp 
             => IsMatch(sp, searchWord, pluralized, singularized)
        )
    ).Cast<T>().ToList();

    return result;
}
\$\endgroup\$
4
  • \$\begingroup\$ I should have mentioned that the model for the stored procedure is imported using Database First approach. I am able to update my model created from the stored procedure to match the class Model : ISearchable class you've written, however, every time I update the model because of a new property I add on SQL Server, the model would go back to its original state. Other than that, your answer would probably be the best answer. \$\endgroup\$ Commented Sep 2, 2016 at 17:12
  • \$\begingroup\$ @bkhosh2 not necessarily, you can add another class with the same name but like public partial class Model : ISearchable {} it will then extend the original model becasue the generated class is already partial. I usually put them a subfolder named Custom. Just remember to remove the Custom from the namespace when VS creates the file. \$\endgroup\$ Commented Sep 2, 2016 at 17:16
  • \$\begingroup\$ @bkhosh2 you are right, you should not modify the generated classes because they'll be overwritten, that's why they are partial - so that we can extend them. I added this information to the review. \$\endgroup\$ Commented Sep 2, 2016 at 17:17
  • \$\begingroup\$ In that case, I will try your method as well and see which answer produces better results. \$\endgroup\$ Commented Sep 2, 2016 at 17:26

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.