Dapper map single record to parent and child

.net c# dapper

Question

I want to map a flat row to a simple parent/child object using Dapper.
I am unsure how to do this, here is a simple example of the tables, query and class to map to.

Project Table
id, name, status_a, status_b, status_c

Status Table
id, description

Query

select   
    prj.id,   
    prj.name,   
    a.description,  
    b.description,   
    c.description  
from project prj  
join status a on prj.status_a = a.id  
join status b on prj.status_b = b.id  
join status c on prj.status_c = c.id 

Class to map to

class Project
{
    public string Name { get; set; }
    public Status Status { get; set; }
}

class Status
{
    public string A { get; set; }
    public string B { get; set; }
    public string C { get; set; }
}

Popular Answer

It looks like you need to use Multi Mapping.

sqlConnection.Query<Project, Status, Project>(yourQueryString, 
     (project, status) => {
          project.Status = status;
          return project;
     },
     splitOn: "description");

Or something like that. I haven't tested this code out, but this is the basic idea.

sqlConnection.Query is basically telling Dapper that your sql query is going to return two objects (Project and Status). This is inferred by the first two types in the generic parameters. The third (Project) tells Dapper that this Dapper call should ultimately return a Project.

The first parameter is going to be your query, like normal. The second parameter is some function that will receive the project and status from your query, and expect you to return a project object. Here, we're just assigning the status to the project's Status property.

splitOn lets you tell Dapper "at what point in the sql query does the "Project" object end and the "Status" object begin.

select   
prj.id,   
prj.name,   
a.description,  
b.description,   
c.description  
from project prj  
join status a on prj.status_a = a.id  
join status b on prj.status_b = b.id  
join status c on prj.status_c = c.id 

You can see that your project object ends at prj.name and your status object begins at a.description.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why