Dapper intermediate mapping

Question

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...

Answer 1

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

Answer 2

Seems like in all ORMs you will have several queries. You only can create your own solution, maybe based on Dapper or Petapoco. For example, combine all queries in one SQL batch:

select * from Primary where ...
select * from Secondary where ...
select * from Tertiary where ...

Then you can navigate from one recordset to nex by using DataReader.NextResult()

Then, need to combine data in memory to complete objects structure.

Answer 3

What about creating a SQLCommand, and then a bunch of SQLParameter objects. Ideally with a stored proc but doesn't have to be.

Each of those output parameters could then be mapped back to your classes.

This other post on Stack has some code that could be relevant.