In Insight.Database v4.0, there was one major goal:

Make it easy to return arbitrary trees of objects from SQL results.

See the full documentation on github.com.

I’m really happy with how our little micro-ORM turned out. Before v4.0, you could return:

• Single Records
• Lists of Records
• Multiple Lists of Records

Each record could be:

• A single object.
• A single object with one-to-one mappings to additional records.

The big thing missing was:

• An object with a list of children (one-to-many).

And of course:

• An object with a list of children with a list of children.
• An object with a list of children with other one-to-one mappings.
• etc.

Now you can do all of this.

One Object

Reading a single object:

Beer beer = connection.Single<Beer>("GetABeer");

List of Objects

Reading a list of objects:

IList<Beer> beer = connection.Query<Beer>("GetAListOfeer");

Two Lists of Objects

Reading two lists of objects:

var results = connection
    .QueryResults<Beer, Glass>("GetAllBeersAndAllGlasses");
IList<Beer> beers = results.Set1;
IList<Glass> glasses = results.Set2;

You can return up to 16 sets of objects this way.

This is equivalent to:

connection.Query(“GetABeerAndProperGlass”, Parameters.Empty,
Query.Returns(Some.Records)
.Then(Some.Records);
[/sourcecode]

Keep an eye on Query.Returns. It gets pretty powerful.

One-To-One Relationships

Reading a list of objects with a one-to-one mapping:

IList<Beer> beers = connection
    .Query<Beer, Glass>("GetABeerAndProperGlass");
Beer beer = beers.First();
Glass glass = beer.Glass;

This is equivalent to:

connection.Query("GetABeerAndProperGlass",
    Parameters.Empty,
    Query.Returns(OneToOne<Beer, Glass>.Records);

Insight will automatically split each record into Beer objects and Glass objects, then assemble them properly. You can get more details by reading [[Object Graphs]].

If you almost always return two objects together, you can put a RecordsetAttribute on the class, so Insight will always try to read in the subobject. It’s ok if the subobject isn’t there.

[Recordset(typeof(Beer), typeof(Glass))]
class Beer { ... }

One To Many Relationships

Here’s where we start to get tricky. For anything advanced, we need to specify the structure that the query returns:

CREATE PROC GetBeerAndPossibleGlasses AS
SELECT * FROM Beer
SELECT b.BeerID, g.8 FROM Beer JOIN Glasses ON (...)

class Beer
{
    public int ID;
    public IList<Glass> Glasses;
}

var results = connection
    .Query("GetBeerAndPossibleGlasses",
    Parameters.Empty,
    Query.Returns(Some<Beer>.Records)
        .ThenChildren(Some<Glasses>.Records);

Now Insight knows that you want a list of beer records, each with a list of glasses that match.

Automatic Assembly

Insight will assume that the Beer class has an ID field, and that the first column in the glasses query is the Beer ID. Then it will break up the glasses into lists and assign them into your Beer objects.

To determine the ID field, Insight uses the following order of precedence:

1. The field explicitly requested in the structure definition.
2. The field on the class marked with the [RecordId] attribute.
3. A field called “ID”.
4. A field called “classID”, where “class” is the short name of the class.
5. A field ending in “ID”.

To determine the target list field, Insight uses the following order of precedence:

1. The field explicitly requested in the structure definition.
2. The field on the class marked with the [ChildRecords] attribute that matches the type of the list.
3. Any field matching the type of the list.

The list field/property can be any of the following types:

* IList
* IEnumerable
* ICollection
* List

If Insight doesn’t guess the ID field or list field right, you can tell it with attributes.

class Beer
{
    [RecordId] public int ID;
    [ChildRecords] public IList<Glass> Glasses;
}

Or you can tell it with hints on the query:

var results = connection.Query("GetBeerAndPossibleGlasses",
    Parameters.Empty,
    Query.Returns(Some<Beer>.Records)
    .ThenChildren(
        Some<Glasses>.Records,
        id: beer => beer.ID,
        into: (beer, glass) => beer.Glasses = glass);

Other Notes:

* The way it’s coded, it’s possible for more than one parent to have a given ID, so it’s actually possible to return many-to-many mappings. Woot.

Mixing and Matching

You can mix and match Then and ThenChildren and also OneToMany records. Using these pieces, you can make almost any structure out of your data.

This is getting a little crazy, but it’s not icky at all! Returning 4 recordsets, with two parents and two children!

Results<Beer, Wine> results = c.QuerySql(
   @"    SELECT * FROM Beer WHERE Type="IPA"
   SELECT b.ID, g.* FROM Glasses g JOIN RightGlass(...) ...
   SELECT * FROM Wine WHERE Type="IPA"
   SELECT w.ID, g.* FROM Glasses g JOIN RightGlass(...) ...",
   parameters,
   Query.Returns(Some<Beer>.Records)
       .ThenChildren(Some<Glass>.Records)
       .Then(Some<Wine>.Records);
       .ThenChildren(Some<Glass>.Records)