Happy to help!

I agree but not everything will scale - a lot of apps are used by a small number of users and some activities will be limited. We have an app that inserts millions of records in parallelism mode (1 minute) and does spatial processing in SQL Server on those records (another 1 minute). Then we summarize the data as summarizing 5 million records on the fly to show the web page can take 0.6 seconds (shocking for not a fast server) and if you have 10 concurrent users it'll slow down the system😁 The summarized data of the 5 millions records takes 0 seconds to load. If this happened all the time, we'd need more processing capacity than Amazon 😄 We didn't design it to scale other than to allow parallel modes instead of serializing the database processing doing 1 record after another for 5 million. We, in fact, prevent another concurrent situation and queue them that type of processing. But good design and practices always pay off in the long run and will make it easier to scale.

Yeah, those prototypes and MVPs end up becoming production apps way too often. I agree that over-optimization at the outset isn't a good idea, but making fundamentally sound design decisions even at that early stage is incredibly important.

Literally the same as me

Same as me but with XPO instead of EF. Making the switch to EF Core on new projects.

Agree, in my shop we use both, and just told my fellow devs that we need to keep both. We use EF for data model (even scaffolding) , simple retrieval and update, but if it comes to heavy performance stuffs using Dapper, and even write a high performance Store Procedure. It certainly like Tim said, it force me to write SQL and if I need to tweak, I know where to Tweak.

Completely agree. Surprisingly, this approach is new to most companies and tends to be a tough sell. IMO, you get the best of both worlds.

This is the way.

Thanks for sharing.

Thanks!

I'm glad you enjoyed it.

Thank you!

You are welcome. Thanks for sharing.

Sir, can you give me any recommendations as to how implement solid authentication and authorization system using Dapper? Microsoft Identity system requires EF and I’ll have to use Pomelo EF with my MySQL database. I failed to do it with Dapper because it requires migrations and it MS Identity is trying to create it’s own tables in the database.

Thanks for sharing.

Thanks!

It is ideal to write your SQL in SQL.

Same having grown up with ADO and even pre ADO methods I thought EF was gonna be better and has some nice features but you lose control. However I'm not really sure what Dapper does over the pure ADO approach. Tim mentioned mapping to models... So does that mean Dapper links with the MVC approach?

Same here

Thanks!

Thank you!

You are welcome.

Thanks for sharing!

mind sharing, if you have a public repo? i've been using dapper as well, and what you just said sounds interesting.

@@feitan8745 I'm planning to put it out before the end of February. I'll update this when it's available 😎👍

@@teriyakov cool man

@@teriyakovyea definitely be interesting to see

Thank you!

Thanks for sharing!

I'm curious what you are trying to test when you test the data layer. Are you looking to verify that the queries return the data you are expecting? Are you trying to test that Dapper does what it says it will do?

@@IAmTimCorey Thanks for the reply Tim, I think there's no point in testing dapper itself because the dapper team does that already. I believe the purpose of testing the DA layer is to ensure your queries and production database don't have a conflict and will return what we expect them to return. Like in EF we can either mock the DbContext or can use the In-memory provider.

So an integration test that uses an actual database to validate that the queries work correctly. Is that correct?

@@IAmTimCorey Yes. But I've seen a lot of the teams and projects make it part of the unit testing...

Unit testing should not include interactions outside of the method, so a database should not be in the mix. That's what I'm trying to figure out. I'm not sure what value a unit test would give in this situation.

You are welcome.

Thanks for sharing.

Ah yes, the DataSet days. Interestingly, data-binding is kind of making a comeback in scenarios like Blazor / WASM. Funny how things come full circle. :)

Thanks!

Thanks for sharing!

Thank you!

