I'm all new to Dapper and I'm trying to get my head around how it actually maps things. I have the following database structure:
calendar
| Id | Name |
meeting_room
| Id | Calendar_id | Capacity |
meeting_room_purpose
| id | Name | Description |
meeting_room_meeting_room_purpose
| id | room_id | purpose_id |
The 'calendar' table is much like an account. It can have multiple meeting rooms associated with it. A meeting room can have multiple purposes, which is the predicament of my question. I need to have an object like this:
class MeetingRoom {
public string Name { get; set; }
public int Capacity { get; set; }
public List<string> Purpose { get; set; }
}
So from calendar, I get the name, meeting_room the capacity and the list of purposes from all tuples with a purpose for the given room. My query is like this:
SELECT
calendar.id as CalendarId,
calendar.name as name,
meeting_room.capacity as capacity,
purpose
FROM calendar
LEFT JOIN meeting_room ON meeting_room.calendar_id=calendar.id
INNER JOIN
(SELECT
meeting_room_purpose.name as purpose
FROM
meeting_room_purpose
INNER JOIN meeting_room_meeting_room_purpose mrmrp ON
meeting_room_purpose.id=mrmrp.purpose_id
AND mrmrp.room_id=@Id)
a
WHERE meeting_room.id=@Id
The output of this query is a set of tuples, which differ only in purpose, e.g.
| CalendarId | name | capacity | purpose |
53 Charmander 6 Internal Meetings
53 Charmander 6 Marketing Meetings
53 Charmander 6 Sales Meetings
So, the meeting room "Charmander", has a capacity of 6 people, and can be used for 3 purposes. It should map to the object as (had it had a simple constructor):
new MeetingRoom("Charmander", 6, new List<string>(){"Internal Meetings", "Marketing Meetings", "Sales Meetings");
My dapper query is as:
_connection.Query<MeetingRoom, string, MeetingRoom>(sql,
(room, purpose) => {
room.Purpose = purpose; return room;
},
new { Id = query.Id }, splitOn: "id, purpose").SingleOrDefault();
If I remove splitOn i get an exception, and if I keep it I just get an empty (null) object. I'm sure it's not too hard, I just can't seem to get my "eurika" moment.
Best Regards
