MicroORMs for .NET: Syntax - SELECTs

This post is the is part of series of posts covering the various microORMs in the .NET world. I intend to look at how each microORM meets certain data access and manipulation needs. The current series includes,

• Obligatory Introduction Post
• Syntax - SELECTs
• Stored Procedures
• Inserts, Updates & Deletes
  

The two main things I want to look at in this article are

1. Syntax of the microORM, specifically around,
2. Selecting single and multiple results

So the most obvious starting point, bar the boring introduction, is how you perform selects using these awesome thingies. To begin with lets get our playground set up. Lets create a table of authors, lets call it Authors⁰

CREATE TABLE Authors(
  Id          int          IDENTITY(1,1) NOT NULL,
  Username    varchar(50)                NOT NULL,
  FullName    varchar(250)               NOT NULL,
  CreatedDate datetime                   NOT NULL,

 CONSTRAINT PK_Authors PRIMARY KEY CLUSTERED (Id ASC)
)

And populate it with some (meaningless) data¹,

INSERT INTO Authors(Username, FullName, CreatedDate) VALUES ('[email protected]', 'James Red', GETDATE());
INSERT INTO Authors(Username, FullName, CreatedDate) VALUES ('[email protected]', 'Harry Black', GETDATE());
-- ...CROPPED FOR BREVITY...
INSERT INTO Authors(Username, FullName, CreatedDate) VALUES ('[email protected]', 'Chloe Green', GETDATE());

Finally we need to create the equivalent C# object that our results can map to. Again nothing too crazy here,

public class Author
{
    public int Id { get; set; }
    public string Username { get; set; }
    public string FullName { get; set; }
    public DateTime CreatedDate { get; set; }
}

So now the stage is set and it's time for our microORMs to play their part.

Selecting Single and Multiple Authors

So lets assume we want to do 2 things -

• Select a single author with an Id of 1, and,
• Select multiple authors whose username (in this case the users email address) ends with example.com.

Dapper

Dapper has a single way of querying your database and it offers this via an extension method on the standard SqlConnection class,

using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    conn.Open();
    Author a = conn.Query<Author>(
        "SELECT * FROM Authors WHERE Id = @Id",
        new { Id = 1 }).First();
}

As you can see I've just opened a normal connection using a ConnectionString property I pulled from my App.config's ConnectionString config block. The magic here lies in the Query<Author> method. This method accepts a parameterised SQL string and maps the result back into the object you specified. Ultra simple, ultra clean. There are a whole host of other arguments to the Query<T> method but it would fill this post up pretty fast.

Selecting multiple object simply requires me to change the query to meet the new requirements,

IEnumerable<Author> dapperA = conn.Query<Author>(
    "SELECT * FROM Authors WHERE Username LIKE @PartialUsername",
    new { PartialUsername = "%example.com" });

One of the features I really liked apart from the simplicity of the whole thing was the parameterised SQL. Many of the frameworks support this but most support it by index based assignment e.g. "WHERE Id=@0". Thats fine but Dappers use of an anonymous object makes the whole thing that little bit nicer. No need to refactor strings should your arguments move around (it has happened) and it all reads just that tiny bit better as well.

Dapper also offers a non-generic Query method that returns instances of dynamic objects but I'm still to be convinced that this is useful when pushing these models into and back out of views etc. Still it's nice to have for those times that creating another model would be overkill.

Massive

Massive is slightly different to the other microORMs in that it only deals with dynamic objects from queries. This means if you need concrete models you will have to map them either manually or use a tool to help (such as AutoMapper). Due to my schema (pluarl table names) I use Massive in a slightly different way to most people. Massive requires your models to extend Massive.DynamicModel so to keep things clean I tended to naturally create a model that represents my Authors table,

public class Authors : Massive.DynamicModel
{
    public Authors()
        : base("DefaultConnectionString")
    {
        PrimaryKeyField = "Id";
    }
}

This provides an entry point for configuring how Massive connects to your database. The DynamicModel has a constructor that allows us to specify a connection string name that Massive will lookup from the Web/App.config ConnectionStrings config section. Alternatively if you don't supply this Massive will grab the first one it finds. This class also allows us to specify the table name (in this case inferred from the class name) and properties such as the primary key of the table (as shown above).

