Property value mappings

FastCrud passes the task of performing value conversions between the .NET and the DB world to Dapper. To give you an idea of what to expect from these conversions, take a look at this chart. Bear in mind though that this might not reflect accurately the default conversions used by Dapper.

We do recommend that you stick with the most common primitives when it comes to designing your database or setting up the types for the properties on your database entities. These entities should be as basic as possible. They should be exposed by your data access layer and should be used by the business layer which holds the logic responsible for transforming them further into domain objects.

Let's pick up an example. Let's assume that we have a Validity column in the database, of type bigint, that represents a time span in milliseconds. The recommended way to set up our entity would be:

    [Table("Badges")]
    public class BadgeDbEntity
    {
        [Key]
        public int BadgeId { get; set; }
        public string Barcode { get; set; }
        [Column("Validity")]
        public long ValidityMilliseconds { get; set; }
    }

We have a clear one-to-one type mapping with the database type. And yes, specifying the measuring unit in the property name is very much encouraged at this level.

But let's say we insist on making our life difficult and have a TimeSpan property mapped to our column:

    [Table("Badges")]
    public class BadgeDbEntity
    {
        [Key]
        public int BadgeId { get; set; }
        public string Barcode { get; set; }
        public TimeSpan Validity { get; set; }
    }

If we used the entity in this way, we're already in trouble. Dapper by default is mapping the TimeSpan type to the Db type time, while our column is of type bigint. Now at this point you might be tempted to change the schema of your table and set up the Validity column to be of type time, however keep in mind that time has a maximum value of 23:59:59.9999999, so you can easily get an OverflowException, as TimeSpan has a much greater range.

So what can we do to map a TimeSpan value to a bigint data type IF we've decided that this convention should be used EVERYWHERE in our DAL? Well, we're gonna have to remove the default Dapper type mapping and provide our own:

Dapper.SqlMapper.RemoveTypeMap(typeof(TimeSpan));
Dapper.SqlMapper.AddTypeHandler(new TimeSpanHandler());

public class TimeSpanHandler: SqlMapper.TypeHandler<TimeSpan>
{
        /// <summary>
        /// Assign the value of a parameter before a command executes
        /// </summary>
        /// <param name="parameter">The parameter to configure</param>
        /// <param name="value">Parameter value</param>
        public override void SetValue(IDbDataParameter parameter, TimeSpan value)
        {
            parameter.DbType = DbType.Int64;
            parameter.Value = (long)value.TotalMilliseconds;
        }

        /// <summary>Parse a database value back to a typed value</summary>
        /// <param name="value">The value from the database</param>
        /// <returns>The typed value</returns>
        public override TimeSpan Parse(object value)
        {
            return TimeSpan.FromMilliseconds((long)value);
        }
}

DateOnly and TimeOnly

Another good use of a type handler is when Dapper is going back and forth with the support for these two "new" .NET 6 types. And who can blame it when the DB providers are not playing ball.

We've added tests on our side and so far it seems that the most reliable support for these two types across the board can be achieved by following these steps.

Add the following two type handlers to your project:

• DateOnlyTypeHandler
• TimeOnlyTypeHandler

Add the following lines at the start of your app:

  SqlMapper.RemoveTypeMap(typeof(DateOnly));
  SqlMapper.AddTypeHandler(new DateOnlyTypeHandler());
  
  SqlMapper.RemoveTypeMap(typeof(DateOnly?));
  SqlMapper.AddTypeHandler(new NullableDateOnlyTypeHandler());
  
  SqlMapper.RemoveTypeMap(typeof(TimeOnly));
  SqlMapper.AddTypeHandler(new TimeOnlyTypeHandler());
  
  SqlMapper.RemoveTypeMap(typeof(TimeOnly?));
  SqlMapper.AddTypeHandler(new NullableTimeOnlyTypeHandler());