Q:

Optimise Entity Framework query with large record set

I need to optimise the following EF query as initially I am bringing back everything back from the CustomerWidgets table.     Now the table has 100,000 rows it is starting to become slow.   I am querying a sql server dbase.
I guess I don’t need to bring everything back first so what is the best approach to modify the code below?
        public List<CustomerWidget> SearchWidgets(string surname, string firstname, string ZipCode)
    {
        // i don't need to bring back everything here!!  optimize it I say!!
        var widgetSearchResults = _context.CustomerWidgets.Where(x => x.IsDeleted == false).ToList();

        if (!string.IsNullOrEmpty(surname))
        {
            widgetSearchResults =
                widgetSearchResults.Where(x => x.Surname.ToUpper().Contains(surname.ToUpper())).ToList();
        }

        if (!string.IsNullOrEmpty(firstname))
        {
            widgetSearchResults =
                widgetSearchResults.Where(x => x.Forename.ToUpper().Contains(firstname.ToUpper())).ToList();
        }

        if (!string.IsNullOrEmpty(ZipCode))
        {
            widgetSearchResults =
                widgetSearchResults.Where(
                    x => x.ZipCode.Replace(" ", "").ToUpper().Contains(ZipCode.Replace(" ", "").ToUpper()))
                    .ToList();
        }

        return widgetSearchResults;
    }

A:

Call ToList() at the end only, and work with IQueryable before:
public List<CustomerWidget> SearchWidgets(string surname, string firstname, string ZipCode)
{
    var widgetSearchResults = _context.CustomerWidgets.Where(x => x.IsDeleted == false);

    if (!string.IsNullOrEmpty(surname))
    {
        widgetSearchResults =
            widgetSearchResults.Where(x => x.Surname.ToUpper().Contains(surname.ToUpper()));
    }

    if (!string.IsNullOrEmpty(firstname))
    {
        widgetSearchResults =
            widgetSearchResults.Where(x => x.Forename.ToUpper().Contains(firstname.ToUpper()));
    }

    if (!string.IsNullOrEmpty(ZipCode))
    {
        widgetSearchResults =
            widgetSearchResults.Where(
                x => x.ZipCode.Replace(" ", "").ToUpper().Contains(ZipCode.Replace(" ", "").ToUpper()));
    }

    /********************
      Call ToList() only here
      *******************/

    return widgetSearchResults.ToList();
}

EF constructs the whole SQL query when actually retrieving the elements from the database, so with your method, you are first retrieving:
SELECT * FROM CustomerWidgets WHERE IsDeleted = false

And then the rest of your queries are executed from the in-memory list (of 100.000 items).
By removing the ToList() calls till the very end, you work with an IQueryable, which is just adding Where clauses, and gets constructed and retrieved only when you call ToList(), so your SQL query ends up being something like:
SELECT * FROM CustomerWidgets WHERE 
           IsDeleted = false 
       AND UPPER(Surname) LIKE UPPER('%Something%')
       AND UPPER(FirstName) LIKE UPPER('%Something%')
       AND UPPER(ZipCode) LIKE UPPER('%Something%') -- plus all the replacing

Which should retrieve only the exact items you want to query for
For extra optimization, you might want to remove all those ToUpper and set a collation in SQL server which is case insensitive (those with a CI in the name): with correct indices, this should be MUCH faster than converting your strings to uppercase in the query.
Also, your ZipCode replacing (for " " to "") should preferably get out of the Where clause, so you could do:
    if (!string.IsNullOrEmpty(ZipCode))
    {
        ZipCode = ZipCode.Replace(" ", "");
        widgetSearchResults =
            widgetSearchResults.Where(
                x => x.ZipCode.Replace(" ", "").ToUpper().Contains(ZipCode.ToUpper()));
    }

This would still call the REPLACE SQL function on the column tho (not on the where clause). You could just not replace anything there: it would be faster for SQL Server to check for extra empty spaces than to call a function for every row. A warning though: this could change the functionality: with your current code, "01 05" would match "0 105"; without the replacement, "01 05" would only match "01 05". If that doesn't work for you, keep the replaces.
PS: I haven't tested if your queries would convert directly to SQL Server (it may raise an exception if they don't)... at first glance, I think they should convert well, but if they don't, it should be easy to work out those expressions first.
Other than this, if your application allows it, you could use paging (using Skip and Take in Linq) if you want to retrieve less elements at once... but I see no further optimizations that you could take.
To recap:

Construct the query with the WHERE clauses instead of retrieving all and then filter in-memory. This is done by calling ToList() at the end
Remove all unnecessary per-row functions (specifically ToUpper in this case). If you can change the column collation, SQL Server will perform case insensitive searches.
No need to replace " " with "" on both the record and the query clause. It'll be faster if you don't. Check the warning on the answer text though.
Make the correct indices on SQL Server
Use paging if that's not enough and your application supports paging

