C# Data Access: Complex Objects with Dapper

[@fernandod3510]

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

[@IAmTimCorey]

I am glad it was helpful.

[@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.

[@cnbnichols87]

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!

[@IAmTimCorey]

I am glad it was helpful.

[@barry1048]

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.

[@IAmTimCorey]

I am glad it was helpful.

[@faisalalhoqani6151]

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

[@IAmTimCorey]

You are welcome.

[@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 👍

[@IAmTimCorey]

Thank you!

[@abduzahrakomil]

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

[@IAmTimCorey]

It all depends on how you want to set up your insert statement. You can just pass in the "top" record for insertion, or you can pass in the whole thing as a JSON object to be parsed in your stored procedure or you could call more than one stored procedure (one for the "top", one for each child object).

[@bobamos8508]

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#?

[@gahshunker]

Tim really got us covered for every scenario. 😮

[@IAmTimCorey]

Great!

[@runtimmytimer]

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

[@IAmTimCorey]

I've considered it, but I found myself calling the ToList in too many places. IEnumerable is great when you are doing lazy-load queries using EF. However, it is not useful when you want to do a foreach, get the count, etc.

[@runtimmytimer]

IEnumerable supports Any() or Count() and you can also foreach over an IEnumerable so I don't see the benefit. There are certainly instances where allocating the list makes sense but what I'm uncovering in real-time is it's pretty rare. That opinion may change over time, however. Now is Any() or Count() as efficient as the counterparts in List? Maybe, maybe not. Be an interesting test to see. Regardless, my follow up question is how many times are you calling either of those methods? Hopefully not many. What I'm seeing is this pattern of multiple ToList() calls (especially with LINQ) and nothing is ever done with the list (other than an Any(), Count() or foreach). So why allocate the list? Allocations = performance and in a cloud environment we pay for every CPU cycle.

[@TALHAEDITS1K]

Awesome as always.

[@IAmTimCorey]

Thanks!

[@ahmedifhaam7266]

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.

[@IAmTimCorey]

You will have one order with multiple order details (one to many). An order detail will always be associated with one order. That's why it has the ID of the Order that it is associated with. If it could be associated with multiple orders, you would have a many to many relationship. In EF, the way to solve the problem (or help with it) is to use SplitQuery. It isn't the magic solution, but it does something similar to what we are doing here. The reason the default generates the query it does is because that's the simplest way to do things. It isn't efficient, but it is simpler. That's basically the motto of EF - it isn't always efficient, but it is simple (sort of). When you have Entity Framework try to make decisions for you, it has to make assumptions. Otherwise, you are doing all of the work. However, those assumptions are just that. It doesn't always assume right. The top video in this search is the tool you are looking for: www.youtube.com/@IAmTimCorey/search?query=ssdt The other entries in the list are videos where I use it in real projects and in other demos.

[@Northbergh]

In your example you made separate queries and did the matching on the C# side. Wouldn't it be much more efficient to have the join happen on the database, which is optimised for such operations?

[@Alchemist19]

thanks for the amazing journey, i learned development from your free lessons!

[@IAmTimCorey]

You are welcome.

[@dylanthomas2346]

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?

[@IAmTimCorey]

It sounds like you are trying to access the database from the client-side project, which won't work. You need to access the database on the server-side project or put the data access in an API. Client-side code cannot/should not directly access secured resources.

[@dylanthomas2346]

@@IAmTimCorey That makes sense, thanks for the quick reply, you’re the man!

[@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.

[@IAmTimCorey]

Handle exceptions where the user can respond. You might process it at lower levels (logging, etc.) but you need to let it bubble up to a UI layer unless you are going to handle it automatically somehow.

[@marceloleoncaceres6826]

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

[@IAmTimCorey]

I am glad it was helpful.

[@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.

[@IAmTimCorey]

The results come back as an IEnumerable. Converting them to a List is easy (since a List is an IEnumerable type). Converting them to Dictionary would be much more expensive every time and unless you always have a guaranteed savings on the other end, it just isn't worth it. I would rather see you use List and get the application running. Then, if you have a performance issue that you think a Dictionary could help with, try it just at that spot. See if it actually fixes anything. That way you don't prematurely optimize something that doesn't typically need fixing. The overall issue is the idea that you are putting large data sets into memory. That's the place where I would look to optimize. Are you doing something that is better left to SQL? Maybe keep your records in the database and do multiple calls to pull out exactly what you want rather than pulling all of the records and then doing the querying inside of C#.

[@pawel89pawel]

Wow awsome video. I really needed it. Now I have a lot to change in my projects - and honsetly I am happy that I need to 🙂. Because I didn't know how to do it I was just having a complete match between the data structure and the model. But when the model was dependent from another model I was just using Id and then when I needed that second model in UI I was calling to the db again - that is unfortunatelly a lot of code on the UI side. Some of it necesssary but definetly not all of them. Thanks for the way to make my code much better.

[@avneelprasad5391]

Tim, what about using Dapper.Contrib for complex select queries ? For example the query method takes in the classes affected and the return class as the initial parameters

[@IAmTimCorey]

Dapper.Contrib is more for querying individual tables quickly rather than for doing complex queries. Because of the complexities of querying in the real world, I don't find it useful enough to add to most of my projects. It definitely has a use, but I don't think it would add enough in this situation to be valuable.

[@avneelprasad5391]

Thank you Tim. You're the reason I use Dapper in my projects !! Happy coding

[@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?

[@IAmTimCorey]

You would only return the records you need from each table. So if you filtered by "LastName=Corey" in the main query, you would do the same in the second query. I show that functionality off in the third query specifically, where I filter based upon the date range. Both queries are filtered by that range so that SQL only returns the relevant rows. So you return as little as possible, which can actually be less than what you could do with SQL alone, since SQL would return duplicate records. For instance, if you queried a Person record for personId=1 and then queried the Addresses records for personId=1, you might get two addresses. That might come back as two records in the query results, but the Person information would be duplicated. Not only that, you might have a duplicate Address because two people lived there. In my results, I would return that Address once and then assign it to two different users.

[@CharlesPedro-sd7mi]

Good video, But I am yet to see any implementation of Dapper in WPF MVVM. All I see is Entity Framework.

[@IAmTimCorey]

MVVM is a UI design pattern. Data access should not done with MVVM directly. There should be some separation. For example, the TimCo Retail Manager series (a playlist on this channel) uses an API that uses Dapper, so technically we are using Dapper that gives data to the WPF project that uses MVVM. The layers are just separated. But if you look at that project, you can replace the API calls with Dapper calls directly and they would work just fine.

[@i.eduard4098]

I have an unrelated question.. I am starting a junior backend position in 6 nov, I wasn't particularly good in interview but my soft skills sailed me in the job. What are you best tips for me if I was working for you from 6 nov, I really want at least 5 years in this company. Is a really good and big company, isn't an 5 people office.

[@IAmTimCorey]

Start now by brushing up on the skills you will need in the role. Practice what you learn. Don't try to speed-learn it. Just focus on actually understanding the basics and going from there. The practice is the most important part. When you get into the role, take notes. Don't just try to remember things. Write it down! That way, you won't have to bug people as often when you forget things. This will also become good documentation for the next person to get hired. You can share it with your boss and ask them for feedback. This can be your first win at the company - developing good onboarding documentation. Next, don't be afraid to ask for help if something isn't clear. Just be sure to write down the instructions and work to follow them exactly. People don't typically mind if you need help the first time. If you ask them four or five times for the same thing, that's when they get frustrated. Work to establish good relationships with people. That will make a big difference when it comes to your long-term success at the company. Don't come in and try to change everything. You don't know why they do things a certain way. Learn the way they do things. You can ask questions about alternate ways, but always come at it from the perspective that they know better than you do (because they mostly do). Do the boring jobs well and with a good attitude. That's always the start of your career. Doing it well will help people trust you with bigger things. Doing it with a good attitude will help them trust you more.

[@i.eduard4098]

@@IAmTimCorey amazing advices. I did started reading Agile principles patterns and practices. The examples in the book are relevant for the job itself. I was like, I am gonna do a demo about the project I am gonna working on it, but then I switched to learn that book and c# on depth by john skeet, and I still have to go over your video about SOLID. I think is a good decision. I wanna start doing some joins, unions, groupbys, haven't used having operator in ages. I mostly did code first rather than db first. This human interaction is a bit sketchy, I am not arrogant or a trouble maker. I grew up with inferiority complex, because bad childhood, sometimes I feel comfortable to let more capable people in charge but sometimes isn't the good, because it shows lack of confidence or lack of independence. I talked with a psychologist a few times last few months. She told I am overthinking, I carry a lot of learnt guilt and I have this inferiority complex, however this was in August when I was truly rock bottom. I friend of mine said to just be relaxed, don't need to put pressure or overthink because I already made it in the job. I am not very worried about the hardskills, when I was first time a junior... I worked as a fullstack web api 2 angular 2 project, it was holy hell first week. I envision my communication like this. I get a task and I chill, I don't start coding, I just layout the logic and I ask the superior, look this is what I am thinking to do, is it okay? Is something annoying isn't it? It screams lack of independence or is okay-ish if say the task is hard?

[@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.

[@IAmTimCorey]

You would either make multiple insert calls (one per table/row) or you could do some combinations (insert multiple rows/table using a TVP or sending all the data to a stored procedure, that then makes all of the necessary inserts). At the end of the day, it doesn't matter which ORM you use (or if you use one at all), the same tasks need to be accomplished - one insert per table/row.

[@Rusty254]

How about saving complex objects, do you know of a more efficient ORM for that?

[@IAmTimCorey]

Saving would be making one insert call per table or doing a combined stored procedure that does the multiple calls internally.

[@markharwood6794]

Hi Tim. As ever love your work :) In the (large) system I am writing at the moment I use Dapper, on your recommendation and love it, and have a Unit of Work and repository pattern going on and I do multiple queries against a single connection, in a UOW I factory build for each data access routine. Interesting that you mention the performance is not bad in doing separate connections as performance wasn't my main aim but rather running all the separate repo calls, which is where the queries actually are, in a single transaction which I create in the UOW. Anyway just thought I'd comment that performance is perhaps not the only reason to go with the pattern you touch on of having the multiple calls inside one connection. I know this video is a simple view, really appreciate the lack of junk you put in your examples :). Anyway just a general comment for no real purpose other than to engage and support what you do.

[@IAmTimCorey]

Thanks for sharing!

[@trustingod0]

Great Video Mr. Corey. In this example you only used 2 tables but what if I need to create relationships on 10 tables? Where would I start the next relationship, would I need to make another Task? Thanks !!!

[@Dimitris.Christoforidis]

Hello Tim! And thank you once again for your wonderful videos!! I wonder if it is good practise to use Dapper asynchronous inside an web api? Example inside a get method. Ιsn't every call to api asynchronous anyway? Does Get Method inside api needs to call dapper async?

[@IAmTimCorey]

The call to the API is async, yes. However, that doesn't mean that all of the calls inside of an API are async. You want to make an async call to the database still in order to keep your API performant.

[@Dimitris.Christoforidis]

@@IAmTimCorey Thank you Tim!! But, using this method don't we use more resources? I mean if a call is async (Example Get) and then i create a new async method to the database ? Then i have two async methods? Right? Each async method uses more resource cause is executed in a different thread?

[@IAmTimCorey]

Don't worry about the caller. That will happen no matter what you do. So you are wondering if you should make an async call or not because of the overhead. If you are going to make a call that has to wait on something (it doesn't happen immediately), you should consider async. Otherwise, you are adding overhead by locking your thread until the task completes.

[@Dimitris.Christoforidis]

Thank you Tim!!!!

[@trustingod0]

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

[@IAmTimCorey]

Yes, it would be similar. The big difference is that your raw SQL would be in C# because SQLite doesn't have the concept of stored procedures.

[@trustingod0]

@@IAmTimCorey Thanks Tim !!!

[@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?

[@IAmTimCorey]

It depends. In the TimCo Retail Manager, I did do that but I also did a transaction on the C# side. I try to avoid that, but it is what worked best in that situation. You could also send all of the data to a stored procedure and then let it separate out the data and do multiple inserts. That can be a lot, though. You could also consider using TVP for the detail records.

[@adrianspinetta]

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

[@IAmTimCorey]

It is a complicated project. Here is the link to the discussion: github.com/DapperLib/Dapper/issues/1715

[@santo998]

Can you make a video explaining versioning? Local Nuget packages, semver pattern, automatically incrementing patch and build number, etc. Examples of how did you solve it, etc.

[@IAmTimCorey]

Thanks for the suggestion. Please add it to the list on the suggestion site so others can vote on it as well: suggestions.iamtimcorey.com/

[@AldoInza]

This is one to bookmark.

[@IAmTimCorey]

I am glad it was helpful.

[@PerryCodes]

Been a while since KZbin recommended one of your vids Corey. I wish you gave as much coverage to EF as you have with Dapper. I tried Dapper back when you were doing the "Do I really have to use EF?" videos that seemed like they were trying to convince people that Dapper was "good enough" and that EF was a total horror show - unfortunately for my needs, Dapper didn't cut it and EF did.

[@PerryCodes]

FYI - the video "Entity Framework Best Practices - Should EFCore Be Your Data Access of Choice?" was the exact moment I disabled notifications. That was anything but a "Best Practices" video.

[@IAmTimCorey]

I think you missed what I was actually saying. I've never said Dapper was "good enough" or even implied that. What I have said is that Dapper is faster than EF in production (at the worst, it is as fast), it is easier to debug SQL performance issues when you use Dapper, and it allows you to let SQL do what it does best and have C# do what it does best. EF isn't a "total horror show", it is a great tool. However, it is also a tool that should be used by people who really know SQL well (SQL experts). EF makes it harder to debug SQL performance, which means you need to be that much better to overcome that issue. EF also does not allow you to lock down your database since it requires such wide-ranging permissions. Basically, you need to either give it admin access or be really good at EF in order to lock it down even a little bit. EF also assumes that the only consumer of the database is the application. It forgets about reporting servers, additional applications, etc. so you need to have good policies in place about how EF makes database changes. As for Dapper not cutting it, Dapper can do anything EF can do. The difference is how well you know SQL.

[@PerryCodes]

​@@IAmTimCorey I was obviously (or perhaps not?) being dramatic... so the quotes likely conveyed the wrong intention. It's been a while since I completed the project, which was a time-off system for a regional health department. I'll see if I can't find the areas where Dapper made things vastly more complex than EF. But this certainly could have been a situation where I've been doing SQL for over 20 years and didn't know much about Dapper when that project started.

[@JohnMarsing]

"Dapper can do anything EF can do. The difference is how well you know SQL." This is the bottom line for me. I'm comfortable with SQL so I like using Dapper over EF

[@trustingod0]

There is supposed to be some recent upgrade that has made EF just as fast as Dapper if speed is the only reason for using Dapper.

[@WantMore-mc8dx]

Why do you use Async? A stressed server will still end up using all resources asap. micro gains?

[@IAmTimCorey]

Data access should almost always be done via async. It has nothing to do with the resources of the server. It has to do with the responsiveness of the client. Instead of waiting for the data to be processed and returned, the UI can be responsive while waiting for the results. It is the difference between the screen locking up/freezing while making a database call and being able to move around the UI while the database call is being made.

[@BobbyBridgeman]

I swear both EF and dapper are miles behind what devexpress xpo was doing 10 years ago. In xpo you just create your models, then get them from the database with 2 lines of code, no split queries or anything. And it generates/updates the schema automatically at runtime. No stored procedures needed.

[@borsuk7667]

So basically you can do almost the same as with EF, but without an option if you need it

[@BobbyBridgeman]

@@borsuk7667 no not nearly as easily. You don't need to use special naming conventions, the database doesn't need to be told to update before reading/writing data (it just does it automatically). No context setups etc. I would post an example but I don't think KZbin will allow it!

[@borsuk7667]

@@BobbyBridgeman automatic update sound like a big problem, what if I do not want this Also without context, how you can configure any special staff, multitenancy for example It will check if entity has TenantId and if yes will filter by it

[@BobbyBridgeman]

@@borsuk7667 Just a quick and dirty example... //set db connection XpoDefault.Session.ConnectionString = @"server=yourserver; user id=usr; password=pwd; initial catalog='dbname'; Trusted_Connection=no;Max Pool Size=2000;"; //get an object by key with sub //will create or update the database just by doing this tblPeople person = Session.DefaultSession.GetObjectByID(1); //or get a list XPCollection people = new XPCollection(); people.CriteriaString = "Email IS NOT NULL"; people.Load(); foreach (tblPeople person in people) { //do stuff } //to save changes, just call .Save() person.Email = "[e-mail]"; person.Address.Town = "mytown"; person.Save(); //object models example namespace Database_Objects { [OptimisticLocking(false)] [Persistent("dbo.tblPeople")] public class tblPeople : XPBaseObject { public tblPeople (Session session) : base(session) {} public tblPeople ( ) : base() { } [Key(AutoGenerate = true)] [Persistent("Person_ID")] public int Person_ID { get { return GetPropertyValue("Person_ID"); } set { SetPropertyValue("Person_ID", value); } } [Persistent("Full_Name")] public string Full_Name { get { return GetPropertyValue("Full_Name"); } set { SetPropertyValue("Full_Name", value); } } [Persistent("Email")] public string Email { get { return GetPropertyValue("Email"); } set { SetPropertyValue("Email", value); } } [Persistent("Address")] public tblAddresses Address { get { return GetPropertyValue("Address"); } set { SetPropertyValue("Address", value); } } } [OptimisticLocking(false)] [Persistent("dbo.tblAddresses")] public class tblAddresses : XPBaseObject { [Persistent("Address_ID")] public int Address_ID { get { return GetPropertyValue("Address_ID"); } set { SetPropertyValue("Address_ID", value); } } [Persistent("Address_1")] public string Address_1 { get { return GetPropertyValue("Address_1"); } set { SetPropertyValue("Address_1", value); } } [Persistent("Town")] public string Town { get { return GetPropertyValue("Town"); } set { SetPropertyValue("Town", value); } } [Persistent("Postcode")] public string Postcode { get { return GetPropertyValue("Postcode"); } set { SetPropertyValue("Postcode", value); } } } }

[@amitkumdixit]

1.With Sql server we could have used multiple result set. Its a single round trip. 2. With PgSql we could have ise jsonb In both the scenario one 1 round trip was required.

[@IAmTimCorey]

I show how to do that in this video: kzbin.info/www/bejne/m3zOmWiOopWCorcsi=g1J1EZu2ppOt4fPe In the current video, though, I was showing various options to build out a complex object, which isn't the same thing as returning multiple datasets. Yes, you could use the multiple datasets to fill out a complex object, but that isn't really the same as what I was demonstrating.

[@MrJltb]

Hi, your videos are awesome. Thanks for sharing with us what you have learned!!, I have a question, let's say that instead of wanting to retrieve all the fields from another entity like for example in the details order, we would need the name of the product that is persisted in another table, if we declare a list variable for say Product class, would it be a good approach even if we have like 10 fields of products table, or it would be better to add only one property to details orders class called Product Name, I've notice that since the properties are build when constructing the class the fields that are not returned by the store procedure have a null value... thanks again!!

[@haslamconsultancy2152]

Shouldn't we be careful about multiple nested statements as the amount of queries that hit the DB exponentially grow? In example, one order may have 1000 order lines and each of them may have serial numbers associated with them and therefore need 1000 individual queries firing to get the detail and even more so if those serials have nests. I guess it comes down to your famous saying "it depends" 😅

[@phillismable6303]

I know its like half a lesson.

[@IAmTimCorey]

That's why I didn't do it that way. No matter how big the lists get, you only make 2 calls to the database - one for the Order list and one for the OrderDetails list. It still isn't wise to load more data than you need, which I talked about, but by only making two calls total no matter what, you are at least more efficient.

[@gmanshackshack6822]

Why not lazy-load subordinate objects? Simply load them when the List's getter is accessed and the List == null.

[@runtimmytimer]

It's added complexity and likely depends on the requirements of the system. If the amount of data is small it's likely not worth the effort or complexity - just load it and be done. Whereas if the amount of data is large it probably makes more sense. Or in cases where it's unlikely the details (or secondary data) is ever accessed. Why load the data if it's never going to be used? Load it when you need it.

[@IAmTimCorey]

I did mention that in the video. You definitely want to not load any data you don't need. However, sometimes people need that data right away. Also, there is going to be a really quick tipping point where lazy loading is actually hurting you. For instance, if you have 1,000 records and you start looking at that list, you are going to make up to 1,000 calls to the database instead of the 2 we initially did.

[@6stringmonk]

This becomes a slippery slope of serendipitously building your own feature rich ORM / persistence framework on top of dapper. Of course, context always matters so I wouldn't say never ever do it. My rule of thumb is I'll start using EF instead of Dapper as soon as I need lazy loading, dirty tracking, or inserts of an object graph.

[@DaniLearnsIT]

Awe man... I was really hoping there would be a better way to do this. I don't know how good of an idea is to call database a ton of times depending on the number of nested objects you have. And then manually matching every column sounds like a ton of overhead. I was hoping dapper would just combine the properties of the nested objects into the main object, fill out the fields and then re-construct the nested objects :(

[@IAmTimCorey]

When you are working with a relational database, you are always going to have to deal with getting data from multiple tables. How you do that depends on how much complexity you want and how efficient you want to be. The method I am demonstrating here is to make two calls that will give you just the records you need, regardless of how many records you have. Yes, you would need to do this for every nested object in a class, so if your class had three nested objects, you would have a total of four calls to SQL. That's extremely efficient. You are only transmitting the specific data you need, the join in C# is very efficient, and opening and closing four calls to SQL is low-impact. Connection pooling means you aren't actually opening four connections typically, even though you are in your code. Compare this to the alternatives. First, you could go even more efficient by putting all 2 (or 4) SQL calls into one stored procedure. It could return multiple result sets, which Dapper could handle. It means a bit more complex code but you only have one connection to the server. The downside is that you are ALWAYS making that call rather than making the choice to not fully hydrate a model. So you have more complex C# code and you are more limited in what you can do, but you save 1-3 connections to SQL (and that's not really the case, since again ADO uses connection pooling). Second, you could go the Entity Framework route. The way it does it is it does SQL joins with all of your tables. That means if you have 10 Person records, each with 5 Address records, you would return 50 records with each person listed five times. If you expand that out to the three nested objects on Person and each had five records, you would be returning 1,250 records with each person record duplicated 125 times. So yes, you make one call to SQL, but it is going to be an ad hoc query, meaning it is less likely to be cached (less efficient call), and you will be returning LOTS of duplicate data across the wire. Then, on the C# side, EF will filter and join the data to build out the models, which is a lot less efficient than my method. So at the end of the day, you have to do something. Each solution has downsides. You need to choose the one with the biggest upside for you.

[@DaniLearnsIT]

@@IAmTimCorey Thank you Tim! I do try to stay away from Entity Framework as much as possible hahahaha, I have heard that it speeds up development process a ton, but when something goes wrong, it is hard to debug. I just saw your video on advanced dapper! It was mega helpful! :) Thank you for all the content you put out! I don't think I would've chosen to stick with .NET if it wasn't for the clear explanations you put out there!

[@jscomputadores1049]

I like dapper but hate sp, for me database server has only two purposes which is storing and retrieving data, and all business logic belong to the application

[@IAmTimCorey]

There wasn't any business logic in my stored procedures. Plus, if you use just stored procedures, you can lock down your data access to only be able to execute stored procedures. That means if someone gains access to your connection string, they can only do what the application can do. Plus, if you need to change how a query works, you don't have to do SQL changes in your application code (and go through the CI/CD process to get those changes updated). Plus, if you need to do anything complex (like a transaction), it is MUCH better to do it on the SQL side rather than the application side. Plus, while SQL does do caching of dynamic SQL statements, it is more efficient to use a stored procedure because the caching is easier and less likely to break.

[@caseypries7559]

I understand you really like dapper and I can appreciate that. I'm also a big fan of your videos. But I don't write things for a large audience so spending all this time wiring up dapper to work is not in the least bit appealing to me. I'd much rather create my sql db using ssm, create a database diagram, and then scaffold it into my project for EF. It's not as efficient when running but much more so for creation. I also like the syntax of linq and I've never been a fan of anything that had to be put in quotes to call something. Too much room for error and God knows I make enough as is.

[@IAmTimCorey]

I'm not sure why it would be slower for you to use Dapper, but if you are using LINQ to create your query, the issue will be speed at runtime. That's where you really get burned, especially since it won't happen right away. It will happen when your database is actually being used in production for a while. Then it will be too late to make the change.

[@caseypries7559]

​​​@@IAmTimCorey as far as slower I saw how much code you had to write to wire it up. And of coarse if you make a change to your db you have to make sure you change your code accordingly. I just run the scaffold command again and I'm done. My db would be very very lucky to ever have 10 users on it at a time. Probably 99% of the time it will only be me using it. It's a CRM, inventory, scheduling app for myself. So speed shouldn't be a big issue. I also hate using strings in my code for things like data access. I much prefer Linq.

[@lolyasuo1235]

Cool. Tbh i do not agree with using stored procedures in 2023. We are trying to get away from them :P

[@IAmTimCorey]

Why?

[@coderider3022]

I agree, legacy approach from 90s. It’s a generational thing too , it’s a dying skill and not as important as it was 10-20 years ago and new devs don’t have a clue but instead have ui skills.

[@Andre-hj4xc]

Because it transfers application logic into the database.

[@IAmTimCorey]

But that's not true. It is a myth. You CAN put application logic in the database, but that's not the fault of stored procedures. That's like saying you shouldn't use C# because you can put database logic in it. Stored procedures allow you to more easily debug SQL performance, they are easier on SQL for caching execution plans, and they protect you from most SQL injection issues. They also allow you to lock down your database in a way that adds a significant layer of protection to your data.

[@MiltonZurita]

​@@IAmTimCoreyI think it's mainly something akin to how antivaxers don't want vaccines because they haven't seen any of the illnesses that we're vaccinated against. They haven't seen the actual benefits of stored procedures so they tend to find it redundant.

[@margosdesarian]

Did you delete my comment tim?

[@IAmTimCorey]

Nope. If it had a link in it or if you mention ASP.NET (which looks like a link to KZbin) or if there is text content that is abusive, KZbin will filter your comment. Some comments fall into my "Held for review" tab, where I can review if they are fine or not and approve them or delete them. Others, however, don't even show up in my dashboard. I checked the Held for review section and your comment was not there. Can you try posting it again?

[@margosdesarian]

@@IAmTimCorey Hi Tim, it turned out that when i edited the comment it got deleted - or rather, was not saved, even though i had already made the comment. Go figure. It happened again today - thats how i worked out what happened.

[@margosdesarian]

@@IAmTimCorey My question was about why you did not use the split technique in dapper - e.g. var products = await connection.QueryAsync(sql, (product, category) => { product.Category = category; return product; }

[@IAmTimCorey]

I'm glad you figured it out. That's good to know. I briefly mentioned that in the video. I covered that technique in my Advanced Dapper video. However, that adds complexity to the Dapper call when you don't really need that complexity. The tiny bit of performance you gain doing that doesn't typically overcome the additional code complexity vs making two simple calls.

[@christianrazvan]

Title is somewhat misleading. This has nothing to do with dapper except that it bring the objects from the db

[@IAmTimCorey]

This app is entirely about data access. I use Dapper to get the data and then show you how to populate complex models. I’m not sure how I could have been more clear.

[@tchpowdog]

It boggles my mind why you continue to want to go through all this complexity instead of using SQL's FOR JSON. In real life scenarios, your models will more often be even deeper and wider than the examples you gave here, and you can still do all that in one FOR JSON query. It's much less complex and you don't have to do all this loop-inside-loop data merging in your application at runtime.

[@IAmTimCorey]

Couple of things here. First, getting SQL to build the JSON can mean a lot of extra work for SQL. Second, it will always entail a much larger amount of data being transferred from your SQL Server to your client. Third, the FOR JSON command sounds great, but it is extremely easy to get wrong. You can end up writing some complex queries with sub-queries in order to get it to properly build your JSON for you. Fourth, our "complexity" in this example is a second query and a join on the C# side. If you added a dozen more objects and arrays that needed to be added in, the complexity wouldn't be much more. Just one query per set of data that you needed.

[@tchpowdog]

@@IAmTimCorey I'll address each of your concerns as you've numbered them: 1. I have tested/used FOR JSON on nearly every query in all my apps since 2017. Returning, in some cases, nested objects that are 5 deep, on millions of records. I have compared the performance of doing this both ways and using FOR JSON is unnoticeable (I have seen zero changes in performance). 2. I do not know what you mean by this. You only query the data you need, just like any other query. You can filter any tier of the nested data and only return what you want to return. 3. So I thought this as well, until I learned how to use it. It's a very simple feature to use. Yes, it requires more query writing, as you would expect - but that's just a trade-off to the amount of C# code and extra procedures you have to write doing it your way. 4. What I mean by complexity is the logic required to do this your way. If you add one more nested array inside LineItems in your example, then you have to do a loop inside a loop. And you have to do that for each nested tier you have. And you're doing all this in your application at runtime. Your way could force a dozen calls to the database with a dozen loops (some may be nested), whereas, the FOR JSON way would be one query, one call to the database, then deserialize the result into your C# object. I have been using FOR JSON since its inception. If you're interested, I have a blog post detailing everything I've learned and best practices. If I post it here my message might get deleted...

[@IAmTimCorey]

I'd definitely be interested in seeing some real-world results from it. Email me (tim at my domain name) and I'll give it a read. Thanks!

[@tchpowdog]

@@IAmTimCorey I'll email you the link to my blog post. My blog post uses the AdventureWorks2017 database for examples.

[@ishimoto9762]

@@tchpowdog would be great if you can share your blog post with us