Dapper query result sets Pk of table as null in objects

Question

I am trying to retrieve data from mysql database using dapper but the result sets id (primary key) and foreign key as nulls. Other attributes have values.

I tried to change sql query from select * from courses to full form as select id,name,did from courses.

    Course{
        public Course()
        {

        }
        public string Id { get; set; }
        public string Title { get; set; }
        public int Credits { get; set; }
        public bool Is_Elective { get; set; }
        public string DId { get; set; }
        public int Sem { get; set; }

    }

   class CourseDAO
    {
        private readonly MySqlConnection conn;
        private string connectionString = "Server=localhost;Database=university;Uid=root;Pwd=*****;";

        public CourseDAO()
        {
            conn = new MySqlConnection(connectionString);
        }

        public List<Course> getAll()
        {
            string sql = "select * from university.course";
            List<Course> courses = conn.Query<Course>(@sql).ToList();
            return courses;
       }
   }

Expected: Courses list have all courses from db with correct values.

Actual Courses list has all courses from db with id and did as null and rest have values.

You have incorrect property names in your class. Change Id to course_id and DId to department_id or vice versa. By default dapper tries to apply values by name (using ignore case strategy). — Maxim Goncharuk, Mar 30 '19 at 17:14

Risto M · Accepted Answer · 2019-04-02T04:24:20.110

Even if issue was solved in question comments by Maxim, I'd like to describe problem with few solution alternatives.

Problem cause:

Dapper executes mapping from sql query result to object by name. Sql query result field 'title' is automatically mapped to Course.Title (mapping is case-insensitive).

In your case there was two name-mismatches between db columns vs. C# properties: (course_id != Id and department_id != DId), therefore Dapper was unable to map those fields.

Solution 1, sql column aliases

You can list table columns with possible columns aliases in sql query following way:

string sql = "select course_id Ad Id, title, credits, Is_elective, department_id as DId, sem from university.course";

Using explicit column names in sql, Dapper can execute automatic name-based mappings.

Solution 2, Dapper custom mappings

Dapper Custom mapping is the feature to manually define, for each object, which column is mapped to which property.

Here is class which deal with the mappings (idea for this both-ways mapping borrowed from another SO answer):

    public class ColumnMap
    {
        private readonly Dictionary<string, string> mappings = new Dictionary<string, string>();

        public void Add(string t1, string t2)
        {
            mappings.Add(t1, t2);
        }

        public string this[string index]
        {
            get
            {
                // Check for a custom column map.
                if (forward.ContainsKey(index))
                    return forward[index];
                if (reverse.ContainsKey(index))
                    return reverse[index];

                // If no custom mapping exists, return the value passed in.
                return index;
            }
        }
    }

Setup the ColumnMap object and tell Dapper to use the mapping.

var columnMap = new ColumnMap();
columnMap.Add("Id", "course_id");
columnMap.Add("DId", "department_id");

SqlMapper.SetTypeMap(typeof (Course), new CustomPropertyTypeMap(typeof (Course), (type, columnName) => type.GetProperty(columnMap[columnName])));

Solution 3, dynamic type and LINQ You can execute field mapping using dynamic object as following:

string sql = "select * from university.course";
List<Course> courses = conn.Query<dynamic>(@sql)
   .Select(item => new Course()
   {
     Id = item.course_id,
     Title = item.title,
     Credits = item.credits,
     Is_Elective = item.Is_elective,
     DId = department_id,
     Sem = sem
   })
   .ToList();

Dapper query result sets Pk of table as null in objects

1 Answers1