Version 3

Example Classes and Tables

public class User {
  public int UserId { get;set; }
  public int Name { get;set; }
  public Address Address { get;set; }              // No DB column. Uses UserId
  public Country CountryOfBirth { get;set; }       // CountryId
  public List<Permission> Permissions { get;set; } // No DB column. Uses UserId
  public dynamic ExtraDetails { get;set; }
}

public class Address {
  public int UserId { get;set; }
  public int StreetNo { get;set; }
  public string StreetName { get;set; }
}

public class Country {
  public int CountryId { get;set; }
  public string Name { get;set; }
}

public class Permission {
  public int PermissionId { get;set; }
  public int UserId { get;set; }
  public int Level { get;set; }
}

Each class is represented by a table. Each property is represented by a column in the database with the same name.

Breaking Changes and Migrating path

Mappers

Removed the following methods as they didn't really relate mapping data from or to the database.

• GetTableInfo(Type t, TableInfo ti)
  Things in GetTableInfo can usually be done with fluent mappings or attributes.
• MapMemberToColumn(MemberInfo pi, ref string columnName, ref bool resultColumn)
  Use the attributes Column or ResultColumn

The Mapper property on Database is now a MapperCollection and multiple mappers can be added and they will be processed in the order they are added. If a mapper returns null it will continue on to the next one.

Nested object mapping

Currently there are overloads to Fetch which you can provide extra generic parameters to, that enable you to map to nested classes. In V3 this is enabled by default if a few different ways.

1> By Naming convention (the double underscore "__")
To map the User class defined above we can do the following.

