Today I was working on performance improvements using Fetch XML aggregate functions to replace some of our more data intensive processes in our base Cobalt Engagement Dynamics product. When I began work I started down the path of simply hard-coding the Fetch XML string in the code which looked something like this.
The nice thing about having the Fetch Xml in the code is it’s easy to read and you can quickly tell what the query is supposed to do. Unfortunately, as I constructed my xml I found myself constantly going back and referencing the documentation for the correct syntax since intellisense couldn’t help me.
So, I did a search for the Fetch XSD and quickly found it on MSDN. I copied the XSD to a file on my desktop and ran the xsd utility against it to create the necessary classes to build my Fetch XML without needing to hardcode it. After making a few adjustments to the type names and adding a Serialize / Deserialize method, I added this to my project and was able to write the following code to create the Fetch Xml with full assistance from inellisense.
As you can see from the code, the issue I’m addressing here is calculating payment balance based on the applied amounts of the invoice payments associated with the payment. In the past we were querying all of the invoice payments and then summing up in a loop. Good enough for only a handful of invoice payments but not acceptable for instances where a single payment is applied to many different orders / invoices over time.
Unfortunately, now that I had the intellisense help I wanted the query was practically unreadable. So, while I was at it I decided to take it one step further and create a wrapper class for the classes generated from the XSD. Which resulted in much more clean, simple and readable code.
Pulling all of this together into our code base I added Execute methods to the FetchWrapper class that would execute the resulting Fetch Xml and return a strongly typed collection of entities that I could then pull my aggregate value from with only a couple of more lines of code.
The full source code and Visual Studio 2010 solution can be found here with a surprise extra function that could be useful. The wrapper class has only been gently tested so if you find bugs or make enhancements please feel free to post them here. Of course all code is provided “AS IS,” without express or implied warranty of any kind.