How can I use Dapper to query multiple tables?

c# dapper design-patterns domain-driven-design

Question

In my app i use Entites as Tables in database representation.

I have an OrderEntity, that have fields like ProductEntity, CustomerEntity, then CustomerEntity has fields like AddressEntity etc.

Now I try to get OrderEntity filled with all the entity-type properties and so on. It looks like I have to load data from 8 tables.

I just have no idea how to do it properly. I have a OrderRepository with Get method, wher I want to return OrderEntity. So should I create SQL with 7 joins, one class with all the columns from the SQL and then after executing SQL create manually OrderEntity etc. in this repository's Get method?

Using repository etc. is easy when I have to get/update 1 table, but when the model is built of more than 1-2 tables, It's becomming really tough for me.

1
0
10/22/2019 5:16:29 PM

Accepted Answer

Option 1:

The approach I have used is to load each relationship individually (for a small N tables). If you have 8 tables, then 8 queries will provide all of the data you require. Here is a contrived example of 3 tables.

public class Person
{
    public int PersonID { get; set; }
    public string PersonName { get; set; }

    public Address[] Addresses { get; set; }
}

public class Address 
{
    public int AddressID { get; set; }
    public int PersonID { get; set; }
    public string AddressLine1 { get; set; }
    public string City{ get; set; }
    public string StateCode { get; set; }
    public string PostalCode { get; set; }

    public Note[] Notes { get; set; }
}

public class Note 
{
    public int AddressID { get; set; }
    public int NoteID { get; set; }
    public string NoteText { get; set; }
}

You would query each of the tables.

var people = conn.Query<Person>("select * from Person where ...");
var personIds = people.Select(x => x.PersonID);

var addresses = conn.Query<Address>("select * from Address where PersonID in @PersonIds", new { personIds });
var addressIds = addresses.Select(x => x.AddressID);

var notes = conn.Query<Note>("select * from Note where AddressID in @AddressIds", new { addressIds });

Then, once you have all of the data, wire it up to fix the relationships between these records you have loaded.

// Group addresses by PersonID
var addressesLookup = addresses.ToLookup(x => x.PersonID);
// Group notes by AddressID
var notesLookup = notes.ToLookup(x => x.AddressID);

// Use the lookups above to populate addresses and notes
people.Each(x => x.Addresses = addressesLookup[x.PersonID].ToArray());
addresses.Each(x => x.Notes = notesLookup[x.AddressID].ToArray());

There are other ways, but a view may not satisfy all conditions, especially when given complex relationships, leading to an explosion of records.

Option 2:

From the following link, you can use QueryMultiple.

https://medium.com/dapper-net/handling-multiple-resultsets-4b108a8c5172

Code as follows, where your child queries will have to select all of the records.

var results = conn.QueryMultiple(@"
    SELECT Id, CompanyId, FirstName, LastName FROM dbo.Users WHERE LastName = 'Smith'; 
    SELECT Id, CompanyName FROM dbo.Companies WHERE CompanyId IN ( SELECT CompanyId FROM dbo.Users WHERE LastName = 'Smith' );
");
var users = results.Read<User>();            
var companies = results.Read<Company>();

Then you would fix the relationships as in Option 1.

1
10/22/2019 9:06:19 PM

Popular Answer

OK (as requested above) - an example using a Tuple and Dapper.

I've really quickly written this out so if there are any mistakes let me know and I'll rectify. I'm 100% sure it can be optimised too!

Using this above structure as an example:

public class Person
{
    public int PersonID { get; set; }
    public string PersonName { get; set; }

    public IEnumerable<Address> Addresses { get; set; }
}

public class Address 
{
    public int AddressID { get; set; }
    public int PersonID { get; set; }
    public string AddressLine1 { get; set; }
    public string City{ get; set; }
    public string StateCode { get; set; }
    public string PostalCode { get; set; }

    public IEnumerable<Note> Notes { get; set; }
}

public class Note 
{
    public int AddressID { get; set; }
    public int NoteID { get; set; }
    public string NoteText { get; set; }
}


string cmdTxt = @"SELECT p.*, a.*, n.* 
    FROM Person p
    LEFT OUTER JOIN Address a ON p.PersonId = a.PersonId
    LEFT OUTER JOIN Note n ON a.AddressId = n.AddressId
    WHERE p.PersonId = @personID";

var results = await conn.QueryAsync<Person,Address,Note,Tuple<Person,Address,Note>>(cmdTxt, 
   map: (p,a,n)=>Tuple.Create((Person)p, (Address)a, (Note)n),
   param: new { personID = 1 });

if(results.Any()) {
    var person = results.First().Item1;   //the person
    var addresses = results.Where(n => n.Item2 != null).Select(n=>n.Item2); //the person's addresses
    var notes = results.Where(n => n.Item3 != null).Select(n=>n.Item3);  //all notes for all addresses
    if(addresses.Any()) {
         person.Addresses = addresses.ToList(); //add the addresses to the person
         foreach(var address in person.Addresses) {
             var address_notes = notes.Where(n=>n.AddressId==address.AddressId).ToList(); //get any notes
             if(address_notes.Any()) {
                 address.Notes = address_notes; //add the notes to the address
             }
         }
    }
}


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow