使用LINQ搜索关键字的变体

Question

首先，我将输入到搜索字段中的单词添加到数组中。然后，我将使用实体框架的数据库优先方法从Server上的存储过程中获得结果。

最后，对于数组中的每个单词，我使用以下查询过滤结果：

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();

对于第一个单词，这个查询大约需要10秒，其余的单词执行得很快。如何在保持搜索结果的灵活性的同时显着地提高此LINQ查询的性能？

Answer 1

我想对Mat's Mug♦的建议提出一个改进的解决方案。

您可以创建如下界面：

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

并在您的模型中实现它-请注意partial(连结至SO)关键字，它将允许您为模型提供其他功能：

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;
            ...
        }
    }
}

然后调整此查询的where部分以使用以下新属性：

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();

其中IsMatch实现了您的条件：

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

如果要搜索另一个模型，只需创建一个与此接口一起工作的方法：

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;
}