C# LINQ Contains() query for autocomplete search box is slow

Question

I've got a search box that I'm providing autocomplete suggestions for but it's really slow, it takes multiple seconds for suggestions to appear. I'm pretty sure my code is inefficient but I'm not sure the best way to improve it, any suggestions?

[HttpPost]
    [Route("search")]
    public virtual JsonResult Search(string term)
    {
        var result = new List<SearchResult>();

        if (!String.IsNullOrWhiteSpace(term))
        {
            var searchTerms = term.ToLower().Split(' ');
            List<Card> resultList = null;
            foreach (var query in searchTerms)
            {
                if (resultList == null)
                {
                    resultList = CardRepository.FindAll().Where(x => x.Name.ToLower().Contains(query) || x.Set.SetName.ToLower().Contains(query) || x.Variant.ToLower().Contains(query) 
                    || x.CardNumber.ToLower().Contains(query) || (query == "holo" && x.IsHolo)).ToList();
                }
                else
                {
                    resultList = resultList.Where(x => x.Name.ToLower().Contains(query) || x.Set.SetName.ToLower().Contains(query) || x.Variant.ToLower().Contains(query) 
                    || x.CardNumber.ToLower().Contains(query) || (query == "holo" && x.IsHolo)).ToList();
                }
            }

            foreach (var item in resultList.Take(10))
            {
                result.Add(new SearchResult()
                {
                    label = item.FullCardName,
                    value = item.CardId.ToString()
                });
            }
        }

        return Json(result);
    }

EDIT: Added the FindAll() code.

 private readonly IDatabase _database;
 public IQueryable<Card> FindAll()
 {
     return _database.CardDataSource.OrderBy(a => a.Name).AsQueryable();
 }

SOLUTION: Going on the advice from the comments and with reference to this post Full Text Search with LINQ I moved my searching to the repository as a method and the result is almost instant autocomplete suggestions. I'm not sure how much better I could make the performance but it's easily usable in its current state.

public Card[] Search(string[] searchTerms)
{
    IQueryable<Card> cardQuery = _database.CardDataSource;
    foreach(var term in searchTerms)
    {
        var currentTerm = term.Trim();
        cardQuery = cardQuery.Where(p => (p.Name.Contains(currentTerm) ||
                                            p.Variant.Contains(currentTerm) ||
                                            p.CardNumber.Contains(currentTerm) ||
                                            p.Set.SetName.Contains(currentTerm) ||
                                            (term == "holo" && p.IsHolo) ||
                                            (term == "reverse" && p.IsHolo))
                                        );
    }

    return cardQuery.Take(10).ToArray();
}

[HttpPost]
[Route("search")]
public virtual JsonResult Search(string term)
{
    var result = new List<SearchResult>();

    if (!String.IsNullOrWhiteSpace(term))
    {
        var searchTerms = term.ToLower().Split(' ');
        var resultList = CardRepository.Search(searchTerms);

        foreach (var item in resultList)
        {
            result.Add(new SearchResult()
            {
                label = item.FullCardName,
                value = item.CardId.ToString()
            });
        }
    }

    return Json(result);
}

Answer 1

I think that the main problem is that you're using .FindAll() which returns a List<T>.

This means that when you say CardRepository.FindAll() it gets all of the records into an in-memory list and then your subsequent refining queries (e.g. Where(x => x.Name.ToLower().Contains(query)) and so on) are all run against the entire list. So it's right that it's returning really slowly.

You could try rewriting it by simply removing the .FindAll() and see what happens.

Please note, I'm just giving you the main problem, there are other issues, but none is as important as this one.

Answer 2

You could use multi-threading like this (pseudo-C# code):

var allCards = CardRepository.FindAll().ToArray(); // Ensure array.
query = query.ToUpper();

var nameTask = Task.StartNew(() => allCards.Where(x => x.Name.ToUpper().Contains(query)).ToArray());
var setTask = Task.StartNew(() => allCards.Where(x => x.Set.SetName.ToUpper().Contains(query)).ToArray());
var variantTask = Task.StartNew(() => allCards.Where(x => x.Variant.ToUpper().Contains(query)).ToArray());
var cardNumberTask = Task.StartNew(() => allCards.Where(x => x.CardNumber.ToUpper().Contains(query)).ToArray());
var holoTask = Task.StartNew(() => allCards.Where(x => query == "holo" && x.IsHolo).ToArray());

Task.WaitAll(new Task[] {nameTask, setTask, variantTask, cardNumberTask, holoTask});

var result = (nameTask.Result + setTask.Result + variantTask.Result + cardNumberTask.Result + halaTask.Result).Distinct().ToArray();