In the BI space I’ve seen a lot of SQL queries succumb to complexity. A data extraction query adds some joins, then some filters, then some nested SELET statements, and it becomes an unhealthy mess in short order. It’s unfortunate, but standard SQL just isn’t a language geared for refactoring towards simplification (although UDFs and CTEs in T-SQL have helped).
I’ve really enjoyed writing LINQ queries this year, and I’ve found them easy to keep pretty.
For example, suppose you need to parse some values out of the following XML:
<ROOT>
<data>
<record>
<field name="Country">Afghanistan</field>
<field name="Year">1993</field>
<field name="Value">16870000</field>
<!-- ... -->
</record>
<!-- ... -->
</data>
</ROOT>
A first crack might look like the following:
var entries =
from r in doc.Descendants("record")
select new
{
Country = r.Elements("field")
.Where(f => f.Attribute("name") .Value == "Country")
.First().Value,
Year = r.Elements("field")
.Where(f => f.Attribute("name").Value == "Year")
.First().Value,
Value = double.Parse
(r.Elements("field")
.Where(f => f.Attribute("name").Value == "Value")
.First().Value)
};
The above is just a mass of method calls and string literals. But, add in a quick helper or extension method…
public static XElement Field(this XElement element, string name)
{
return element.Elements("field")
.Where(f => f.Attribute("name").Value == name)
.First();
}
… and you can quickly turn the query around into something readable.
var entries =
from r in doc.Descendants("record")
select new
{
Country = r.Field("Country").Value,
Year = r.Field("Year").Value,
Value = double.Parse(r.Field("Value").Value)
};
If only SQL code was just as easy to break apart!