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");
}

!!

I've updated the Question, the uPerson Class is a base class — iProg, Sep 17 '20 at 13:39

score 2 · Accepted Answer · answered Sep 17 '20 at 13:43

2

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.

answered Sep 17 '20 at 13:43

Marc Gravell

1,026,079
266
2,566
2,900

Thank you, I think I get that. I need to create a database model for dapper! but how can I retrieve it with my domain model? – iProg Sep 17 '20 at 13:48
@iProg you'd need to have your own code to translate between the two models – Marc Gravell Sep 17 '20 at 13:51
I thought that maybe I don't have to do that with dapper but now I'll have to do it. Thank you – iProg Sep 17 '20 at 14:01

Dapper Resolve Invalid cast exception

1 Answers1