NDC 2012 Schedule in PDF

Here is a printer friendly version of the NDC 2012 schedule:

Wednesday

Thursday

Friday

Just right click the links and select Save As to save the files.

See you in Oslo :-)

ndc

Auto Generating Inner Joins in PetaPoco

One of the neat features of PetaPoco is the Multi-Poco queries. It allows you to define a query that joins two or more tables and populate a set of related objects.

As an experiment on extending PetaPoco I wanted to be able to auto generate the sql statement for a Multi-Poco query. And I have to say it was pretty simple. Let's say we define our pocos as:

[PrimaryKey("PersonId")]
public class Person
{
    public int PersonId { get; set; }
    public int AddressId { get; set; }
}

[PrimaryKey("AddressId")]
public class Address
{
    public int AddressId { get; set; }
    public string Street { get; set; }
}

Let's say that we would like to retrieve all the Person records with the associated Address object. This is how it would work right now:

string sql = @"SELECT [Person].[PersonId], [Person].[AddressId],
        [Address].[AddressId], [Address].[Street]
        FROM [Person] INNER JOIN [Address] ON [Person].[AddressId] = [Address].[AddressId]";

var database = new Database(string.Empty);
IEnumerable<person> persons = database.Query<Person,Address>(sql);

This is how I'd wanted it to be:

var database = new Database(string.Empty);
IEnumerable persons = database.AutoQuery<Person,Address>(); 

To make it possible I created a few methods using PetaPoco's metadata classes. The main method is BuildSql which takes the type of the main poco (the root table) and an array of types of the pocos that shoud be joined to the main table. Here's the code:

public partial class Database
{
    public IList<T1> AutoQuery<T1, T2>()
    {
        string sql = BuildSql(typeof(T1), new[] { typeof(T2) });
        return Query<T1>(new[] { typeof(T1), typeof(T2) }, null, sql, null).ToList();
    }

    public string BuildSql(Type rootType, Type[] joinTypes)
    {
        PocoData rootData = PocoData.ForType(rootType);
        string rootTable = EscapeSqlIdentifier(rootData.TableInfo.TableName);

        IEnumerable<string> columns = GetColumns(rootData);
        string join = string.Empty;

        foreach (var joinType in joinTypes)
        {
            PocoData joinData = PocoData.ForType(joinType);
            columns = columns.Union(GetColumns(joinData));
            join += BuildJoin(rootTable, joinData);
        }

        string columnList = string.Join(", ", columns);

        return string.Format("SELECT {0} FROM {1}{2}",
                             columnList,
                             rootTable,
                             join);
    }

    private string BuildJoin(string rootTable, PocoData join)
    {
        string joinedTable = EscapeSqlIdentifier(join.TableInfo.TableName);
        string joinPk = EscapeSqlIdentifier(join.TableInfo.PrimaryKey);
        return string.Format(" INNER JOIN {0} ON {1}.{2} = {3}.{4}",
                             joinedTable,
                             rootTable,
                             joinPk,
                             joinedTable,
                             joinPk);
    }

    private IEnumerable<string> GetColumns(PocoData rootData)
    {
        var tableName = EscapeSqlIdentifier(rootData.TableInfo.TableName);

        var cols = from c in rootData.QueryColumns
                   select tableName + "." + EscapeSqlIdentifier(c);
        return cols;
    }
}

Basically what we are doing is generating the sql statement and using the Query method that is already available in PetaPoco. I think that this shows how easy it is to extend PetaPoco with your conventions. And what is even nicer is that you don't need to convice anyone to change PetaPoco, you can just add your changes locally.

Of course this is just a simple example. It's easy to futher evolve it to take additional paramters to specify a WHERE clause for the statement.

I hope this will give other people more ideas on how to extend PetaPoco to solve their own problems.