1) You can use stored procedures with EF if you are already using EF. This video focused more on the initial decision of which ORM to choose. 2) I personally recommend SSDT to build/update your database. You still need to manually change your table structures when changes are needed, but I'm ok with that. The frustrating thing I see is this idea that your models need to match your tables. That's not usually an efficient design. If you are just mapping tables to models, you need to really evaluate if you need a relational database. I more often see a mapping between models and queries, where only the data you need is returned rather than being sloppy and returning all of the data and ignoring some of it. 3) Yes, you can add Dapper to a project that already uses EF Core. That's what Stack Overflow does in their design. 4) If you use SSDT, your database gets updated with your code deployment.

For the database part a simple repository with transaction (UoW) would do the job.

Excellent!

It really is.

Just be careful that you don't sacrifice the performance and simplicity of your application in order to follow a pattern.

The problem I'm having with this whole thing is he's basically saying using Dapper forces you to also learn SQL in depth, and EF doesn't necessarily. Ok but if your job (as mine is right now) is to set up an MVP for a product I simply don't have that kind of time. So I am that "poor developer" you refer to here, I'll fully admit that, but what can I do about it? Only so many hours in a day. I think a better solution I'd personally like to see someone (Microsoft?) do is make a video about best practices. Still after watching Tim's video here I'm not any closer to knowing what exactly it is that I'm doing wrong, other than not providing a char limit to string properties.

@@johnhershberg5915 I think it's fine to not know SQL in depth... at first. Understanding the data side is something you may not have time right now for, but you need to know it to know how things end up working. Everyone is a poor developer until they aren't. This isn't a criticism, it's just a fact of time and experience. It really is a good idea to offload the data logic to the server where the data is stored whenever possible if for nothing else for the network transmission and searching efficiencies. If you're not full stack yet, maybe find someone who is good with the data side and just sync with them. Something we don't consider often is the size of the payload and the effect on network transmission as we don't consider that a bottleneck much anymore, but it can be. If I can pull one part of data already searched and sorted, vs the entire dataset then searched and sorted programmatically in memory. It's going to be exponentially faster even if you make more small network calls with less overhead overall. Doing joins, and even things like stored procedures on the Database side just divides the processing components. Here's a thing I think he mentioned that is very important. ALL applications are data transfer applications. Thats it, its information in the form of data being moved from one place to another. In that case, the data model is probably the most important feature you can focus on at first. GUI, looks great but functionality is priority to me, even in MVP's. Although, we know that that's not what the customer wants, but what the customer needs. But yeah, correctly scoping DB fields is about as basic a "improvement" as you can get, and that starts with appropriate DB design.

I highly recommend stored procedures. That way, you can optimize them in SQL when you notice a problem in SQL. You don't need to find the T-SQL in your C# source code to optimize it when you find a problem in SQL.

The question is, how often do you switch database servers in an actual project? Personally, I've changed the database layer once in 20+ years of consulting with hundreds of companies.

You are welcome.

Thanks!

When you get to that point, I wonder what value EF is actually adding to your process. It would seem like a lot of overhead for what Dapper does for you with much less complexity.

@@IAmTimCorey Thanks for you replay, I will investigate and test more in deep with dapper, I'll watch your two videos(Dapper and Best Practice with EF).

Thanks!

Thanks for sharing.

It's a preference thing. But using dapper and writing your own sql is just better since you know exactly what your code is suppose to do.

You are welcome.

Thank you!

Thanks for sharing.

Thanks!

Thanks for sharing!

I'm glad you like it.

I have absolutely the same question sir! I found that examples of custom authentication and authorization systems using Dapper are very poor.

Thank you!

Why? It seems like you get the drawbacks of each when you do that and I don't see the benefit.

I recommend SSDT for SQL development and migrations.

Oh no, I was meaning schema changes and, thus, schemas/models migration, not the actual data

@@RussianBananan SSDT contains SQL Server database project that allows you to manage your full schema in visual studio using publish and schema compare to produce scripts for schema updates

