Dapper intermediate mapping
c#, dapper, orm, sql-server
Solution
Dapper supports Multi Mapping, for documentation see: http://code.google.com/p/dapper-dot-net/
Here is one of the examples from one of the projects I'm currently working on:
var accounts2 = DbConnection.Query<Account, Branch, Application, Account>(
"select Accounts.*, SplitAccount = '', Branches.*, SplitBranch = '', Applications.*" +
" from Accounts" +
" join Branches" +
" on Accounts.BranchId = Branches.BranchId" +
" join Applications" +
" on Accounts.ApplicationId = Applications.ApplicationId" +
" where Accounts.AccountId <> 0",
(account, branch, application) =>
{
account.Branch = branch;
account.Application = application;
return account;
}, splitOn: "SplitAccount, SplitBranch"
).AsQueryable();
The trick is to use the splitOn option, to divide record-set into multiple objects.
You can also check my question to see class structure for the above example: Dapper Multi-mapping Issue
Problem
Slightly more advanced mapping then in my previous question :) Tables: ``` create table [Primary] ( Id int not null, CustomerId int not null, CustomerName varchar(60) not null, Date datetime default getdate(), constraint PK_Primary primary key (Id) ) create table Secondary( PrimaryId int not null, Id int not null, Date datetime default getdate(), constraint PK_Secondary primary key (PrimaryId, Id), constraint FK_Secondary_Primary foreign key (PrimaryId) references [Primary] (Id) ) create table Tertiary( PrimaryId int not null, SecondaryId int not null, Id int not null, Date datetime default getdate(), constraint PK_Tertiary primary key (PrimaryId, SecondaryId, Id), constraint FK_Tertiary_Secondary foreign key (PrimaryId, SecondaryId) references Secondary (PrimaryId, Id) ) ``` Classes: ``` public class Primary { public int Id { get; set; } public Customer Customer { get; set; } public DateTime Date { get; set; } public List<Secondary> Secondaries { get; set; } } public class Secondary { public int Id { get; set; } public DateTime Date { get; set; } public List<Tertiary> Tertiarys { get; set; } } public class Tertiary { public int Id { get; set; } public DateTime Date { get; set; } } public class Customer { public int Id { get; set; } public string Name { get; set; } } ``` Is it possible to use one select to fill them all? Something like this: ``` const string sqlStatement = @" select p.Id, p.CustomerId, p.CustomerName, p.Date, s.Id, s.Date, t.Id, t.Date from [Primary] p left join Secondary s on (p.Id = s.PrimaryId) left join Tertiary t on (s.PrimaryId = t.PrimaryId and s.Id = t.SecondaryId) order by p.Id, s.Id, t.Id "; ``` And then: ``` IEnumerable<Primary> primaries = connection.Query<Primary, Customer, Secondary, Tertiary, Primary>( sqlStatement, ... here comes dragons ... ); ``` Edit1 - I could do it with two nested loops (foreach secondaries -> foreach tertiaries) and perform a query for each item, but just wonder if it could be done with single database call. Edit2 - maybe the QueryMultiple method would be appropriate here, but if I understand correctly then I would need multiple select statements. In my real life example the select has more then 20 conditions (in where clause), where the search parameter could be null so I would not like to repeat all those where statements in all the queries...