Tag Archives: Mapping

Matching Database Types

In order for SQL queries to perform as fast as possible, it is important to ensure that the types you use in your .NET code match the types you use in your database. This is especially important when you have indexes because if the data type of the SQL parameter doesn’t match the data type of the column, the index for that column won’t be used!

Consider the following table and class:

CREATE TABLE [dbo].[Customers]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Forename] NVARCHAR(50) NOT NULL,
    [Surname] NVARCHAR(50) NOT NULL,
    [DateOfBirth] DATETIME2 NOT NULL,
    [CustomerStatusId] INT NOT NULL
)
public class Customer
{
    public int Id { get; set; }
    public string Forename { get; set; }
    public string Surname { get; set; }
    public DateTime DateOfBirth { get; set; }
    public CustomerStatus Status { get; set; }
}

The default configuration of MicroLite is to map string to DbType.String (NVARCHAR) since .NET strings are unicode. If you don’t use unicode columns in your database, you should change the default type mapping for strings as follows:

// Map strings as ANSI instead of unicode
TypeConverter.RegisterTypeMapping(typeof(string), DbType.AnsiString);

Also, the default mapping for DateTime is to DbType.DateTime so if you use DATETIME2 columns, you should change the default mapping as follows:

// Always use DbType.DateTime2 instead of DbType.DateTime for System.DateTime
TypeConverter.RegisterTypeMapping(typeof(DateTime), DbType.DateTime2);

Note, in MicroLite 7 we plan to change the default DateTime mapping to DbType.DateTime2.

You can see the full set of default mappings Here

Advertisements

Understanding InvalidCastException

We can sometimes get an InvalidCastException thrown when reading a record from the database, the usual reason for this is that the database column does not match the property type on the class. For example, we have a column defined in the database as:

PhoneNumber int not null

and in the class we have

public string PhoneNumber { get; set; }

The reason this will fail is that MicroLite will read the value using the most efficient method and since the property is a string and IDataReader has a method GetString that will be called, essentially doing this:

obj.PhoneNumber = dataReader.GetString(idx);

However since the actual returned value in the IDataReader is an int which cannot be implicitly cast to a string we get an InvalidCastException. To resolve this, change the property type and database column to be compatible types (e.g. change the database column to string or change the class property to int).

MicroLite 6.0 Logging/Debugging

One of the things we focused on in MicroLite 6.0 is making the logging and debugging process even nicer for developers.

Since very early on MicroLite has had support for writing to your application log via the logging extension packages. However it has never been easy to see how a class has been mapped. To make this easier to understand, we have created an extension method for IObjectInfo which can be used to write the mapping to a TextWriter. We also write this to the log if the log is capturing Debug statements.

It’s as easy as this:

// Resolve the IObjectInfo for the mapped class.
var objectInfo = ObjectInfo.For(typeof(Customer));

// Write the mappings to the console
objectInfo.EmitMappingsToConsole();
// or write the mappings to a string
var stringWriter = new StringWriter()

// Write mappings to a TextWriter
objectInfo.EmitMappings(stringWriter);
var mappingDetails = stringWriter.ToString();

For the following class using the default conventions:

public class Customer
{
    public int Id { get; set; }
    public string Forename { get; set; }
    public string Surname { get; set; }
    public DateTime DateOfBirth { get; set; }
    public CustomerStatus Status { get; set; }
}

It will emit the following:

MicroLite Mapping:
——————
Class ‘MyApplication.Customer’ mapped to Table ‘Customers’

Property ‘DateOfBirth (System.DateTime)’ mapped to Column ‘DateOfBirth (DbType.DateTime)’
Allow Insert: True
Allow Update: True
Is Identifier: False

Property ‘Forename (System.String)’ mapped to Column ‘Forename (DbType.String)’
Allow Insert: True
Allow Update: True
Is Identifier: False

Property ‘Id (System.Int32)’ mapped to Column ‘Id (DbType.Int32)’
Allow Insert: False
Allow Update: False
Is Identifier: True
Identifier Strategy: DbGenerated

Property ‘Status (ConsoleApplication5.CustomerStatus)’ mapped to Column ‘CustomerStatusId (DbType.Int32)’
Allow Insert: True
Allow Update: True
Is Identifier: False

Property ‘Surname (System.String)’ mapped to Column ‘Surname (DbType.String)’
Allow Insert: True
Allow Update: True
Is Identifier: False

MicroLite 6.0 – Specifying the DbType

In MicroLite 6.0, we have exposed the ability to control the mapping between a .NET Type and the Database Type (DbType).

If not explicitly configured, the types will be mapped using the default mappings – you can see the default mappings here

If you use the Attribute based mapping and want to specify the DbType for a specific property, you can set it in the ColumnAttribute applied to that property:

[Column("DoB", DbType.DateTime2)]
public DateTime DateOfBirth { get; set; }