Absolutely. The "issue" is that there isn't much to change. That's the beauty of Dapper. It is really simple and it "just works". There is talk of adding AOT to Dapper to make it even faster, but I'm not sure when that will happen. But yes, Dapper is a currently-maintained project.

I already have: kzfaq.info/get/bejne/jtqkadCEldaWj5c.html

@@IAmTimCorey thanks for your contribution

The problem I see with this is that MVPs tend to become the "real" application. So the very foundation of your application is knowingly less performant. I know you don't want to prematurely optimize, but there is also something to building the foundation properly. Otherwise, you work yourself into a corner. You won't have time to go back and fix it later, so you just live with the bad design decision.

EF gets faster with time, when executing complex queries.

@Feitan - That statement confuses me. I've not seen that to be the case. What do you mean EF gets faster? Complex queries are the biggest issue with EF, where it gets too much information. For instance, in a many-to-many join, it just returns lots of duplicate records and then deletes them on the C# side. Things like that are inefficient, but that inefficiency doesn't really show up until you have a "real" production database, where relationships are larger and the number of records is beyond what you normally see in a development database.

@@IAmTimCorey i've done some heavy lifting with both and sometimes EF was even or even on top of Dapper after some repetitive tasks, due to the caching maybe. And yes, it was in production. Not trying to say EF is faster tho', as many other experienced people have proven it to be false.

Yes, it can. The question is, what is EF Core giving you then? Is it just database creation? If so, why not use SSDT: kzfaq.info/get/bejne/n9B0lqt43d_VdWg.html

@@IAmTimCorey Isn't SSDT SqlServer specific? How about other databases?

You aren't "shifting overhead to either C# or SQL", you are making C# do what SQL is best at. You aren't relying on the power of your database. I see lots of people who are super-optimistic about EF, especially at the beginning. What I wonder is how many EF installations have you had to manage in production at any type of scale? That's where the problems come in. Not development, not early testing, not even early production. It isn't until it is too late. I'm working with a non-profit right now that has less than 300 employees and less than 20,000 active customers, yet they are being crushed by EF. And unfortunately, it is so woven into their architecture by now that it cannot just be replaced. Even replacing certain calls with stored procedures isn't an option. They have exceeded the capacity of their largest servers and have now moved to the cloud so that they can scale larger. That is an absolute waste of resources because of EF.

You're trading simplicity, control, having a clue what's going on and blazing speed for version control? To each their own I guess.

​@@DigitalNomadOnFIRE No, I'm looking for a solution to decrease downtime for a company with a very complex database and maintained by 20+ developers. There are more challenges than just database, but my focus is database management and version control to decrease downtime and making it easier to maintain. The project I am talking about is a prototype project, but it's helping me make choices

@DannyM - Check out SSDT. It will allow you to add your database to source control, you can build it in Visual Studio, and you can deploy it with your source code. Here is a video on how to use it: kzfaq.info/get/bejne/n9B0lqt43d_VdWg.html

@@IAmTimCorey yeah and youse dacpac deployment with sqlpackage manager

@@IAmTimCorey I still use SSDT even if I'm doing ORM with code first. I keep them in sync and because I have multiple projects in my solution that don't all use ORM so there are stored procs, views, etc. and then get the added benefit of your database schema being in source control. I've also found I like writing T-SQL in Visual Studio better than SSMS.

Thanks for sharing! As for Repo DB, I have seen it but not used it.

Thanks for sharing.

Thank you!

Agreed. It is also important to be able to benchmark your queries and really understand how to improve their performance, even if EF Core wrote them.

Yes, you can, although I don't simply because I don't like messing with authentication/authorization code to that level. It makes me responsible for security flaws in my code to a much greater extent.

@ghost mall Thanks

@@IAmTimCorey Thanks Tim

Yep, sometimes you have to use what you are given.

I get that. The problem is that since I've stayed away from it for so long, I can't do it justice to teach it at any level of depth. At least not at this point. I don't teach something unless I know it really well. Otherwise, I can lead people down the wrong path. That's why I have not taught EF in more depth. It may be something I take on in the future, but it would be a lot of work.

