Massive's Dynamic Query Syntax

Rob Conery

It's Dynamic, All The Way To The Core

There's absolutely nothing wrong with the way Scott wrote his initial queries – although I'm sure many of his readers gasped – GASPED! at the use of inline SQL. I do admit it's still hard to get used to seeing it.

But Scott called me on Skype and asked about one of the queries and I mentioned to him that the dynamic invocation bits were a lot more fun than writing the SQL by hand. He didn't quite know what I meant (dang I need to make it easier to find!) – so we worked together to make his Massive stuff more "Massive-y".

This is the hardest thing to get used to. Massive is a bit "magical" in this way and a lot of people don't like it. To be more specific, one of the key features of Massive is it's ability to fire a "method missing" style of "catch" in the same way that Ruby's objects have a method missing hook.

This will work, but it's not "The Massive Way". SQL is a "last-resort" if you will. You can replace the above using this syntax:dynamic tbl = new Shows(); //important! This needs to be declared dynamic
var shows = tbl.Find(Enabled:1, orderby:"DatePublished DESC");

There is no "Find" method on the Shows class – instead it's dynamically "figured out" by implementing "TryInvokeMember" in the Massive source code. If you're interested in the method (it's too long to put here)

Let's take a look at another example. On Scott's home page he only wants the very last show. His original example works, but it's not very fun to read:dynamic tbl = new Shows(); //important! This needs to be declared dynamic
var lastShow = tbl.Query(@"SELECT ShowID, DatePublished, ShowTitle, Description, Enabled, ShowNumber
FROM Shows
WHERE Enabled = 1 and ShowID=IDENT_CURRENT('Shows')
ORDER BY DatePublished DESC").First();

This can replaced using the same dynamic goodness as above:dynamic tbl = new Shows(); //important! This needs to be declared dynamic
var lastShow = tbl.First(Enabled:1, orderby:"DatePublished DESC")

Again, "First" doesn't exist – it's invocation is handled by Massive at runtime and decipherd into a query. In this case, "First" means "SELECT TOP 1".

Finally – and this is a bit more minor – but Scott's constructor is a bit verbose:using System;
using System.Collections.Generic;
using System.Web;

public class Shows : DynamicModel
{
//you don't have to specify the connection – Massive will use the first one it finds in your config
public Shows():base("hanselminutes")
{
PrimaryKeyField = "ShowID";
}
}

This works OK and it's clear, but you can do this too:using System;
using System.Collections.Generic;
using System.Web;

public class Shows : DynamicModel
{
//you don't have to specify the connection – Massive will use the first one it finds in your config
public Shows():base("hanselminutes","ShowID") {}
}

More Ways To Get Dynamic

I just checked in a bunch of changes yesterday so you can now query your heart out using inferred queries:dynamic tbl = new Shows(); //important! This needs to be declared dynamic
var first = tbl.Min(columns:"ID"); // get the lowest ID
var first = tbl.First(); //an easier way to do it
var last = tbl.Last(); //you get the idea
var last = tbl.Max(columns:"ID");
var showCount = tbl.Count();
var totalMinutes = tbl.Sum(columns:"ShowMinutes");
var avgShowMinutes = tbl.Avg(columns:"ShowMinutes");

You might be wondering… "what about joins!" I get asked this question often – and in fact the ORM guys like to make fun of the fact that Massive doesn't make Select N+1 easy for you :). I've been musing on a few ideas – I'll blog about it when I figure it out.

What I've been doing is just creating a method that is specific. So let's say that one of Scott's shows has 1 or more Guests – I'd be specific about what I need:public class Shows : DynamicModel
{
//you don't have to specify the connection – Massive will use the first one it finds in your config
public Shows():base("hanselminutes","ShowID") {}

As I mention – this is a bit more manual then I'd like, but then you don't fall into the Select N+1 trap here and, all in all, it's two lines of code

when you need it, and where you want it.

That's my goal with Massive. You'd be surprised how often you truly need that built-in "object graph" thing in the wild. I find it nice to provide a specific method for what I need when – but that's me.