C# Data Access: Complex Objects with Dapper

In C#, we deal with complex objects that have nested objects inside of them. In SQL, a query returns flat rows. Those two structures don't match up well. Entity Framework allows us to return complex objects, not just rows. But how do we do the same thing using Dapper? In this video, we will look at how to load Order objects, each with a list of order details inside of them. We will look at multiple ways to accomplish the task using Dapper, as well as how this can be much more efficient than the default method in Entity Framework.

[fernandod]

I walk into work this morning to begin using Dapper for this data integration I've been assigned, and here is Tim Corey with a video about it! This man is an angel watching over us all

[bdscarboro]

I think you're illustrating what may be the best performing approach to the complex object mapping with Dapper, especially in scenarios where there are many parent rows, and many child rows, and both the parent and child objects have lots of properties. As you point out, your approach minimizes the amount of raw data being sent from the db server back to the application. However, when I saw the video title, what I'd hoped to see was also some discussion of other approaches for accomplishing the same, along with pros and cons for each. Two other approaches I had in mind are 1. using the "splitOn" parameter of Dapper's Query method, and 2. having the db call return just the parent records, where the child data in each is represented in json, along with a custom type handler to deserialize that json into the object tree. Having said that, I realize that would have required more than the 28 minutes that this one ended up being. Thanks for your efforts.

[cnbnichols]

What are the odds that you would publish a video on a topic I was wrestling with today. I knew my solution wasn’t quite right and here you are with a spot on solution!1. Thanks for all the education you’ve provided me!

[barry]

Tim, thanks for another great video! I started using Dapper a while back after watching one of your previous Dapper videos and I love it! I created a NuGet package in my local NuGet repository with a library that has all the my base Dapper code which makes it really easy to use with projects that require SQL Server data.

[faisalalhoqani]

I'm happy to see a new video about Dapper. Thank you dear Tim keep it up.

[abduzahrakomil]

Great video, many thanks. What if I want to insert into Orders given that OrderModel has a nested object?

[JohnMarsing]

Great video.
I read through all the comments and gleaned some useful information that I kept notes on. Thanks for your effort in making great content 👍

[ahmedifhaam]

At 3:35 you mentioned its a one to many relationship between order and order details. Which side here will be many. I found it a bit confusing the order details is the many i assume and its a backwards relationship where many order details are entered but points to same order id?
Another question is, so in EF is there a way, to solve this problem? I can't believe by default ef generates queries like that, there must be a reason right?
Btw in which video do you explain the data tools project? I remember you going over it in recent videos, but in which one do you explain it in detail. It seems really good, I want to start using it in my existing project which has a very large db where data changes take place frequently, and we all have to share the schema script between us or use a database tool to sync schema.

[bobamos]

I did a mapping where i loop trought the data of the main table (in the same connexion) and using the id of the main table, i call the sub query with the orderid setted to the main table id, is that wrong? Because like this you use linq, but isin't sql much faster for parsing data than c#?

[runtimmytimer]

One thing I've started to do recently is removing the ToList() call and just return the IEnumerable<T> from dapper. It's often times an unnecessary allocation.

[mrjohnzon]

Great video as usual. Thanks! I was wondering where the best place would be to handle exceptions, for example trying to delete objects with foregin key constraints. I wouldnt want to do that in the generic SaveData but doing it in the data class calling SaveData doesnt seem to work.

[gahshunker]

Tim really got us covered for every scenario. 😮

[Reellron]

Could converting the response from the details query into a Dictionary instead of List be a good idea? Converting into a Dictionary is somewhat costly, but I always get worried when I see nested loops for potentially large data sets.

[dylanthomas]

Hey Tim! Thanks for your videos!
I’m running into an error “Microsoft.Data.SqlClient is not supported on this platform”
Im running a .net 8 blazor auto project in vs2022 with dapper.
I’ve scoured the web and most replies say to ditch sqlclient for EF core.
Have you seen this issue?

[trustingod]

Great Video. I was wondering would this be straightforward to do using Sqlite? Thanks !

[adrianspinetta]

Very helpful and you know if Dapper will manage some day DateOnly type?

[AndersKjaerProduction]

Thanks, Tim for another great video. I would like to know how to do an update of a nested object with Dapper. Would you also split them into two separate SQLs and calls to the database?

[MikevanKuik]

@IAmTimCorey Thank you again for a great lesson.
This part is about getting complex objects.
How would you go about saving complex objects?
Saving such an order with multiple orderlines for example.

[MachineofLight]

I have some trouble understanding how it is efficient to retreive 2 (or significantly more) full tables only to then join and filter them afterwards. I'm a database developer with some focus on performance tuning, and always try to retreive as little as possible.
Is the query somehow optimized at runtime, including any filtering done after the initial query, or do we really get the data of all tables involved first?

[marceloleoncaceres]

Tim, Thank you very much, I was just wondering about this topic a few days ago,