PetaPoco – Convention Based Fluent Mapping

After using the Fluent Mapping’s I blogged about a few months ago, I started to see patterns occurring. My table names were always the pluralized type and the primary key was the Type name concatenated with “Id”. With all this repetition, I decided that conventional mappings were needed and that they should be overridable for the edge case.

Here is the simplest example.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
    });
}

This just tells the FluentMapping to scan the assembly with the type MvcApplication in it and any class it finds, define a mapping so that the object will can be persisted to PetaPoco. Now by itself this is pretty pointless because by default PetaPoco already does this for you on the fly. It will use the same defaults PetaPoco currently invokes which is

  1. TableName = Type name
  2. PrimaryKey = “ID”
  3. PrimaryKeyAutoIncrement = on

You would also not want add every type in your project either as most of them will not be used for database.

Ok, so we don’t want all the types, but we want to filter them by their namespace. All my DB models are in Models/Db, which has the namespace MvcApplication.Models.Db so we will use this as the filter.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
    });
}

The IncludeTypes method takes in a lambda expression with the Type as the argument and returns a bool. This method can also be used to exclude types.

So with all our DB models now being mapped using defaults, lets change them. Say that all our tables have the prefix “t_” on them because our DB admin likes hungarian notation. He also wants the primary key to auto increment (identity column) and the column name to end with “Id”. Ok mister Db man….you’re the boss.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.TablesNamed(type => "t_" + type);
        x.PrimaryKeysNamed(type => type + "Id");
        x.PrimaryKeysAutoIncremented(type => true);
    });
}

Too easy.

Next we can configure how columns are mapped. By default the column name will map to the property name, but it doesn’t have to. Our Db man says all columns should have the “d_” prefix if the column is a DateTime property, and the rest should remain the same as the property name.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.TablesNamed(type => "t_" + type);
        x.PrimaryKeysNamed(type => type + "Id");
        x.PrimaryKeysAutoIncremented(type => true);
        x.Columns.Named(prop =>
            prop.PropertyType == typeof(DateTime) ? "d_" + prop.Name : prop.Name);
    });
}

I think you get the idea. You can also Ignore properties, set a Version columns and set a Result column.

There are also two extension methods that set some smart defaults. These are:

  1. Tables are the pluralized version of the Type name
  2. PrimaryKeys are the Type name concatenated with “Id”
  3. All complex properties (classes) are ignored

These can be set simply like this.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.WithSmartConventions();
    });
}

You can create your own extension methods as well. They are extremely easy. Check out the code for the WithSmartConventions to see how easy it is.

There is one last thing. Conventions are great, but if you can’t override them then they’re pretty useless, because there is always an exception to the rule. Mr Db man comes to me and says that there is one table which is used by 5 systems and does not play by our conventions we have setup. The Type name is Abc, but the table name needs to be “Abcies” not “Abcs”, and the primary key is “MyAbcId”. He also doesn’t want to map the Name column.

protected void Application_Start()
{
    FluentMappingConfiguration.Scan(x =>
    {
        x.Assembly(typeof(MvcApplication).Assembly);
        x.IncludeTypes(type => type.Namespace == "MvcApplication.Models.Db");
        x.TablesNamed(type => "t_" + type);
        x.PrimaryKeysNamed(type => type + "Id");
        x.PrimaryKeysAutoIncremented(type => true);
        x.Columns.Named(prop =>
            prop.PropertyType == typeof(DateTime) ? "d_" + prop.Name : prop.Name);

        x.OverrideMappingsWith(new MappingOverrides());
    });
}

public class MappingOverrides : Mappings
{
    public MappingOverrides()
    {
        For<Abc>().TableName("Abcies")
                    .PrimaryKey("MyAbcId")
                    .Columns(x => x.Column(y=>y.Name).Ignore());
    }
}

Any mappings defined in our mapping overrides will be overlayed on top of our conventions. We covered 99% of our mappings with these conventions but still needed to manually configure one table.

I hope you can see how powerful this can be, especially when you’re got more than 100 tables.

This is currently only available on my branch, which can be found https://github.com/schotime/PetaPoco and downloaded Schotime-PetaPoco-4.0.3.11.zip.

Let me know how you get on or if you have any issues.

Adam

3 Responses to PetaPoco – Convention Based Fluent Mapping

  1. In your last code block this line:

    x.OverrideMappingsWith(new PetaPocoMappings());

    should likely be replaced by

    x.OverrideMappingsWith(new MappingOverrides());

  2. Vincent says:

    Composite keys no longer working?

    I downloaded the latest version from you github, followed the syntax of separating the Primarykey properties with a comma, but it doesn’t seem to work?

  3. Schotime says:

    check out NPoco on nuget

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>