How to load entity from multiple tables with Dapper?

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.

Answer 1

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

Answer 2

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.

Answer 3

Try using view in SQL, it will make things really easy.

View works as table, as well Dapper queries for view are this same as to table. The way you want to do this will make things harder than they should be.