Now we have our class defined we can start selecting from the database. To select a single object we can use the Single method.

Authors table = new Authors();
dynamic result = table.Single(1);
Author author = Authors.Map(result);

You'll notice I've made another step here and that is mapping the dynamic result to a concrete Author object. It's a simple mapping method added to the Authors object but could be made more generic fairly easiy,

public static Author Map(dynamic obj)
{
    return new Author
    {
        Id = obj.Id,
        CreatedDate = obj.CreatedDate,
        FullName = obj.FullName,
        Username = obj.Username
    };
}

Selecting multiple objects can be done in 2 ways using All which returns the entire table and then you filter or using the Query method (preferred unless you really want to return the entire table for refdata or something I guess)

var tbl = new Authors();
IList<dynamic> dyn = tbl.Query(
    "SELECT * FROM Authors WHERE Username LIKE @0",
    "%example.com");

dynamic db = new Products();
var products = db.FindBy(
    CategoryID:8,
    UnitPrice:100
);

PetaPoco

PetaPoco has a number of way of selecting single objects, or more specifically multiple ways of building/manipulating SQL queries. But before we do that we need to get access to the database. We do this via the PetaPoco.Database object which is an IDisposable wrapper around a Database connection that gives us the PetaPoco magic.

using (PetaPoco.Database db = new PetaPoco.Database("DefaultConnectionString"))
{
    // query goes here
}

The Database constructor takes a name of a defined connection string to use for connecting to the database. Now we are ready to start writing our query,

Using Single(id)

Author a = db.Single<Author>(1);

Using Custom Parameterised SQL

Author b = db.Single<Author>("SELECT * FROM Authors WHERE Id = @0", 1);

Using SQL Builder to Append Statements

Author c = db.Single<Author>(
    PetaPoco.Sql.Builder
        .Append("SELECT * FROM Authors")
        .Append("WHERE Id = @0", 1));

Using SQL Builders Fluent Syntax to Append Statements

Author d = db.Single<Author>(
    PetaPoco.Sql.Builder
        .Select("*")
        .From("Authors")
        .Where("Id = @0", 1));

See what I mean? Lots of ways. Now I guess the 3rd and 4th methods will probably seem a bit OTT for this simple select but think how powerful these would be if we had to conditionally build up our where clause or select statement based on a number of user defined conditions. Nice.

Selecting multiple objects offers the same sort of variety except we can use either Query or Fetch. The difference between the two is summed up by the PetaPoco site,

These are pretty much identical except Fetch returns a List<> of POCO's whereas Query uses yield return to iterate over the results without loading the whole set into memory.

So what I say about Query applies to Fetch as well. Retrieving multiple objects is done in the same way as selecting a single object in that you can give the method parameterised SQL or use the SQL builder to construct the query.

IEnumerable<Author> authors = db.Query<Author>(
    "SELECT * FROM Authors WHERE Username LIKE @0",
    "%example.com");

So to cover this off a full select using PetaPoco will would look like this.

using (PetaPoco.Database db = new PetaPoco.Database("DefaultConnectionString"))
{
    Author q = db.Single<Author>("SELECT * FROM Authors WHERE Id = @0", 1);
}

IMO The syntax is nice, clean and flexible. One thing about PetaPoco that is a VERY minor pain point for me is that it assumes singular name for tables which contradicted my earlier decision. Not to worry PetaPoco uses attributes against the model class to manipulate the standard configuration and I can specify a class level attribute of [PetaPoco.TableName("Authors")] to put things right with the world. This also only applies to the queries that use object inference such as Single<T> and not to the methods that accept SQL.

ServiceStack ORMLite

ORMLite from ServiceStack does things a wee bit differently to the rest. It's a bit less self-contained because it also applies extension methods to the string object as well. Also like PetaPoco it takes the singular naming convention for Object Name → Table Name so first things first we need to annotate our Author model with another attribtue

[ServiceStack.DataAnnotations.Alias("Authors")]

Another thing we need to do once before hand is set the dialect the ORMLite will use. This is so it knows how to speak to the specific database type we created. Typically you would put this in something like Global.asax.cs, Program.cs or whatever bootstrapper you care to use as it needs done only once.

OrmLiteConfig.DialectProvider = new SqlServerOrmLiteDialectProvider();

Now we can go ahead and start selecting things like a boss,

using (IDbConnection db = "...".OpenDbConnection())
using (IDbCommand cmd = db.CreateCommand())
{
    Author rows = cmd.GetById<Author>(1);
}

And for selecting many

using (IDbConnection db = Program.ConnectionString.OpenDbConnection())
using (IDbCommand cmd = db.CreateCommand())
{
    List<Author> rows = cmd.Select<Author>(
        "Username LIKE {0}",
        "%example.com");
}

Alternatively if we want to perform a specific operation on each result we can use the Each<T> to iterate over the result set rather than simply return it.

As you can see you don't need to specify a full SQL query, just the WHERE clause which makes everything look a little bit neater and avoids repeating unnecessary portions of SQL that could be inferred.

ORMLite's syntax is slighlty more complex - it feels a bit closer to the metal. But it's certainly not horribly different that it would put me off using it.

removed by original author :(

Simple.Data

Finally Simple.Data offers a very clean syntax that mixes dynamic with concrete results. The first thing you need to do with Simple.Data is get a handle on the Database object that is returned as a special dynamic object. There are a number of ways of doing this,

Simple.Data.Database.Open();
Simple.Data.Database.OpenNamedConnection("DefaultConnectionString");
Simple.Data.Database.OpenConnection(Program.ConnectionString);

By using Database.Open() Simple.Data will look for a connection string called Simple.Data.Properties.Settings.DefaultConnectionString and use it to create the database instance. Alternatively you can specify a specific named connection using Database.OpenNamedConnection or even a raw connection string using Database.OpenConnection

These methods all return a special dynamic object that dynamically creates a SQL statement by constructing methods on the fly. It's best to look at the code for this. To select a single object we can do this,

Author author = Simple.Data.Database.Open().Author.FindById(1);

So whats happening here. Well firstly we a asking it to look at the Authors table. Next we are telling it to FindById - but this method doesn't exist. We could easily have said FindByUsernameAndEmail and we would get the results we expected. This bit of metaprogramming is the magic behind Simple.Data where method signatures are parsed and used to build SQL. Nice stuff. I still have a bit of issue working with dynamic objects but in this case my models are concrete so it's really not an issue.

Selecting multiple objects is that same except instead of FindByXXX we use FindAllByXXX and as this query returns a SimpleQuery object we then need to perform a .Cast<Author> at the end to get back a list of authors.

An alternative and more flexible way of doing queries is using Find and FindAll. These methods let you specify more complex queries. To implement our LIKE comparison for our query above we need to use FindAll like so,

dynamic authors = Simple.Data.Database.Open().Authors;
IEnumerable<Author> authors = authors
    .FindAll(authors.Username.Like("%.com"))
    .Cast<Author>();

This also shows the use of the Cast<T> mentioned above. We can supply a variable number of arguments to Find and FindAll as well as using different comparison operators.

That Was a Long One²

So yeah not only have I touched on selecting single and multiple object at a high level I also had to add a bit of discussion around setting things up so I apologise for the length of this post. On the plus side discussing configuration alone for MyBatis and nHibernate would probably take the same length of time. Thats whats great about these microORMs 99% of the time you don't need all that set up for most projects. No need getting bogged down in configuration when it's not actually achieving anything.

In terms of the syntax - so far I am quite taken by both Simple.Data and PetaPoco but that could change when I start really pushing things - after all everyone can do selects quite easily.

In terms of dynamic results (e.g. from Massive) I'm still to be convinced on their usefulness without having to always map to concrete classes. I don't see how this helps refactoring (it looks like it makes it much worse) nor do I see how it works with most validation mechanisms. But I would love to be proven wrong on this please.

One thing I do know - it's going to take a lot more justification for someone to use a heavyweight ORM in my next project. While I not willing to declare the death of ORMs like some other people I am certainly leaning in that direction.

Source for this series is slowly evolving in a Github repo if you feel inspired to have a poke around.

⁰ We can argue about plural or singular data naming conventions some other time, or in the comments!!!

¹ I created a load of test data using the handy little tool AutoPoco

² "Thats what she said" <self:facepalm>