If you use convention based mapping and want to specify the DbType for a specific property, you can define that in your own convention:

        new ConventionMappingSettings
        {
            ResolveDbType = (PropertyInfo propertyInfo) =>
            {
                if (propertyInfo.PropertyType == typeof(DateTime) && propertyInfo.ReflectedType == typeof(Customer))
                {
                     return DbType.DateTime2;
                }

                // Use the default
                return TypeConverter.ResolveDbType(propertyInfo.PropertyType);
            }

If you always want to use a specific DbType for a .NET Type, you can set it via the TypeConverter class:

// Always use DbType.DateTime2 instead of DbType.DateTime for System.DateTime
TypeConverter.RegisterTypeMapping(typeof(DateTime), DbType.DateTime2);

MicroLite 5.0 – Changes to Convention Mapping

In MicroLite 5.0, we are making further enhancements to the convention based mapping which provide even greater flexibility.

Convention Mapping History:

The changes in MicroLite 5.0 are as follows:

ResolveIdentifierStrategy

We have replaced the IdentifierStrategy property with the ResolveIdentifierStrategy method. This means that you no longer have to use the same IdentifierStrategy for every class (or that you can migrate from attribute mapping to convention mapping if this was preventing you from doing so).

By default it will return IdentifierStrategy.DbGenerated but you can override it if you want to specify a different strategy or calculate it based upon the type:

Configure.Extensions().WithConventionBasedMapping(
    new ConventionMappingSettings
    {
        ResolveIdentifierStrategy = (Type type) => 
        {
            if (type == typeof(MySpecialType))
            {
                return IdentifierStrategy.Assigned;
            }

            return IdentifierStrategy.DbGenerated; 
        }
    });

ResolveTableSchema

We have replaced the TableSchema property with the ResolveTableSchema method. This means that you no longer have to use the same schema for every class (or that you can migrate from attribute mapping to convention mapping if this was preventing you from doing so).

By default it will return null but you can override it if you want to specify a specific schema or calculate it based upon the type:

Configure.Extensions().WithConventionBasedMapping(
    new ConventionMappingSettings
    {
        ResolveTableSchema = (Type type) => 
        {
            if (type == typeof(MySpecialType))
            {
                return "MySpecialSchema";
            }

            return "dbo"; 
        }
    });

MicroLite 5.0 – Changes to IdentifierStrategy

One of the changes coming in MicroLite 5.0 is the removal of the IdentifierStrategy options for Guid and GuidComb.

If you currently use either of them, you can maintain the functionality by using the Assigned IdentifierStrategy and creating a custom IListener to set the value.

using System;
using MicroLite.Listeners;
using MicroLite.Mapping;

public class GuidListener : Listener
{
    public override void BeforeInsert(object instance)
    {
        var objectInfo = ObjectInfo.For(instance.GetType());

        if (objectInfo.TableInfo.IdentifierColumn.PropertyInfo.PropertyType == typeof(Guid))
        {
            var identifier = Guid.NewGuid();

            objectInfo.SetIdentifierValue(instance, identifier);
        }
    }
}

If you used the GuidComb strategy, you can find the code to generate the next GUID here and use this line instead:

var identifier = GuidGenerator.CreateComb();

MicroLite 4.0 – Convention Based Mapping Enhancements

In MicroLite 3.0, we introduced convention based mapping and with MicroLite 4.0 we are changing to make convention based mapping the default. Also, a number of enhancements have been made to allow your conventions to be specified.

The ConventionMappingSettings class now has a number of extra properties which can be configured to define your own conventions. The default values for these are backwards compatible with the conventions defined in MicroLite 3.0.

AllowInsert

The AllowInsert property is a Func<PropertyInfo, bool> which can be used to specify whether a property value can be inserted (the equivalent of setting AllowInsert = false on the ColumnAttribute if you were using attribute mapping). By default, this method will always return true, however if you had an audit property with updated date, you could configure the settings as follows:

Configure.Extensions().WithConventionBasedMapping(
    new ConventionMappingSettings
    {
        AllowInsert = (PropertyInfo propertyInfo) => 
        {
            return propertyInfo.Name != "Updated"; 
        }
    });

AllowUpdate

The AllowUpdate property defines a Func<PropertyInfo, bool> which can be used to specify whether a property value can be updated after it is first inserted (the equivalent of setting AllowUpdate = false on the ColumnAttribute if you were using attribute mapping). By default, this method will always return true, however if you had an audit property with created date which should not be changed, you could configure the settings as follows:

Configure.Extensions().WithConventionBasedMapping(
    new ConventionMappingSettings
    {
        AllowUpdate = (PropertyInfo propertyInfo) => 
        {
            return propertyInfo.Name != "Created"; 
        }
    });

Ignore

The Ignore property defines a Func<PropertyInfo, bool> which can be used to specify whether a property which would ordinarily be mapped should be ignored (the equivalent of not applying a ColumnAttribute if you were using attribute mapping). By default, this method will always return false.

IsIdentifier

The IsIdentifier property defines a Func<PropertyInfo, bool> which can be used to specify whether a property is the property mapped to the identifier column in the database table. By default, this method will return true if the property name is Id or {ClassName} + Id.

ResolveColumnName

The ResolveColumnName property defines a Func<PropertyInfo, string> which can be used to resolve the name of the column a property is mapped to. By default, the property name will be used unless the property is an enum in which case {EnumType} + Id will be used.

Example public string Forename would resolve to Forename whereas public CustomerStatus CustomerStatus would resolve to CustomerStatusId.

ResolveIdentifierColumnName

The ResolveIdentifierColumnName property defines a Func<PropertyInfo, string> which can be used to resolve the name of the column the identifier property is mapped to. This function will only be invoked if the IsIdentifier function returns true. By default, this will return the property name.

Example public int Id would resolve to Id so the expected column name in the database would be Id.

If your database schema defines the identifier column as TableName + Id (e.g. CustomerId) but in your code you want to use public int Id, you could implement the method as follows:

Configure.Extensions().WithConventionBasedMapping(
    new ConventionMappingSettings
    {
        ResolveIdentifierColumnName = (PropertyInfo propertyInfo) => 
        {
            return propertyInfo.DeclaringType.Name + "Id"; 
        }
    });