@@IAmTimCorey I completely understand where you're coming from and I personally prefer dapper myself, hoping never to use EF, but alas, I've just moved teams and found myself working with EF and the poor implementation the other developers have done with it. Thanks anyway. Much appreciate your thoughts on it.

@@IAmTimCorey how recently? A lot of bad things I hear about EF is from developers who barely have used it since core. EF Core 6/7 is very different from EF 6. Sure, if you dont know SQL you probably will make a mess with EF and how you create relations. But that's the same if you manually create tables and have no idea what you are doing. You should learn fundamentals of SQL regardless, but I don't think you need to be an expert in it, unless you want to work as a DBA. Also lets face it, majority of developers will work with small-medium sized apps and EF is fine for that. I've been at places where its the other way around. Legacy databases with several hundreds stored procedures calling into other stored procedures, no one dares touching the database because the DBAs working on it quit a decade ago. So you keep extending the app but building wierd work arounds to support the existing database. Or code using Dapper with SQL in code where a column name change would require manual updates in 30 different handwritten strings and you hope you didn't miss or misspelled any.

Thanks for sharing!

One solution for using Dapper with SQL is to use SSDT. That allows for the database structure to be updated using CI/CD rather than manually.

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/

If you use SSDT, you can put your project in Visual Studio, track the changes in source control, deploy the changes with your application, and you can do things like renaming a column and having all other entities that use that column name (in the SSDT project) rename as well. Here is a video with more info: kzfaq.info/get/bejne/n9B0lqt43d_VdWg.htmlsi=AlVFk0-p3PUR7ekK

Sure, but at some point, you lose enough of the benefits of EF that you start to wonder if the complexity it adds is still less than the value it provides. Most queries that EF does should probably be stored procedures. The way EF gets joined data is inefficient. But then if you try to use a stored procedure, the best option would actually be to have it return two result sets (or run two stored procedures). But EF isn't set up to do that. So now you have to think about dropping out to Dapper. Now you've added more complexity, bypassed more of EF, and in the end aren't getting enough benefits to justify the complexity.

If you use SSDT to develop your database, you can deploy it just like your code (and put it in source control). As for EF doing mapping better, I would disagree. With Dapper, you make the best choice for the situation (one query, multiple queries, etc.) to get the data. With EF, it makes the call to return more data than you need. For instance, if you had a table with ten people in it, and a table with orders in it, and each person had ten orders associated with their account, and you wanted to get a list of people with their orders, EF would return 100 records. That sounds right, but it would return each person 10 times (one per associated record). That's a LOT of extra data transmitted across the wire. As an EF user, you never see that data. You see 10 models, each with 10 sub-models for orders each. Unless you actually look at the query, you don't see the wasteful transmission. With Dapper, you would do two queries and then map the data over. That's a simple (and small) example. The "normal" EF use case is joining multiple tables together (it is a relational database after all), and transmitting hundreds or thousands of times the amount of data across the wire compared to what you actually need.

@@IAmTimCorey EF has a feature called split queries for that exact scenario. You can also use projections, table/entity splitting, and raw sql to shape things differently

I did that in this series: kzfaq.info/sun/PLLWMQd6PeGY0bEMxObA6dtYXuJOGfxSPx We build a WPF app with an API backend in the .NET Framework so that we can learn how to upgrade to .NET Core. We then add a Blazor Server UI as well.

Mostly unavoidable. There is a reason why we don't have generic applications that just fit every scenario. Trying to figure out how to write a performant ORM that works for everyone is practically impossible. EF Core 8 has made some big strides, but there is still a lot that is on the developer to do. You need to customize it to be precise enough for your needs, and even then you will find areas where it just makes a mess.

I've done that with Microsoft Identity because I don't want to mess with authentication/authorization code.

