Why Writing Software is like Loading the Dishwasher

Many moons ago, when I was but a child, I remember my father getting irritated with my mother over the dishwasher.  You see, my father would place dishes in the dishwasher, and no matter how he placed them, my mother would move them when she placed additional dishes in there.  This would drive my father nuts, because he always felt like no matter how he put the dishes in, he was always wrong.

My mother, bless her heart, could never manage to really explain her reasoning.  She would only say something like "But I need to put this big dish here, or the plates won't fit", or "The salad bowls only fit here".  She'd only give specific examples of why it was wrong this time.

My father assumed there was always "one true way" to load the dishwasher, that he apparently never understood.  When he tried to understand how my mom was doing it, all he saw was contradictions:  last time, the bowls went on the bottom, and this time the bowls go on the top rack!

My mother, on the other hand, didn't realize that my father was looking for the "one true way", and was confused as to what his problem was.

The real problem is that there is no "one true way" to load a dishwasher.  It's a self-referential problem, where the two parts can't be solved without the other:

1. It can't be loaded correctly until all the dishes are identified.
2. You can't identify all the dishes until you try putting them in the dishwasher and you realize it's full.

Software, I propose, is like loading the dishwasher, in that there is no "one true way" and it's self-referential.  More formally, here is The Dishwasher Problem restated in software development terms:

1. You can't know how it should all fit together until it's done.  As a result, you keep having to move things around as you go.  You can guess where things ought to go, because that's where they've been most of the time, but there are no guarantees.  We tend to call these "best practices" and "patterns".
2. You don't know how much you'll be able to get done until you've done it.  This is where short iterations and quick turnaround are key.  You have fewer variables, and you can solve #1 more easily when there are fewer things to fit together.

And yes, as of my last visit back home for Christmas, my parents are still arguing about how to load the dishwasher.

Group By - Differences between SQL and LINQ

The General Problem

One of the things that I've always struggled with in SQL is the group by clause.  When you use a group by statement in SQL, you have access to only those values you use in the grouping, or the results of aggregate functions.  In order to get any of the other values from the original records, you have to learn tricks like using the XML capabilities, or joining the sub-query back to the original table.  These are cumbersome to create, and difficult to read and figure out, even if you know the "trick".

The grouping is different in LINQ.  When you use a group by statement in LINQ, you end up with a list of collections.  The collections have the original objects with the original data in them, with the Key property on the collection being the value that was grouped by (hooray for smart collections!).  This gives us all sorts of possibilities with the things we can because we have all the original data, grouped the way we want.

A Specific Instance

I regularly would like to generate a CSV list of the values in a "group by" clause.  Let's imagine that you have a table of the FirstName and LastName of each of the Presidents of the United States.  And maybe you'd like to see how many first names are repeated more than once.

Using SQL, you can very easily get the first names that appear more than once:

select FirstName

from President

group by FirstName

having count(*) > 1

With one small addition, you can even list how many times each first name is used:

select FirstName, count(*) as Total

from President

group by FirstName

having count(*) > 1

It gets really difficult, however, when you want to get a list (preferably comma-separated) of the last names to go along with those first names.  It requires that you use the (fairly recent) XML capabilities in SQL Server, or else join back to the original table in some way

Since LINQ actually gives you a collection of the original objects when you group things, you can do much more complex, nifty things without having to "cheat".

So lets see what the equivalents are using LINQ.  Here is the query to produce the equivalent list of first names that appear more than once:

from president in Presidents

group president by president.FirstName into bag

where bag.Count() > 1

select bag.Key

To get the count of how many times each first name is used, you have to introduce an anonymous type, but again it's standard LINQ-type stuff:

from president in Presidents

group president by president.FirstName into bag

where bag.Count() > 1

select new { FirstName = bag.Key, Total = bag.Count() }

