How can I use Dapper to query multiple tables?

c# dapper design-patterns domain-driven-design

Question

Entities are used as tables in the database representation of my program.

I have an order entity with fields for product entities, customers entities, customers entities with fields for address entities, etc.

Now I'm attempting to populate OrderEntity with all the entity-type attributes, etc. It seems that I must load data from eight tables.

I simply don't know how to do it correctly. I want to return an OrderEntity from a Get method on an OrderRepository. So, should I construct SQL with seven joins, a class containing all the SQL's data, and then manually create OrderEntity, etc., in this repository's Get function after running SQL?

When I just need to acquire or change one table, using repositories and other tools is simple for me. However, when the model consists of more than 1-2 tables, it becomes much more difficult.

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

Accepted Answer

Choice 1:

I have loaded each connection separately as my strategy (for a small N tables). If there are 8 tables, then 8 queries will provide all the information you need. Here is a fabricated set of three 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; }
}

Each table would be subject to a query.

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

Once you have all the information, you should wire it such that the connections between the entries you have loaded are fixed.

// 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 various options, but a view could not meet all requirements, particularly when complicated linkages are present, which would result in a record explosion.

Choice 2:

You may use QueryMultiple from the following URL.

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

Code like seen below, where your child queries must choose every record.

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>();

The relationships would then be fixed as in Option 1.

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

Popular Answer

Okay, a Tuple and Dapper example (as suggested above).

This was written rather fast, so if there are any errors, please let me know and I'll fix them. I'm certain it can be optimized as well!

Using the preceding structure as an illustration:

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