How to load entity from multiple tables with Dapper?

dapper querymultiple
dapper query multiple one to many
dapper group by
dapper return table
dapper multi mapping 3 tables
dapper query multiple objects
dapper hierarchical data
dapper update query

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.

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.

Multiple Mapping - Dapper .NET, How could we map the data returned by a query on such tables to our objects? If we would have had three tables also, once for each object —  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.

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.

Loading Related Entities with Dapper Many-to-One, Loading Related Entities with Dapper Many-to-One - Part 2 The GridReader makes it very easy to map multiple result sets to different objects the Airport table are not repeated in each row from the ScheduleFlight table. Using Multiple Result Sets. In the previous post, we loaded the ScheduledFlight entities and all related Airport entities in a single query. In this example we will use 2 separate queries: One for the ScheduledFlight entities, one for the related arrival and departure Airport entities.

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

Result Multi Mapping, These extension methods can be called from any object of type IDbConnection. Example - Query Multi-Mapping (One to One). Query method can execute a query  I've just started working with Dapper and I don't seem to find something very simple like mapping an entity to a table in my database: I have a stored procedure: CREATE PROCEDURE [dbo].GetUserById (@UserId int) AS begin SELECT UserId,LastName,FirstName,EmailAddress FROM users WHERE UserID = @UserId end go

Managing Relationships With Dapper, If a related entity is included as part of a query, EF Core will ensure that any This is how that query can be executed by Dapper to map multiple products in one relationships are represented in database schema as a separate JOIN table:. How to load entity from multiple tables with Dapper? Hot Network Questions Where does the "2" in 2^n come from when computing max memory size? n=n-bit

Tutorial on Handling Multiple ResultSets and Multiple Mapping , This article takes a look at how to use Dapper to read multiple ResultSets from the database in a single Also explore query related entities. Is there any way to use Dapper.NET with stored procs that return multiple result sets? In my case, the first result set is a single row with a single column; if it's 0 then the call was successful, and the second result set will contain that actual rows/columns of data.

Dapper.NET: Handling Multiple Resultsets - DEV, In any case, if you have multiple resultset, Dapper can help you, via the QueryMultiple method: to create complex object with data coming from different tables. You want to load a customer object with all related orders. So one course can be taught at several locations. Entity Framework does the mapping for me so my Course object is populated with a list of locations. How would I go about this with Dapper, is it even possible or do I have to do it in several query steps?

Comments
  • As mentioned below, a sql view will do the trick here.
  • So with 1 request to my API i will have to make 8 calls to database, is it really good solution?
  • @CSharpBeginner see my edit to add Option 2.
  • is it possible to somehow use result of first sql as input to second? For example first SQL will get productsId, and in second Sql i want to get for example details WHERE ProductId IN (here output from first sql)
  • @CSharpBeginner that is a separate SQL question really, but I've updated Option 2 to give you a brute force example of how to accomplish that. You could do the same through JOINs, temp tables, etc, but this gives you a sense that it can be done.
  • @CSharpBeginner Just as an extra - check out Tuple with Dapper. It's really powerful. If you can call all the entities in one query you can use a Tuple to allow you to put everything together. Less DB calls on this too. Happy to give an example if you need it.
  • What do you mean use View, in what sense? What View should return?
  • Just create View in SQL database which has all your necessary fields. Then create a model that matches these fields and use it in the controller.
  • What is the advantage when compared to create model and single repository method with the same SQL body as View?
  • If you have a sql view, your method will have one short query which will make the code cleaner. Besides, one simple query is much faster than the complex one.