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.
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.
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 });
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 .
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();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With