Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Querying into a complex object with Dapper

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

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.

Here is what I have and it is working but I am wondering if this is the cleanest/simplest way to do it:

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.

like image 392
Blake Rivell Avatar asked Jul 07 '17 23:07

Blake Rivell


People also ask

How does Dapper map objects?

Dapper maps data to the first type in the same way as it does if only one generic parameter has been supplied to the QueryAsync<T> method. If is then told to map data to the Category type, and to assign the resulting object to the product's Category property.

How do I write a dapper query?

C# Dapper parameterized query When we write parameterized queries, we use placeholders instead of directly writing the values into the queries. The example selects a specific row from the table. var car = con. QueryFirst<Car>("SELECT * FROM cars WHERE id=@id", new { id = 3 });

What is splitOn in dapper?

splitOn: CustomerId will result in a null customer name. If you specify CustomerId,CustomerName as split points, dapper assumes you are trying to split up the result set into 3 objects. First starts at the beginning, second starts at CustomerId , third at CustomerName .


1 Answers

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();          
}
like image 183
Aderbal Farias Avatar answered Nov 01 '22 13:11

Aderbal Farias