Dapper Resolve Invalid cast exception

Question

I am using Dapper with MySql to get a list of uTeacher with multi-mapping but I can't get it to work! I get Invalid Cast exception from Int32 to uDepartement class and other classes!!

( DataException: Error parsing column 33 (Department=1 - Int32) )

I have the following Classes:

public class uTeacher : uPerson
{
        public uTeacher() { } 

        public uPosition Position { get; set; }
        
        public uFaculty Faculty { get; set; }

        public uDepartment Department { get; set; }

        public IList Modules { get; set; }
}

+--uTeacher------------+
| int Id
| ....
| uDepartment Department
| uPosition Position
| uFaculty Faculty
| IList Modules
+---------------------

+-uDepartment -+    +--uPosition --+   +--uFaculty --+   +--uModule--+
| int Id            | int Id           | int Id          | int Id
| ...               | ...              | ...             | ...
| IList Teachers    | IList Teachers   | IList Teachers  | uTeacher Teacher 
+--------------

My GetAll method:

public Task<IEnumerable<uTeacher>> GetAllAsync()
        {
            var select = $@"select * 
                        from uTeacher t
                        left join uDepartement d on d.Id = t.Department 
                        left join uPosition p on p.Id = t.Position
                        left join uFaculty f on f.Id = t.Faculty
                        left join uModule m on m.Teacher = t.Id ";    //many

            var result =
                Connection.QueryAsync<uTeacher, uDepartment, uPosition, uFaculty, uModule, uTeacher>(
                    select, (t, d, p, f, m) =>
                    {
                        t.Department = d;
                        t.Position = p;
                        t.Faculty = f;
                        t.Position = p;
                        t.Modules.Add(m);
                        return t;
                    }, splitOn: "Id,Id,Id");
            return result;
        }

I like using dapper but i'am stuck here, I've spent many hours on this and can't figure out what's going on.

Edit

I get the same exception when I run this without mapping

public Task<IEnumerable<uTeacher>> GetAllAsync(){
return Connection.QueryAsync<uTeacher>("Select * from univteacher");
}

!!

Answer 1

Dapper is seeing a column called Department, and is trying to populate that value into a property that is also called Department, which means that it is trying to put an int from the database into a property:

public uDepartment Department { get; set; }

which: won't work well. I'd probably suggest making the object match the database, i.e. something like:

public int Department { get; set; }

(and possibly a public uDepartment DepartmentObject property)

If you want the domain model to not do that: that's fine - but you might need a different object for talking to the database, i.e. your domain model and your database model don't need to be the same thing.