Here's where it gets fun:  since it's LINQ, we have full access to the bag variable that we've created, which is a collection of President records.  Since I want to create a CSV string of some of the data inside that collection, I can use a LINQ statement inside the anonymous type to gather that data (LINQ-ception?):

from president in Presidents

group president by president.FirstName into bag

where bag.Count() > 1

select new

{

    FirstName = bag.Key,

    LastNames = string.Join(

        ",",

        (from person in bag select person.LastName))

}

Now I finally get what I want:  a list of first names, followed by a CSV list of last names they go with.  The only "trick" is remember that you have full access to all the original records/objects in the grouped collection, unlike SQL.

FetchXML Happiness in CRM 4.0

One of the best things that happened at Convergence 2011 (besides winning the Xbox Kinect, of course), was learning that you can get the FetchXML from an Advanced Find very easily via the CRM 2011 interface.

However, I still have to work with CRM 4.0, where the FetchXML is completely inaccessible...or is it?

I came across a wonderful post about retrieving the FetchXML from an Advanced Find in CRM 4.0:http://ronaldlemmen.blogspot.com/2006/11/using-advanced-find-for-fetchxml.html

It's an old post, from 2006, but still as useful today as it was then.

The long and short of it is that after you hit the Find button on the Advanced Find dialog, just hit F11 to full-screen the dialog, which gives you an address bar, and then paste the following Javascript into that address bar:

javascript:prompt("my query:", resultRender.FetchXml.value);

It will bring up a dialog with the FetchXML in it, that you can grab and do with what you will. e.g.:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname"/>
    <attribute name="telephone1"/>
    <attribute name="new_jobtitle"/>
    <attribute name="parentcustomerid"/>
    <attribute name="ownerid"/>
    <attribute name="jobtitle"/>
    <attribute name="emailaddress1"/>
    <attribute name="contactid"/>
    <order attribute="fullname" descending="false"/>
    <filter type="and">
      <condition attribute="firstname" operator="eq" value="Bob"/>
    </filter>
  </entity>
</fetch>

In my case (I was writing functional tests for a plugin), I needed to limit the number of results returned by the query, which you normally can't do via the CRM 4.0 interface. I also didn't need all the fields that would normally be returned.

However, it's very easy to add edit to the XML to bring back what you need and limit the number of records returned. I removed everything but the contactid, and added an attribute to the root element to only bring back the first record. The final FetchXML I used looked something like this (though I wasn't looking for guys named "Bob", my query was a little more complex):


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" count="1">
  <entity name="contact">
    <attribute name="contactid"/>
    <filter type="and">
      <condition attribute="firstname" operator="eq" value="Bob"/>
    </filter>
  </entity>
</fetch>

This gave me exactly what I needed , the first contactid to match my criteria, thanks to some simple tricks to get access to the FetchXML.

Dynamics CRM Environment Confusion

Currently I'm working with Microsoft Dynamics CRM, extending the platform, writing custom code & web services and the like. And here's a small problem that I've run in to...

If your company is anything like my company, you don't just have one CRM environment. In fact, we don't just have two CRM environments, we have five environments, each progressing one step up from Development, through QA, and into Production. Even though the Internet Explorer address bar clearly shows which environment I'm in when it's maximized, I frequently get confused, or worse, I can't see which environment is which in the status bar preview.

However, I have discovered how to solve this problem. It's not supported, technically, but we're talking about testing & development environments, not a production one. Also, the change is so minor, it's easy enough to undo it and make sure it isn't the cause of a problem, before calling the Microsoft support line.

If you look in the CRMWeb\_imgs folder in the installation directory (ours is C:\Microsoft CRM\CRMWeb\_imgs), there is a file called "masthead.jpg", that looks like this:

If you update this image, replacing it with one of the exact same dimens

ions, like this one:

it will be seamlessly integrated into CRM. See for yourself:

Voila! Instantly recognizable environment indicators. And if you're just a little OCD like me, I also set the background color on those servers' desktops to the exact same color as the masthead, just so that when I remote into them, it's clear where's I am.