How do I return an object (with another object within) from a join table in SQL using C# and Dapper?

Question

The problem I have, is that I am currently trying to get a list of objects with multi-mapping. I want get a list of Object A, that has Object B within, and both are saved separately in my database. Below are my objects and Tables.

Objects are:

// Object A
public class TestObject
{

    public int objectId { get; set; }
    public string name { get; set; }
    public ObjectDetail detailId { get; set; }
}

and

// Object B
public class ObjectDetail
{
        public int detailId { get; set; }
        public string owner { get; set; }
        public string description { get; set; }       

}

My tables are dbo.testobject with columns "objectId" (primary key int), "name" (nvarchar), "detailId" (int); and dbo.objectdetail with columns "detailId" (primary key int), "owner" (nvarchar), "description" (nvarchar).

So to begin, I have no problems inserting rows into the tables, matching the keys, and querying a single object of TestObject, able to call out all the details including the ones inside the matching ObjectDetail.

However, I am stuck on trying to get a list of TestObject.

What I have done initially, is the code below.

string sql = $"select ob.*, de.* " +
    $"from dbo.testobject ob " +
    $"left join dbo.objectdetail de " +
    $"on ob.detailId = de.detailId ";

using (IDbConnection connection)
{
    var selected = connection.Query<TestObject, ObjectDetail, TestObject>(sql,
        (test, detail) => { test.detailId = detail; return test; },
        splitOn: "detailId");
}

Which returns the error:

System.Data.DataException: 'Error parsing column 2 (detailId=8 - Int32)' and an Inner Exception ofInvalidCastException: Invalid cast from 'System.Int32' to 'SQLDataAccessDemo.ObjectDetail'.

So I guessed that it might be because of the "detailId" column in dbo.testobject does not match the datatype of detailId in the class TestObject. So then I went back and played around with the code making the changes below.

// I first made changes to the classes
// TestObject is now int Id, string name, ObjectDetail detail
// ObjectDetail object is now int Id, string owner, string description

string sql = $"select ob.*, de.* " +
    $"from dbo.testobject ob " +
    $"left join dbo.objectdetail de " +
    $"on ob.detailId = de.Id ";

using (IDbConnection connection)
{
    var selected = connection.Query<TestObject, ObjectDetail, TestObject>(sql,
        (test, detail) => { test.detail = detail; return test; });

}

Which works and doesn't give the error.

So my question is, how do I make it work without having to change my initial tables?

Answer 1

Would suggest that you should not name the object property with postfix Id. Hence your TestObject class should contain a new property: ObjectDetail that holds the object value.

And please use Pascal case to name the properties as following C# naming conventions.

public class TestObject
{
    public int ObjectId { get; set; }
    public string Name { get; set; }
    public int DetailId  { get; set; }
    public ObjectDetail ObjectDetail { get; set; }
}

public class ObjectDetail
{
    public int DetailId { get; set; }
    public string Owner { get; set; }
    public string Description { get; set; }       
}

In your Dapper query, works with splitOn:

string sql = $"select ob.*, de.* " +
    $"from dbo.testobject ob " +
    $"left join dbo.objectdetail de " +
    $"on ob.detailId = de.Id ";

using (IDbConnection connection)
{
    var selected = connection.Query<TestObject, ObjectDetail, TestObject>(sql,
        (test, detail) => 
        { 
            detail.DetailId = test.DetailId;
            test.ObjectDetail = detail; 
            return test; 
        }, 
        splitOn: "detailId");
}