Dapper - Mapping multiple result sets that use table inheritance

dapper inheritance

Question

I have the following class structure and matching database structure that I'm trying to map. I have a design class that contains a list of elements. There can be 3 different types of elements: text, shape and image. I use an abstract class to maintain the common properties between the shapes and my database structure mirrors that.

My stored procedure returns two record sets, one with the designs and another with all the elements. The element record set uses left joins to return all elements along with their non-common properties. Each element has an ElementTyepID that determines what type of element it is. My design class contains a List. Is there a way to take that second result set and have it map to the correct sub class of Element based on the ElementTypeID?

public class Design
{
   public int DesignID {get;set;}
   public string DesignName {get;set;}
   public List<Element> ElementList {get;set;}
}

public class Element
{
   public int ElementID {get;set;}
   public int DesignID {get;set;}
   public int ElementTypeID {get;set;}
   public int Width {get;set;}
   public int Height {get;set;}
   public string Color {get;set;}
}

public class ElementText
{
   public int ElementTextID {get;set;}
   public int ElementID {get;set;}
   public string Font {get;set;}
   public string TextValue {get;set;}
}

public class ElementShape
{
   public int ElementShapeID {get;set;}
   public int ElementID {get;set;}
   public int ShapeType {get;set;}
}

public class ElementImage
{
  public int ElementImageID int {get;set;}
  public int ElementID {get;set;}
  public string ImageURL {get;set;}
}




Table: Design
-------------
DesignID int
DesignName varchar(50)


Table: Element
--------------
ElementID int
DesignID int
ElementTypeID int 0-Text, 1-Shape, 2-Image
Width int
Height int
Color varchar(10)


Table: ElementText
------------------
ElementTextID int
ElementID int
Font varchar(50)
TextValue varchar(50)



Table: ElementShape
-------------------
ElementShapeID int
ElementID int
ShapeType int



Table: ElementImage
-------------------
ElementImageID int
ElementID int
ImageURL varchar(255)



Create Table #Designs
(
  [DesignID] [int] NOT NULL,
  [DesignName] [varchar](50) NOT NULL,
)

-- Place all designs into a temp table
Insert Into #Designs
Select DesignID, DesignName from Design

-- Return 1st Result Set (Designs)
select * from #Designs

-- Return 2nd Result Set (Elements)
select * from Element as e
left Join ElementText as t on e.ElementID=t.ElementID
left Join ElementShape as s on e.ElementID=s.ElementID
left Join ElementImage as c on e.ElementID=c.ElementID
Where e.DesignID in (Select DesignID from #Designs)
Order By e.ElementID ASC

Expert Answer

This union approach isn't one that dapper supports directly. You might have some luck with the Query<…> api that accepts multiple generic type arguments - one per horizontal split - and use the final selector to.build the correct object. Alternatively you might be able to use the non-generic api and pull the columns out manually (although you may have problems if there are duplicated column names).

But for something automatic - the feature would need to be considered, designed, specified, implemented, tested, and supported. These things have not, for this scenario, happened yet.


Popular Answer

I don't see why the out-of-the-box multimap api wouldn't work. Because your element types don't inherit from any common class you need to simply return all of them in a tuple...You can subsequently evaluate whether or not they're null and decide what to do with them.

Query<Element,ElementText,ElementShape,ElementDesign,Tuple<Element,ElementText,ElementShape,ElementDesign>>(<sql>,
(e,et,es,ed)=>{return Tuple.Create(e,et,es,ed)}, spliton:"ElementID,ElementTextID,ElementShapeID,ElementDesignID");

I've been in a similar situation where we have 'MessageRecipients' that could be User,Account,Location objects and are differentiated by 'MessageRecipientType'.



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