I think I just forgot to mention that, that's the one that I mean for login and security

lol yeah, there are some big gotchas that people miss with the defaults of EF.

This is what I’m doing as well, but it feels unwieldy after a class grows in size. Wondering if I’m not doing it efficiently. Do you have any examples or articles that explain how to do this efficiently?

I used to write my own ADO code because it felt like the most efficient way to write data access code, but Dapper is so small and performant that it is practically the same as direct ADO code, plus it gives me the added performance benefit of having my data mapped correctly to strongly-typed properties in my model. That's a big performance gain.

Appreciate the reply Tim, sounds like dapper is exactly what I’m looking for then! Any videos you recommend for getting started? Should I start with your “Data Access with Dapper and SQL” video? Or any others you suggest? kzfaq.info/get/bejne/mt19ecpn2t61YH0.html&feature=shares

This is the video where I introduce Dapper: kzfaq.info/get/bejne/e9pinst409itoZc.html Then here is an Advanced Dapper video: kzfaq.info/get/bejne/m7Gbm5iJ0paznIk.html After that, the video you listed would be a good next step.

Awesome, sometimes just knowing where to start is half the battle! Thanks again!

Why? You also get the drawbacks of both solutions.

@@IAmTimCorey agree

​@@IAmTimCorey select with dapper is faster while update,delete and insert is faster (code wise) in ef core

Thanks for sharing.

Yeah, making a mess with EF is definitely easy to do.

I get the idea, but the reality isn't that clean. First, clean layers almost always break down in "real" applications over time. Second, even if the layers are entirely clean, you will have a LOT of queries to replicate into SQL statements. I'm working with a small-ish non-profit right now that would need to take probably 3-6 months to just replace their queries with raw SQL statements in order to move away from EF. That's just not viable.

@ghost mall - I'm not blaming the tool. I'm saying that it is being used improperly. When relationships get complex enough, you really need to write custom queries instead of using EF-generated queries. The problem is that most people don't do that. A great tool can be a real problem when it is used improperly.

For sure. You can also call stored procedures. Those options are really helpful when you need to optimize EF.

@IAmTimCorey I am doing it a lot, especially when you have some structures like tree nodes and you need to build a full tree or some other "unusual" tasks like that.

Thanks for sharing!

Thanks!

It's sort of the long way of saying, I spent a couple years trying to disagree with Tim, only to come around to the same conclusion. This video is very honest and graceful, and I've come around to the same understanding. Damn you Tim.

You made me laugh with your follow-up comment. Thanks for sharing.

I agree that having two systems means debugging gets harder. However, there are some things to help. First, the SQL logs will be a LOT clearer and easier to search because you can search for the stored procedure's name rather than trying to figure out what the generated code will look like. That works the other way as well, where if you find something in the logs, it is easy to search your code for the offending bit. Second, your stored procedures should hopefully be rather simple. If you are putting full business logic in your procedures (beyond basic safety checks), you probably want to re-evaluate your planning. If they are simple, there is no debugging needed on the SQL side. If they are more complex, I would recommend unit testing your procedures so that they are rock-solid.

@@IAmTimCorey thanks Tim... not least for deciphering my awful typos!! I think that is perhaps where I have come unstuck. I used SQL long before C# or even VB so tended to put a lot of complex logic in the SPs. After being stuck in my ways, I'm taking a step back and almost going through a new learning curve on this... in fact just watching your other Dapper video now and I wasn't even aware of the SQL project type in Visual Studio!! That alone made the video worthwhile before I even get into Dapper.a

I'm glad it was helpful. Here is a full video on SSDT: kzfaq.info/get/bejne/n9B0lqt43d_VdWg.html

@@IAmTimCorey smashing, thank you!

