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

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.