小巧玲珑的中间映射 [英] Dapper intermediate mapping

问题描述

更高级一些的映射，然后在我的前面的问题：）

Slightly more advanced mapping then in my previous question :)

表：

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

类：

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
";

和则：

IEnumerable<Primary> primaries = connection.Query<Primary, Customer, Secondary, Tertiary, Primary>(
    sqlStatement,
    ... here comes dragons ...
    );

EDIT1 - 我可以用两个嵌套循环做（的foreach二级 - >的foreach第三会），并执行。查询每个项目，只是不知道是否可以与单个数据库调用来完成

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 - 也许QueryMultiple方法是适当的位置，但如果我理解正确的话我需要多个SELECT语句。在我的现实生活中的例子中，选择具有更多然后20​​条件（where子句中），其中搜索参数可以为空，所以我不希望重复所有那些语句中所有查询...

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

推荐答案

小巧玲珑支持多映射，文件看：的 http://code.google.com/p/dapper-dot-net/

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();

诀窍是使用splitOn选项，来划分记录设置成多个对象

The trick is to use the splitOn option, to divide record-set into multiple objects.

您还可以检查我的问题，看看班级结构上面的例子：< A HREF =htt​​p://stackoverflow.com/questions/11042618/dapper-multi-mapping-issue>小巧玲珑多映射问题

You can also check my question to see class structure for the above example: Dapper Multi-mapping Issue

这篇关于小巧玲珑的中间映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！