There's a balance here. I'm doing some consulting work for a company that uses an open-source data system that is built on EF (unfortunately the .NET Framework version). It is designed for small non-profit organizations, so they felt that it "didn't matter". The non-profit I'm consulting with has less than 200 employees, yet their database has millions of records. So small is relative. Also, even though other companies that use the software have smaller databases, they still have performance issues because of EF configuration that they cannot get around easily. It would take a major rewrite to change how the system was structured. It isn't as easy as replacing a bad query with a stored procedure. EF was abused, but it "worked" at smaller scales (basically, they didn't notice enough of a performance hit), so now they are dependent on how EF does things. The "solution" is to move the application to Azure so that they can scale up the application (they've exceeded what a reasonably-priced server can do with tons of RAM and SSDs).

Yes. It uses ADO under the covers and then it maps the return values to model classes. It does so incredibly efficiently, so that the performance including the mapping is not much more than just straight ADO calls.

@@IAmTimCorey thank you, for both replies. Much appreciated.

Nope, Dapper is absolutely a tool for everyone. It is incredibly performant and has been proven in high-throughput situations. In fact, larger apps will more often benefit from Dapper over EF because of the performance gains.

Can Dapper also do things like migrations/scaffolding?

Goint to sql does not necessarily mean writing procedures. You can write sql queries in your c# project and run them with dapper

Dapper is doing a smaller version of what EF does. In fact, it uses the same underlying ADO.NET to make the calls to SQL. EF writes the queries and then executes them on SQL. Dapper asks you to provide the queries and then it executes them. You can use stored procedures (preferred) or you can write the SQL in your C# code and have Dapper execute it. The reason that stored procedures are preferred is because you get the benefits of having your SQL "stuff" in SQL. If you want to optimize a query, you can do it in SQL using your DBA. If your DBA wants to optimize SQL that is in your C# code, they have to give a developer the SQL and have them find out where it is and then make the changes. Even worse, if you are using EF, that optimization might not even be possible. I've heard the "don't do debugging in SQL" argument before, but it doesn't really hold water. Do SQL tasks in SQL and do C# tasks in C#. If your SQL call is incorrect, fix it in SQL. That's the way to properly optimize your system. Doing too much business logic is usually a bad idea in SQL, but so is "preventing" that by putting your SQL code fully inside of your C# code.

I'm not sure I've ever seen a perfectly formed database (outside of a demo).

@@IAmTimCorey Me neither, but I have heard the legends.

That reduces the value of what EF provides to the point that I wonder why Dapper isn't a better choice.

Can you please make a video for that for better understanding. In a project I have to used SP by using EF I will be greatful to you.

In EF we use FromSql to execute the procedure but it's result must be same as Entity type does it also happens while using dapper.

Yes, every tool can be used incorrectly. That's obvious. But EF is known for being abused. As for your old workplace, the problem doesn't sound like the tools they chose. It definitely wasn't because they chose to write raw SQL. It sounds like they either wrote bad SQL or that they had other issues. A tool doesn't fix bad SQL or design issues.

