Dapper multiselect: Nested class primary key doesn't map unless using "AS"

Question

I have two classes:

class Foo{
    public int FooId { get; set; }
    ...
    public Bar Bar { get; set }
}

class Bar{
    public int BarId { get; set; }
    public int FooId { get; set }
    ...
}

when i then run the query like this:

sqlConnection.Query<Foo, Bar, Foo>(
    "SELECT * FROM Foo JOIN Bar ON Foo.FooId = Bar.FooId",
    (foo, bar) => { 
         foo.Bar = bar;
         return foo; 
       }, 
    splitOn: "FooId");

the result would then be that all properties on both Foo and Bar will map up Except for Bar.BarId. After checking the column name and type in the database against my Bar class I still couldn't find any differences.

One strange thing I stumbled upon was that if I wrote:

"SELECT *, BarId AS BarId FROM Foo JOIN Bar ON Foo.FooId = Bar.FooId"

Bar.BarId actually mapped as expected, have I misunderstood how to use Dapper or is this a bug?

Answer 1

It is trying to do a split on FooId, so every time it sees FooId it is cutting the data. This use-case is essentially intended for the (not uncommon) scenario where all the tables have a predictable key such as Id. In your case, this is not what you want, as you get from the database:

FooId, a, b, c | BarId, FooId, x, y, z
^^ from Foo ^^ | ^^ from Bar ^^

However, that splits on FooId as:

FooId, a, b, c, BarId | FooId, x, y, z

which is why BarId doesn't get included in the second object, and also why adding it to the end makes it work.

There is another usage, IIRC, that accepts the sequenced keys to split on; you would use:

splitOn: "FooId,BarId"