How to map database table columns to class properties in Dapper .Net

Question

I have a table called employee and have several columns like EmpID,FirstName,MiddleName,LastName,Address,EmailID,RegionID,DesgID and I m using Dapper .Net and extensions to deal with SQL Server Database. I use Dapper SqlExtensions to insert, update, delete functionality and use Dapper multimapper option to fill the details. So I used below class for above employee table

    [Table("employee")] //To map the table to class
    public class EmployeeModel
    {
        [Key] //Denote primary key
        public Int32 EmpID { get; set; }
        [Column("FirstName")] //Trying to map table column FirstName to variable First (Fails)
        public string First { get; set; }
        public string MiddleName { get; set; }
        public string LastName { get; set; }
        public string Address { get; set; }
        public string EmailID { get; set; }
        public Int32 RegionID { get; set; }
        public RegionModel Region { get; set; }
        public DesignationModel Designation { get; set; }
        public Int32 DesgID { get; set; }

        public Int32 Add(EmployeeModel Details)
        {
            try
            {
                using (var connection = DBProviderFactory.GetOpenConnection()) //Creating IDbConnection Here
                {
                    return Convert.ToInt32(connection.Insert(Details)); //Using Dapper Extension To Insert New Employee Details
                }
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public Int32 Update(EmployeeModel Details)
        {
            try
            {
                using (var connection = DBProviderFactory.GetOpenConnection())
                {
                    return Convert.ToInt32(connection.Update(Details)); //Using Dapper Extension to Update Employee Details
                }
            }
            catch (Exception ex)
            {
                return -1;
            }
        }



        public IEnumerable<EmployeeModel> AllEmployees()
        {
            try
            {
                using (var connection = DBProviderFactory.GetOpenConnection())
                {
                    //Using multi mapper in Dapper to Fill All Employee Details such as region,state,country,designation details etc..
                    string query = @"select e.EmpID,e.FirstName,e.MiddleName,e.LastName,e.Address,e.EmailID
                                     ,r.RegionID as RID,r.Region,s.StateID,s.State,c.CountryID,c.Country,d.DesgID as DID,d.Designation
                                       from employee as e inner join region as r on e.RegionID=r.RegionID
                                           inner join state as s on r.StateID=s.StateID inner join country as c on 
                                               s.CountryID=c.CountryID inner join designation as d on e.DesgID=d.DesgID";
                    return connection.Query<EmployeeModel, RegionModel, StateModel, CountryModel,DesignationModel, EmployeeModel>(query,
                        (employee, region, state, country, designation) =>
                        {
                            employee.Region = region;
                            region.State = state;
                            state.Country = country;
                            employee.Designation = designation;
                            return employee;
                        }, splitOn: "RID,StateID,CountryID,DID");
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

    }

    public class EmployeeModelMapper : ClassMapper<EmployeeModel>
    {
        public EmployeeModelMapper()
        {
            Map(m => m.Region).Ignore();
            Map(m => m.Designation).Ignore();
            Map(m => m.First).Column("FirstName"); //Trying to map table column FirstName to variable First (Fails in the case of Multimapping)
            AutoMap();
        }
    }

In the above example, I m trying to Map table column FirstName to the class variable First but it fails in the case of running Query using Dapper connection.Query() refer the AllEmployees() method in the EmployeeModel class.

Also another option I tried using Dapper Mapper extension, that also can be found in the above code, refer EmployeeModelMapper class.

My Problem:

How to map all table columns to corresponding their class variables for common use in Dapper and extensions.

Answer 1

Dapper by default does not support property attributes such as Column and Key.

You have two options here, ether adjust your query to use the AS keyword to change the name of columns in the result set to match your property names which is the simplest option.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using Dapper;
using Newtonsoft.Json;
using Repository.DTO;

namespace Repository.Repositories
{
    public class EmployeeRepo
    {
        public IEnumerable<EmployeeModel> AllEmployees()
        {
            try
            {
                using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Read"].ConnectionString))
                {
                    const string query = @"select EmpID, FirstName [First] from employee";
                    return connection.Query<EmployeeModel>(query);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(JsonConvert.SerializeObject(e));
                throw;
            }
        }
    }
}

Or if this is not possible you can experiment with providing your own SqlMapper.ITypeMap implementation.

A whole bunch of people have already done this in many different ways. I personally am a fan of Dapper-FluentMap.

This would be your DTO. I have embedded the mapper config on the DTO for ease of config. Note you only need to list the columns in the mapping that are different from the column names that are returned. In our example, only the First property is needed as the EmpId property is the same name as the column in the result set.

using Dapper.FluentMap.Mapping;

namespace Repository.DTO
{
    public class EmployeeModel
    {
        public int EmpId { get; set; }

        public string First { get; set; }

        public class EmployeeModelMap : EntityMap<EmployeeModel>
        {
            public EmployeeModelMap()
            {
                Map(p => p.First).ToColumn("FirstName");
            }
        }
    }
}

The next step is to initialise your mapping.

using Dapper.FluentMap;
using Repository.DTO;

namespace Repository
{
    public class Bootstrap
    {
        public static void Map()
        {
            FluentMapper.Initialize(config =>
            {
                config.AddMap(new EmployeeModel.EmployeeModelMap());
            });
        }
    }
}

Then thats it.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using Dapper;
using Newtonsoft.Json;
using Repository.DTO;

namespace Repository.Repositories
{
    public class EmployeeRepo
    {
        public IEnumerable<EmployeeModel> AllEmployees()
        {
            try
            {
                using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Read"].ConnectionString))
                {
                    const string query = @"select EmpID, FirstName from employee";
                    return connection.Query<EmployeeModel>(query);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(JsonConvert.SerializeObject(e));
                throw;
            }
        }
    }
}

Answer 2

One easy way is to change your SQL select statement to use AS and it will map automagically.

So instead of:

string query = @"select e.EmpID,e.FirstName, ....

You just need

string query = @"select e.EmpID,e.FirstName as First, ....