How to improve LINQ query

Question

I am working on an application where a user can filter records by categories.

A record must have 1 but can have many categories. (1-*)

The question I have is, What can I do to improve this search? It currently runs at about an O(n^3) for the following reason:

A transaction may have many records (1-many) A record may have many categories (1-many) Iterate through the categories selected for searching

The basic outline of what I am doing is the following:

retrieve all announcements

if(startDate and endDate have values)
    model = model.Where(x => x.WADate >= start.Value && x.WADate <= end.Value).ToList();

if(queryString is not null)
    model = model.Where(
                        x => x.WANum.Contains(query) ||
                        x.Tooltip.ToLower(CultureInfo.InvariantCulture).Contains(query)
                        || x.Topic.ToLower(CultureInfo.InvariantCulture).Contains(query)
                    ).ToList();

if (selectedCategories.Count > 0)
        {
            bool HasMatch;
            foreach (var ancmt in announcements)
            {
                HasMatch = false;
                foreach (var cat in selectedCategories)
                {
                    foreach (var xref in ancmt.waXref)
                    {
                        if (cat.ID == xref.WACategoryID)
                        {
                            HasMatch = true;
                        }
                    }
                }
                if(HasMatch)
                {
                    model.Add(new EditViewModel
                    {
                        WATypeID = ancmt.WATypeID,
                        WANum = ancmt.WANum,
                        WATypeName = ancmt.waType.WATypeDescription,
                        Link = ancmt.Link,
                        Tooltip = ancmt.Tooltip,
                        Topic = ancmt.Topic,
                        WADate = ancmt.WADate,
                        WAID = ancmt.WAID,
                    });

                    ancmt.waXref.ToList().ForEach(
                        x => model.Last().Categories.Add(
                            new CategoryViewModel { ID = x.WACategoryID, Name = x.waCategory.WACategory, IsSelected = false }));
                }
            }
        }
        // If no catgories were selected, keep all announcements for next stage of search
        else
        {
            foreach (var ancmt in announcements)
            {
                model.Add(new EditViewModel
                {
                    WATypeID = ancmt.WATypeID,
                    WANum = ancmt.WANum,
                    WATypeName = ancmt.waType.WATypeDescription,
                    Link = ancmt.Link,
                    Tooltip = ancmt.Tooltip,
                    Topic = ancmt.Topic,
                    WADate = ancmt.WADate,
                    WAID = ancmt.WAID,
                });

                ancmt.waXref.ToList().ForEach(
                    x => model.Last().Categories.Add(
                        new CategoryViewModel { ID = x.WACategoryID, Name = x.waCategory.WACategory, IsSelected = false }));
            }
        }

I am using Method syntax, not query and prefer to stay in method syntax.

Answer 1

For any real performance gains it would be best to create specific Stored Procedures to handle the true heavy lifting processing in the database and not on the client PC.