3
\$\begingroup\$

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?

\$\endgroup\$
3
\$\begingroup\$

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();
\$\endgroup\$
  • \$\begingroup\$ Awesome, thank you for the Order By tip. However, what if i had 20 if statements, thus making my function huge. Would you have a suggestion as to how the function could be re-written? \$\endgroup\$ – JustLearning Apr 9 '16 at 14:48
  • 2
    \$\begingroup\$ @ShareYourKnowledge I would redesign the data access to use Query Objects - ayende.com/blog/2266/query-objects-vs-methods-on-the-repository. \$\endgroup\$ – almaz Apr 9 '16 at 19:14
0
\$\begingroup\$

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.

\$\endgroup\$
  • 5
    \$\begingroup\$ Could you summarize what's important in the article ? Links can rot and your answer can suffer. \$\endgroup\$ – Marc-Andre Apr 12 '16 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.