Unexpected behaviour with a multi-mapping query using Dapper.net

Question

I've only just started looking at Dapper.net and have just been experimenting with some different queries, one of which is producing weird results that i wouldn't expect.

I have 2 tables - Photos & PhotoCategories, of which are related on CategoryID

Photos Table

PhotoId (PK - int)  
CategoryId (FK - smallint)  
UserId (int)

PhotoCategories Table

CategoryId (PK - smallint)  
CategoryName (nvarchar(50))

My 2 classes:

public class Photo
{
    public int PhotoId { get; set; }
    public short CategoryId { get; set; }
    public int UserId { get; set; }
    public PhotoCategory PhotoCategory { get; set; }
}

public class PhotoCategory
{
    public short CategoryId { get; set; }
    public string CategoryName { get; set; }
{

I want to use multi-mapping to return an instance of Photo, with a populated instance of the related PhotoCategory.

var sql = @"select p.*, c.* from Photos p inner 
            join PhotoCategories c 
            on p.CategoryID = c.CategoryID where p.PhotoID = @pid";

cn.Open();
var myPhoto = cn.Query<Photo, PhotoCategory, Photo>(sql, 
               (photo, photoCategory) => { photo.PhotoCategory = photoCategory; 
                                           return photo; }, 
               new { pid = photoID }, null, true, splitOn: "CategoryID").Single();

When this is executed, not all of the properties are getting populated (despite the same names between the DB table and in my objects.

I noticed that if I don't 'select p.* etc.' in my SQL, and instead.

I explicitly state the fields.

I want to return EXCLUDING p.CategoryId from the query, then everything gets populated (except obviously the CategoryId against the Photo object which I've excluded from the select statement).

But i would expect to be able to include that field in the query, and have it, as well as all the other fields queried within the SQL, to get populated.

I could just exclude the CategoryId property from my Photo class, and always use Photo.PhotoCategory.CategoryId when i need the ID.

But in some cases I might not want to populate the PhotoCategory object when I get an instance of the Photo object.

Does anyone know why the above behavior is happening? Is this normal for Dapper?

Answer 1

I just committed a fix for this:

    class Foo1 
    {
        public int Id;
        public int BarId { get; set; }
    }

    class Bar1
    {
        public int BarId;
        public string Name { get; set; }
    }

    public void TestMultiMapperIsNotConfusedWithUnorderedCols()
    {

        var result = connection.Query<Foo1,Bar1,
                      Tuple<Foo1,Bar1>>(
                         "select 1 as Id, 2 as BarId, 3 as BarId, 'a' as Name",
                         (f,b) => Tuple.Create(f,b), splitOn: "BarId")
                         .First();

        result.Item1.Id.IsEqualTo(1);
        result.Item1.BarId.IsEqualTo(2);
        result.Item2.BarId.IsEqualTo(3);
        result.Item2.Name.IsEqualTo("a");

    }

The multi-mapper was getting confused if there was a field in the first type, that also happened to be in the second type ... AND ... was used as a split point.

To overcome now dapper allow for the Id field to show up anywhere in the first type. To illustrate.

Say we have:

classes: A{Id,FooId} B{FooId,Name}
splitOn: "FooId"
data: Id, FooId, FooId, Name

The old method of splitting was taking no account of the actual underlying type it was mapping. So ... it mapped Id => A and FooId, FooId, Name => B

The new method is aware of the props and fields in A. When it first encounters FooId in the stream it does not start a split, since it knows that A has a property called FooId which needs to be mapped, next time it sees FooId it will split, resulting in the expected results.