Linq the right way

We all know how working with a functional language makes coding so easy, and can make you a bit lazy.

Lets looks a some good practice and some horrible ones:

  1. Don’t string your function calls out for a long way. It makes your code horrible for troubleshooting.
    1. Example of BAD:
      var _a = _context.tableA.Where(n->n.fielda == “Some Val”).OrderBy(n->fieldb).select(n-> new {fname = n.fn, lname = n.ln, postal = n.zip.substring(0,5)}).FirstOrDefault();
    2. Example of GOOD:
      var _a1 = _context.tableA.Where(n->n.fielda == “Some Val”)
      var _a2 = _a1.OrderBy(n->fieldb); //Maybe, you may want to put the first or default here.
      var _a3= _a2.FirstOrDefault(); //This shrunk the data set before reshaping.
      var _a4= _a3).select(n-> new {fname = n.fn, lname = n.ln, postal = n.zip.substring(0,5)});
      Note how you have 4 placed to do breakpoints for testing.
    3. Example of BEST:
      Move all of the query to a stored proceedure. Build a custom structure for the return. Embed the call into your TESTABLE data object.
      MyStrongTypedStructure _a = contextManager.MyStoredProc(“Some Val”);
    4. The best way to look at this, is that I have coded, refactored, and re-refactored. My code is now excelent. (We hope. Using test cases, and actually testing your code will prove it.)
  2. Look at 1.4 above. REFACTOR, REFACTOR, REFACTOR
    1. Look, most RDBMS systems are designed to handle stored proceedures a lot better than your local code. Take advantage of it.
    2. Don’t over join.
      Use temp tables, a feature native in most mature RDBMS systems, and you will find that you can make code that is a lot more efficient.
      1. “select a.a from a join b on a.b = b.b join c on b.c = c.c where c.d = ‘nn'” can be changed to
        select into #filterc from c where c.d = ‘nn’
      2. At this point you already have a lot tighter join than you had before by replacing c with #filterc.
      3. If c reduces down to a single variable then just declare the variable, and set it from a select into c. Then you don’t even need temp tables.
  3. “Temp tables break my link interface”
    1. If you are using Linq to SQL, edit the XML file, and define the object. If you are using EF, build your own class, in code or designer, and assign the return to it.
  4. Remeber, if you are working with Linq, you are working with collections.
    1. SQL collections come in 2 flavors: IEnumerable, and IQueryable.
      1. IEnumerable means the resulting objects are populated in memory, and can hog resources that you need.
        If you are using linq against a refrence to an existing collection, then you may be safe from this memory hog. I say “may” because there are all sorts of ways to cause this to expand.
      2. IQueryable means the resulting object is a query that when executed will retrieve the data that you are requesting.
        Regardless of how well your linq queries are written, if you are relying on linq to build and execute, you may be suprised at how bad the query looks.
        Defer to stored proceedures as much as possible, or work with very small datasets when you cannot.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.