// Assumes address is stored on users
db.Fetch<User>("select userid, name, streetno AS address__streetno, streetname AS address__streetname from users");
// When its not
db.Fetch<User>(@"select userid, name, streetno AS address__streetno, streetname AS address__streetname
                 from users u inner join addresses a on u.userid = a.userid");

2> Using a null alias

db.Fetch<User>(@"select u.*, null npoco_address, a.*
                 from users u inner join addresses a on u.userid = a.userid");

This will prepend "address__" to all the aliases following the column.

3> Using the old convention.
If the property is not found on the first class then check the next class. Once the class has been skipped it will not go backwards. If the property has already been mapped on a class it will assume its for the next class and skip to the next as well. The way it orders the classes is a by a depth first traversal. So the user class would be represented like so.

1. User
2. Address

If Address had any properties that were classes it would be next.

IMPORTANT In V2 it was the order of the generic arguments that mattered when doing the query. They had to match with the columns.
In V3 it is the order of the properties (depth first).

//v2
db.Fetch<User, Address>(@"select u.*, a.*
                 from users u inner join addresses a on u.userid = a.userid");
//v3
db.Fetch<User>(@"select u.*, a.*
                 from users u inner join addresses a on u.userid = a.userid");

• This will look for the first db column userid on User. It finds it so it moves to the next column.
• It will then look for the db column name on User. It finds it there too so it moves to the next column.
• It will then look for the db column userid on User. It finds it, but it has already been mapped so it moves to the next class which is Address. It finds it here, so its mapped and moves to the next db column. (Note: no more columns can be mapped to User now as we have mapped to a column to Address which is after User in the list)
• It will then look for the db column streetno on Address. It finds it so it moves to the next column.
• It will then look for the db column streetname on Address. It finds it and since there are no more columns to map, it finishes.

So in case 2, the way it defines the order is explicit, where as this is not. If you fail to get the correct results using 3, then resort to 2.

If you have been using the [ResultColumn] attribute on a property referencing a nested object (Address in the example above), you will additionally need the [ComplexMapping] attribute in v3 as well.

Paging column

When paging, automatic conversion of a query to a paged query happens, an extra column poco_rn is created. If you try and run a generic query like

db.Page<object[]>("select u.* from users u");

The first column in V2 would have been the row number. This is now omitted in the results and cannot be mapped to.

New Features

Linq Provider

• QueryBuilder
  Provides ability to reuse Where, OrderBy and Limit across queries.
• ToDynamicList
  Maps to List
• ToArray
• Where
  New overload which takes a sql string
  Another which provides a context object to retrieve aliases automatically assigned by the query
• UsingAlias
  Aliases can be defined for the root object and also for the Includes
• Include, IncludeMany Left join by default but this can now be overridden.

Mapping

• Enum to string and vica-versa by default. ColumnType just needs to be set to string via attribute or Fluent mapping.
• Better errors for Enum to string mappings that fail.
• Add IDbCommand parameter to Mapper.GetParameterConverter method
• Multiple mappers allowed.

Batch Insert

• Ability to insert data in batches. Note that this is not bulk insert. This joins insert statements together such that only one call to the database happens per batch. The default batch size is 20, however this can be configured. Not that primary keys will not be re-hydrated into the objects. Its basically fire and forget.

Relationships

• Many-to-one (Foreign Key -> Primary Key)

[Reference(ReferenceType.Foreign, ColumnName = "CountryId", ReferenceMemberName = "CountryId")]
public Country CountryOfBirth { get;set; }

Foreign references are:
-- available for includes when writing LINQ queries.
-- insert/update referenced primary keys when inserting (CountryId in this case)

• One-to-one (Primary Key -> Primary Key)

[Reference(ReferenceType.OneToOne, ColumnName = "UserId", ReferenceMemberName = "UserId")]
public Address Address { get;set; }

One to One references are:
-- available for includes when writing LINQ queries
-- a way to have both the object and foreign key value on the object (both the int CountryId and Country CountryOfBirth can be present)

• One-to-many (Primary Key -> Primary Keys/Foreign Keys)

[Reference(ReferenceType.Many, ColumnName = "UserId", ReferenceMemberName = "UserId")]
public List<Permission> Permissions { get;set; }

One to Many references are:
-- available for 1 IncludeMany when writing LINQ queries
-- available to use in db.FetchOneToMany

Interfaces and Abstract classes

• You can map to interfaces and abstract classes by specifying a factory on how to create them. You will get passed the IDataReader for the current line.

public class Post : ContentBase { }
public class Answer : ContentBase { }
public abstract class ContentBase {
    public string Name { get; set; }
    public string Type { get; set; }
}

db.Mappers.RegisterFactory<ContentBase>(reader => {
    var type = (string)reader["type"];
    if (type == "Post")
        return new Post();
    if (type == "Answer")
        return new Answer();
    return null;
});

var data = db.Fetch<ContentBase>(@"
  select 'NamePost' Name, 'Post' type
  union
  select 'NameAnswer' Name, 'Answer' type
").ToList();

Interceptors

Previously there was only 'On' methods that were overrideable on the Database object. In V3 we've introduced 5 interfaces which can be used kind of like filters in MVC. They allow you to hook into the events without subclassing Database.

The interfaces are:

public interface IExecutingInterceptor : IInterceptor
{
    void OnExecutingCommand(IDatabase database, IDbCommand cmd);
    void OnExecutedCommand(IDatabase database, IDbCommand cmd);
}

public interface IConnectionInterceptor : IInterceptor
{
    IDbConnection OnConnectionOpened(IDatabase database, IDbConnection conn);
    void OnConnectionClosing(IDatabase database, IDbConnection conn);
}

public interface IExceptionInterceptor : IInterceptor
{
    void OnException(IDatabase database, Exception x);
}

public interface IDataInterceptor : IInterceptor
{
    bool OnInserting(IDatabase database, InsertContext insertContext);
    bool OnUpdating(IDatabase database, UpdateContext updateContext);
    bool OnDeleting(IDatabase database, DeleteContext deleteContext);
}

public interface ITransactionInterceptor : IInterceptor
{
    void OnBeginTransaction(IDatabase database);
    void OnAbortTransaction(IDatabase database);
    void OnCompleteTransaction(IDatabase database);
}

All you have to do is implement one of these interfaces and then register it through the DatabaseFactory using the WithInterceptor(IInterceptor interceptor) method or add it directly to the Interceptors list on the Database object. They will be run in the order they are registered.

Data

There is now a Data property (Dictionary<string, object>) on the Database object which can be used to store information for the lifetime of the Database object. Usually 1 per request in a web scenario.

Dynamic Properties

You can now have properties with the type Dictionary<string, object> or dynamic and these will get mapped using the "__" convention.

var user = db.Fetch<User>("select u.*, 25 AS ExtraDetails__Age from users").First();
Assert.Equal(user.ExtraDetails.Age, 25);