CODEWITHSUNDEEP
c#sqlwpfdapper
linus72982
linus72982

Reputation: 1393

Dapper mapping throws invalid cast

Two tables:

+-Person-+       +--Rank--+
| Id     |       | Id     |
| FName  |       | Name   |
| LName  |       +--------+
| Rank   |
+--------+

The object model looks the exact same except Rank in Person is of type RankModel instead of int.

I do a simple inner join on them:

string sql = "SELECT pe.Id, pe.LName, pe.FName, pe.Rank, ra.Id, ra.Name" +
             "FROM Person pe INNER JOIN Rank ra ON ra.Id = pe.Rank";

Then I use Dapper to map:

return connection.Query<PersonModel, RankModel, PersonModel>(sql, (per, rank) =>
    {
    per.Rank = rank;
    return per;
    }).ToList();

But I get an exception:

InvalidCastException: Invalid cast from 'System.Int16' to 'MainDB.Models.RankModel'.

Clearly it's trying to cast an int to a RankModel, but I can't figure out why -- it should be splitting the return into two objects and then attaching the RankModel object into the Rank property of the PersonModel object. This is driving me crazy, I've spent 3 hours on this and can't figure out what's going on.

Upvotes: 0

Views: 1351

Answers (1)

Cinchoo
Cinchoo

Reputation: 6322

You need to add 'splitOn' param in query statement in order to split them into Person, Rank model objects.

Also your sql needs to be refactored as below

string sql = "SELECT pe.Id, pe.LName, pe.FName, ra.Id, ra.Name" +
             "FROM Person pe INNER JOIN Rank ra ON ra.Id = pe.Rank";

And your statement should be

return connection.Query<PersonModel, RankModel, PersonModel>(sql, (per, rank) =>
    {
    per.Rank = rank;
    return per;
    }, splitOn:"Id,Id").ToList();

Upvotes: 2

Related Questions