Customer search using LINQ followed by conditional filtering of results

Question

I have a search function in my class which checks various class property values for null or certain values and queries the database based on those properties. This how my function looks like now:

 public object searchCustomerList()
    {
        object partner = null;
        try
        {
            using (var rep = new OrderEntities())
            {
                var results = (from par in rep.Partners
                               join term in rep.CustomerTerms on par.TERMID equals term.TERMID
                               select new
                               {
                                   par.ID,
                                   par.COMPANY,
                                   par.CONTACT,
                                   par.PRICECAT,
                                   par.STATE,
                                   par.TERMID,
                                   par.PHONE,
                                   term.TERMDESC
                               });
                if (customerDetails.priceCategory != Prog.allObjectsIndex) {
                    results = results.Where(c => c.PRICECAT == customerDetails.priceCategory);
                }
                if (customerDetails.termID != Prog.allObjectsIndex) {
                    results = results.Where(c => c.TERMID == customerDetails.termID);
                }
                if (customerDetails.customerState != Prog.allObjectsIndex)
                {
                    results = results.Where(p => p.STATE == customerDetails.customerState);
                }
                if (!string.IsNullOrWhiteSpace(customerDetails.companyName))
                {
                    results = results.Where(p => p.COMPANY.Contains(customerDetails.companyName));
                }
                if (!string.IsNullOrWhiteSpace(customerDetails.contactPerson))
                {
                    results = results.Where(p => p.CONTACT.Contains(customerDetails.contactPerson));
                }
                partner = results.AsEnumerable()                                                                        // Execute method call in Linq-to-Objects                                                                                        
                            .Select(x => new                                                                            // The rest of the query is evaluated in memory
                            {
                                x.ID,
                                x.COMPANY,
                                x.CONTACT,
                                PRICECAT=Prog.getPriceCategory()[(int) x.PRICECAT],
                                STATE = Prog.getStatus()[(int)x.STATE],
                                x.TERMDESC
                            }).OrderBy(x => x.COMPANY).ToList();

            }
        }
        catch (Exception ex)
        {
            saveError("Failed to extract customer information. Error Description:" + ex.Message);
        }
        return partner;
    }

From the little that I know about LINQ, this function is ineffective as it first searches the database and then searches subsets of the result.

Is there a more effective way to re-write this function?

Answer 1

Filtering is actually done correctly, using the best and recommended approach. All LINQ queries are executed lazily, that is they are not executed until you start enumerating them. So all those .Where calls actually just register additional filtering of the records which would translate to entries in the where clause in SQL.

One thing to notice - the call to AsEnumerable() method before .OrderBy is potentially ineffective because it forces the ordering to be done on the client side instead of SQL. I would rewrite it as

    results
        .OrderBy(x => x.COMPANY)
        .AsEnumerable()
        .Select(x => new
        {
            x.ID,
            x.COMPANY,
            x.CONTACT,
            PRICECAT=Prog.getPriceCategory()[(int) x.PRICECAT],
            STATE = Prog.getStatus()[(int)x.STATE],
            x.TERMDESC
        }).ToList();

Answer 2

You could move some of that logic out into an extension method that handles the filtering to clean it up a bit.

See extending expressions.