Rob Conery's Blog

Massive and Complexity

Got an email today from someone watching the MVC 3 series and they asked the question (paraphrasing): “I like what you’re doing with Massive - but can it handle complex queries?”. This, fortunately, is where Massive shines.

In response I wrote this:

“Absolutely. In fact it’s why I wrote it. Here’s a semi-complex query that is all too common in the business case:

var results - DynamicModel.Open(connectionString).Query(@"
  SELECT Orders.OrderNumber, Categories.Name FROM Products
  INNER JOIN Categories ON Categories.CategoryID = Products.CategoryID
  INNER JOIN OrderItems ON OrderItems.ProductID = Products.ID
  INNER JOIN Orders ON Orders.OrderID = OrderItems.OrderID
  WHERE Orders.OrderDate > @0
", DateTime.Now.AddYears(-1));

//roll the results
foreach(var item in results){
  Console.WriteLine("{0} - {1}", item.OrderNumber, item.Name);
}

This is freehanded - but if you tried to write this in LINQ and weren’t very good at it you’d end up with a mess, and mangled SQL. You could, literally, email your DBA, ask for a Query, and pop it into your code and be done here. 

With EF, you’ll diddle over the LINQ and getting the right results back -  and then probably have your DBA storming down the aisles, threatening you with bodily harm with the SQL that his his DB.”

Keep in mind this was an email response and I tend to speak a little more freely :). But I think there’s an important point in there - and it’s not that you should be using Massive ;). It’s that using a tool that embraces SQL (like Dapper or Massive) will speed you on your way rather quickly.

One thing that’s striking me about the MVC 3 videos so far is that the DB work is < 5% of what I’m having to deal with in total. I will admit the dynamic thing is causing me some issues, but overall the DB thing isn’t.