使用Dapper查询复杂对象 [英] Querying into a complex object with Dapper

问题描述

我有一个具有以下属性的Customer类:

I have a Customer class with the following properties:

public int Id { get; set; }
public string Name { get; set; }
public int AddressId { get; set; }
public Address Address { get; set; }

我的目标是编写一个Dapper查询，该查询将使用内部联接在返回的每个Customer中填充整个Address属性.

My goal is to write a Dapper query that will use an Inner Join to populate the entire Address property within each Customer that is returned.

这是我所拥有的并且正在运行，但是我想知道这是否是最干净/最简单的方法:

StringBuilder sql = new StringBuilder();
using (var conn = GetOpenConnection())
{
    sql.AppendLine("SELECT c.Id, c.Name, c.AddressId, a.Address1, a.Address2, a.City, a.State, a.ZipCode ");
    sql.AppendLine("FROM Customer c ");
    sql.AppendLine("INNER JOIN Address a ON c.AddressId = a.Id ");

    return conn.Query<Customer, Address, Customer>(
        sql.ToString(),
        (customer, address) => {
            customer.Address= address;
            return userRole;
        },
        splitOn: "AddressId"
    ).ToList();
}

我担心添加其他属性，例如:

I have some concern about adding another property such as:

public Contact Contact { get; set; }

我不确定如何切换以上语法以填充地址"和联系人".

I am not sure how I would switch the syntax above to populate both Address and Contact.

推荐答案

我已经使用Dapper 1.40版进行了编码，并且编写了类似下面的查询，但填充多个对象没有任何问题，但是我面临的限制是我可以在查询中映射的8个不同的类.

I have coded using Dapper version 1.40 and I have written queries like the way below, I haven't got any issues to populate mote more than one object, but I have faced a limit of 8 different classes those I can map in a query.

public class Customer {
    public int Id { get; set; }
    public string Name { get; set; }
    public int AddressId { get; set; }  
    public int ContactId { get; set; }
    public Address Address { get; set; }
    public Contact Contact { get; set; }
}

public class Address {
    public int Id { get; set; }
    public string Address1 {get;set;}
    public string Address2 {get;set;}
    public string City {get;set;}
    public string State {get;set;}
    public int ZipCode {get;set;}
    public IEnumerable<Customer> Customer {get;set;}
}

public class Contact {
    public int Id { get; set; }
    public string Name { get; set; }
    public IEnumerable<Customer> Customer {get;set;}
}

using (var conn = GetOpenConnection())
{
    var query = _contextDapper
        .Query<Customer, Address, Contact, Customer>($@"
            SELECT c.Id, c.Name, 
                c.AddressId, a.Id, a.Address1, a.Address2, a.City, a.State, a.ZipCode,
                c.ContactId, ct.Id, ct.Name
            FROM Customer c
            INNER JOIN Address a ON a.Id = c.AddressId
            INNER JOIN Contact ct ON ct.Id = c.ContactId", 
            (c, a, ct) =>
            {
                c.LogType = a;
                c.Contact = ct;
                return c; 
            }, splitOn: "AddressId, ContactId")
        .AsQueryable();

    return query.ToList();          
}

这篇关于使用Dapper查询复杂对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！