There is a lot to unpack here. 1. Yes, EF is a great ORM. 2. "it generates bad queries" is absolutely an argument. A lot of times that is because of poor usage by the developer, but not always. When you do a join in EF between a Person table and an Order table, if each person has 10 orders, EF will return each person 10 times (one per order). So if you have 100 people, it would return 1,000 records including 900 duplicate Person records. Then if every order has 10 items in it and you want to return those as well, the result will be that you get back 10,000 records with 9,900 duplicate Person records and 9,000 duplicate Order records. EF will then eliminate the duplicates on the client side and display them as nice, neat objects. However, you just transferred all of that extra information across the network. That is not a good query (but it is the best solution EF can do unless you force it to call multiple queries). 3. "99% of the queries are great" - I just pointed out that no, they aren't. Looking at a query in the console doesn't actually tell you anything. You need to test it on SQL and see what it returns and how it performs. You can't see the execution plan in the console. You can't see if it is properly using indexes in the console. Anyone who writes a SQL statement and thinks it is good by looking at it hasn't learned enough about how SQL works. Testing it is the only way to properly evaluate it (and testing it against near-production data, since the data can change whether the query is performant or not). 4. "I want to be able to refactor my database often" - That is a terrible idea. That's not how databases work. You need to have a solid plan going into building your database and once it is built, you need to be very careful how you change it. Trying to do major refactoring on a database once it is in production is a BAD idea. Even doing it in development is a bad idea, since that should have a direct impact on your code as well. That's like saying that you want to change the structure and layout of your house's foundation as you build it. Your data is the foundation of your application. Redoing how it is structured will fundamentally change everything about your application. 5. "I don't want to write joins" - You are working with a *relational* database. The whole point is joins. If you don't want to do joins, you probably want to look at a NoSQL solution instead. Trying to avoid joins by using EF is not the way. 6. "I want strong typed stuff everywhere in my codebase" - That's what C# is - a strongly-typed language. I'm not sure what you think you are avoiding here. I would encourage you to dive a LOT deeper into EF and how it works before you use it in production. Get some more practice in how it works and in testing the results in a real-world scenario (not production, though). Here is a video I did on some best practices to consider when using EF: kzfaq.info/get/bejne/p9F6bM6Wp9O1h6M.htmlsi=omflTeOH_5ed_afF

@@IAmTimCorey "bad queries", ok i just tested this and see what you mean. But how to you solve this? "AsSplitQuery()" seems to work. (5-10% faster in my tests) But thats an advanced feature from EF. (that you don't get with dapper) If i would write it manually i would write one big query "select * from customers left join orders" exactly how EF does it. With "great queries" i mean it doesnt do "stupid stuff" like other ORMs. It can handle child.FirstOrDefault() pretty well and also childCollection.ToList(). I don't care about 10% faster, i just don't want it to generate n+1 queries or something like that. I don't want to write joins manually, its tedious. I strongly have to disagree with "You need to have a solid plan going into building your database". Thats not how reality works. No project i've EVER worked on had a perfect database design. And when you take over mangled projects then everything is a mess. i definitely want to change my database all the time. But arguing that topic strongly depends on the environment. Just be careful if you don't allow your devs to change annoying (database) systems, they might eventually get annoyed working at the project. Strong typed LinQ, not raw sql. But i'm not familiar enough with dapper to compare them. I check out your video, thanks.

@@IAmTimCorey Another great point is that writing raw sql is very prone to errors. I never had a problem with ORMs. But when you write raw sql you constantly have to check if you did all joins correctly and not suddenly have 10x records or just 1/3 because you messed up a join. Raw SQL is even dangerous sometimes, ever forgot a where clause in an update statement? :D I think the options to manually pimp queries is great, but we also have that with an ORM. Do you really want to write joins between 10 tables manually? Do you really never want to refactor the database. Oh the column name was "name" good luck refactoring that in 10000 queries with raw sql where every table has a column called name.

Just a couple of things here that I'll touch on. First, you asked how you would do that join correctly. The answer is to make two separate calls to SQL. That is so much more efficient than trying to flatten objects into rows. If you have multiple SQL calls, you can then join the data in C# using LINQ very efficiently (let SQL do what it does best and let C# do what it does best). By the way, Dapper puts your data into C# objects too, so you can use LINQ, etc. just like you can with EF. When you are building your application in the first place, you might make significant database changes (although, that is still VERY expensive). However, once your application is running, that's a bad idea. You are approaching your database like it is only used by your one application. You don't do reporting? You don't have a data warehouse? You don't have any other application that needs your data? If you do, you break all of those so your application can be better. If you struggle writing good SQL, you are definitely going to struggle writing good EF queries.

Thanks for sharing.

Thanks for sharing.

They've made a lot of improvements in .NET Core (now just .NET), but the reliance on the "magic" of EF to "just work" is still there in the documentation.