In my experience with EF, I can normally write >= 99% of my queries with LINQ to entities. On the rare occasion that something extra complex is needed you can always move your query to a stored proc and execute it with the EF FromRawSQL() method. In ultra rare occasions you can also drop straight into ADO and do whatever you need with that.
This is so true. I have been using EF for years and it can get frustrating at times. It is difficult to anticipate what query it is going to generate. I have to constantly use SQL Profiler to check if it generating the query as expected or not. And this is usually because the LINQ queries can get so weird in syntax it is difficult to remember especially group by and outer joins.
My philosophy with EF is simply to jump through the escape hatch to SQL at any sign of minor difficulty. My tolerance for EF is limited to the most simple of data access or simple update, which is vast majority of them. No point in learning wacky complex syntax, keyword, rarely-used settings just so EF would generate the performant SQL you are expecting, just cut EF out and write them yourself.
The first Dapper con about spelling could be avoided if you were using an IDE like Rider. It can validate your query against the database during development.
@@lukeconner On the Database tab in the gutter on the right edge of the window. You can connect to your database of choice and download the schemas for your db. might need to play around with a setting called "SQL Resolution Scopes" to get it to work. Pretty great stuff.
Thank you, this is very helpful. I am a dinosaur in this regard, I’m still querying the database with raw queries with a reader and stream it into a list of custom object. It gives me full control over the query (I can run complex joins etc) and also report progress as it reads along (can implement progress bar etc). So for me, working with sql queries is a big bonus, so Dapper for me.
Does it really have to be one or the other? What about using repositories and implement them with EF when there is no need for more control and Dapper when the EF would create some horrible db queries? Also it is good to know that there are add-ons to Dapper like Dapper.Contrib that makes it easier to work with Dapper. Regarding complex left joins and group by's in EF, I think I would prefer to solve that issue by writing views or stored procs in the database rather than having the complexity in the C# code.
EF has the ability to run raw sql queries as well, not only stored procedures or data modification queries. The only thing which is tricky in EF is getting multiple result sets from query or sp.
The first dapper pro - Entity can call the query directly exactly like dapper and have it serialize properly back exactly like dapper. You could ignore the entity framework sql generation entirely, easily, if you want.
You can add navigation properties at build time (no change to database) by extending the generated partial classes on a scaffolded database in case you dont have access to fix a shitty database schema or just don't want to touch it. You can then use Include in linq to build the query (and ThenInclude, also in some cases use query splitting). Ofc profile and inspect queries (redgate ANTS is great for this), if nothing else you'll see what indexes are missing and fix that if you have access to the database. In general EF6 creates pretty good queries if you follow some best practices, and EF7 is a big improvement in that and many other areas. There's really nothing quite like EF when migrating legacy systems (db first), but as powerful and convenient as it is you really have to know what you're doing when you run into some niché edgecase and things arent working, usually it can be done right with EF without resorting to stored procs etc but it's tricky. That's true with all ORMs though, just maybe a bit more with EF and LINQ as it actually has the ability abstract away so much more.
For CRUD with dapper you could use Dapper.Contrib as well to get an EF-like experience, but still retain all the plusses with dapper for more advanced querys.
The only real problem with both is if you have a bigger team, you'll want to define when you want to use both so you don't end up with a mix based on whichever developer wrote that particular method.
I tottaly agree, I'm also database first developer, I somehow have much more confidence when I design database than just let EF to do all the work :) I also use entity developer from devart which works really well to sync database changes back to code
Dapper strikes me as an excellent candidate for a generated data layer. If I get the opportunity to reimplement our data layer this is the approach I would take. Ironically I would likely use the EF generated model as the basis of generating the Dapper code.
I like the idea of the video. This question is very important. I just have one question. If we already have EF in the project, what the reason to add Dapper? We can just do a raw SQL query with EF and don't even need to think about Open/Close connection. Are there something I missed with Dapper?
@@lukeconner Agree. But the question you raised affects the decition if I need to start using Dapper or EF. With your expirience, do you think there are cases when Dapper still have benefits even you already have EF in the project?
If you think the vast majority of your project is fine with EF and you may need the 1 off raw sql, go EF. If you think your project is going to need more than the 1 off raw sql you might as well go dapper so it's more consistent.
*Controversial Opinion* - I enjoy using EF for code first and writing to the DB. Dapper is then used for my reads. However, I do all my testing in SSMS before I write any code for EF or Dapper. I'm coming across too many projects recently where the devs are using EF because they know LINQ but have no clue about DB's, indexes or even how to see what SQL EF is generating. In my opinion, EF is a great tool that should only be used by experienced devs that understand the code behind it and how the DB works. If they don't then they're trying to run before they can walk which more often than not, leads to poor DB performance over time and the tool being blamed rather than the dev. This is also becoming worse with the more DB adapters that EF is supporting. Devs are saying they can work with many DB's just because they know EF supports it, but have no understanding of what differentiates each DB or their capabilities.
Rider supports SQL queries that must be as external files and have tests. If you replace T-SQL with something else, let's say PL/pgSQL, then it will be as hard to understand as an expression tree. Expression trees in EF are tweekable. Also, your query is ugly and must be refactored. For instance, you can use the Specification Pattern.
I have always used EF + LINQ. If the query is slow etc - IMO better to optimise the overall architecture than tinkering with a query itself. Cuz when you pass the Pareto's 80% in particular app development, codebase becomes insane to manage and such help as models / query management by EF is a game changer at that point. If the the app reaches complexity beyond the ability to maintain it - it's game over. EF pushes that edge far away. Second must have is LINQ + expression trees, when multiple frameworks can dynamically extend the query. Let's say you have UI framework on FE to manage complex grid (filtering, pagination, etc) - it does push that filter to the backend, where its translated into query with expression trees, and then you can chain another additional filters into that query, there are so many things that can be done with FE, you can write your own filters for json db fields so easily, translators, etc. Dapper offers performance only, and the gap constantly shrinks with new EF versions, so for me, like, dapper is 0xdeadbeef.
TLDR: EF Core feels much better if you stop trying to be the DB guy, and just use embrace it's object nature. And please use lambda based LINQ Very good video, but I think that the reason why you seem not like EF Core that much, is because you think about the database too much. It sound weird, but hear me out :) I've been in many projects using EF and after we moved from the old EF to EF Core, there is almost no need to use Dapper (with exceptions of using like a db view, but that will change soon also with new versions of EF Core). At least 95% of the queries is select, filter, maybe count. Most of them need full related objects. Writing queries for that is as simple as slapping Include on the query. Sometimes the query comes out wonky, but you can still execute raw SQL with EF Core and map it to objects (but they have to be in your DbContext). You can enable query logging so in developlment (or in prod if you want) you can see every SQL query that the framework executes, with parameters. On top of that, if you cannot trust unexperienced dev with EF Core, you definetely cannot trust them with dapper. Even you in your video showed a query vulnerable to SQL injection to showcase how to achieve a simple Where cluase. For dapper something like SQLKata is a must, but it gets a bit unergonomic if you use a lot of parameters for your query and you want to build your query depending on some conditions. As for the left joins.... Why do you use that flavor of LINQ? I know why, because it's like SQL. So you want to write it like sql. You want a left join? It's Include(x => x.AnotherTable). If there is nothing there, you get a null property. You want a right join? That means that you can start with the other table and include the first one., if you set up the relations between models correctly. You can use conventions, like CategoryId and Category will be in sync. Category can have List, so you get both ways "navigation". For free. The Db shape is exactly the same. Then, about the repositories, I personally find them mostly redundand. EF Core is a repository in itself, why double the layer? Just pass IQueryable around and you can get very efficient with calls that further filter down results, or even just check if there is any result. You don't have to write a repository method for that, just finish the query with Any(). You do things like automatic updates of ModifiedAt etc in interceptors either way (very cool feature btw :)). For this specific query you prefer Dapper? Inject DbConnecion, and use dapper. DbConnection has nothing to do with EF Core. Or write a Dapper QueryProvider, there is so many ways to solve this.
How would you call yourself a backend developer if you know nothing about the database? You can profile the queries EF is generating and EF has tools that allow you to fine-tune the queries being generated. You could write bad queries in SQL as well. So you need a good understanding of LINQ and how it works rather than blaming it for bad queries. There are actually tutorials on Left Joins and Group By clauses on Microsoft Learn and many other sites. I think its a false con.
I think it's a bad idea to use a raw sql in your code, I would suggest to use stored procedures instead, much cleaner and you move the responsibility to the db side.
Honestly, if you want to be a real developer, you should know both and be able to use both efficiently and correctly. If you don't, time to get crackin.
In my experience with EF, I can normally write >= 99% of my queries with LINQ to entities. On the rare occasion that something extra complex is needed you can always move your query to a stored proc and execute it with the EF FromRawSQL() method.
In ultra rare occasions you can also drop straight into ADO and do whatever you need with that.
For me, when it's too complex for EF I usually just use a stored proc.
It is also simple to let dapper user the same connection as ef core. even sharing the transaction.
@@prowhiskey2678 I did the same thing in an app that has both
This is so true. I have been using EF for years and it can get frustrating at times. It is difficult to anticipate what query it is going to generate. I have to constantly use SQL Profiler to check if it generating the query as expected or not. And this is usually because the LINQ queries can get so weird in syntax it is difficult to remember especially group by and outer joins.
My philosophy with EF is simply to jump through the escape hatch to SQL at any sign of minor difficulty. My tolerance for EF is limited to the most simple of data access or simple update, which is vast majority of them. No point in learning wacky complex syntax, keyword, rarely-used settings just so EF would generate the performant SQL you are expecting, just cut EF out and write them yourself.
You should format your dapper queries using raw string literals from C# 11. That's one con gone
The first Dapper con about spelling could be avoided if you were using an IDE like Rider. It can validate your query against the database during development.
I actually am using Rider. How do I turn that feature on?
@@lukeconner On the Database tab in the gutter on the right edge of the window. You can connect to your database of choice and download the schemas for your db. might need to play around with a setting called "SQL Resolution Scopes" to get it to work. Pretty great stuff.
@@MrSamKeene I’m so happy you pointed that out, this will definitely solve some headaches for me.
Does anyone know if Visual Studio has a similar validation feature for Dapper? Thanks! 👍
@@imaginative-monkey I looked but couldn’t find anything.
Dapper +sqlkata for the read
Ef core for the writhe and for handle db migration.
Best combination for me
Couldn't agree more
Luke, great video with cool insights. Subscribed.
Thanks, I’ll be posting regularly again
Any specific topics you would like me to cover?
this is enlightening! thanks!
Thank you, this is very helpful. I am a dinosaur in this regard, I’m still querying the database with raw queries with a reader and stream it into a list of custom object. It gives me full control over the query (I can run complex joins etc) and also report progress as it reads along (can implement progress bar etc). So for me, working with sql queries is a big bonus, so Dapper for me.
The biggest benefit you’ll get, aside from far less lines of code is being able to query right into objects. It removes lots of boiler plate code.
Does it really have to be one or the other? What about using repositories and implement them with EF when there is no need for more control and Dapper when the EF would create some horrible db queries? Also it is good to know that there are add-ons to Dapper like Dapper.Contrib that makes it easier to work with Dapper. Regarding complex left joins and group by's in EF, I think I would prefer to solve that issue by writing views or stored procs in the database rather than having the complexity in the C# code.
My conclusion in the video was use the right one for the right occasion. They both have pluses and minuses.
EF has the ability to run raw sql queries as well, not only stored procedures or data modification queries.
The only thing which is tricky in EF is getting multiple result sets from query or sp.
One of the reasons we had to put all of the queries into sprocs with Dapper. SQL embedded in the code is unmaintainable in larger projects.
Which fonts are these? Thanks
I can double check, but I’m using rider, so it’s just the default font in rider.
@@lukeconner Can you please check and let me know? I've just installed Rider on my Windows 10 OS and fonts are not the same.
@@ax8635 It's called Jetbrains Mono
The first dapper pro - Entity can call the query directly exactly like dapper and have it serialize properly back exactly like dapper. You could ignore the entity framework sql generation entirely, easily, if you want.
That's true, but I was trying to go through the primary reasons one might use to compare and decide between the two.
You can add navigation properties at build time (no change to database) by extending the generated partial classes on a scaffolded database in case you dont have access to fix a shitty database schema or just don't want to touch it. You can then use Include in linq to build the query (and ThenInclude, also in some cases use query splitting). Ofc profile and inspect queries (redgate ANTS is great for this), if nothing else you'll see what indexes are missing and fix that if you have access to the database. In general EF6 creates pretty good queries if you follow some best practices, and EF7 is a big improvement in that and many other areas. There's really nothing quite like EF when migrating legacy systems (db first), but as powerful and convenient as it is you really have to know what you're doing when you run into some niché edgecase and things arent working, usually it can be done right with EF without resorting to stored procs etc but it's tricky. That's true with all ORMs though, just maybe a bit more with EF and LINQ as it actually has the ability abstract away so much more.
Hey man, please zoom in in your editor, I can't see anything on mobile.
I'm working on some more videos, and I made sure it was better for the next one.
For CRUD with dapper you could use Dapper.Contrib as well to get an EF-like experience, but still retain all the plusses with dapper for more advanced querys.
Great video Luke, thanks! I would suggest increasing the font for mobile viewers. I wish you the best of luck with your channel 🫶🏼
Yeah, after I posted it I realized it was too small. I’m going to try and experiment a bit and see if I can get it better.
Why not both?
The only real problem with both is if you have a bigger team, you'll want to define when you want to use both so you don't end up with a mix based on whichever developer wrote that particular method.
I tottaly agree, I'm also database first developer, I somehow have much more confidence when I design database than just let EF to do all the work :) I also use entity developer from devart which works really well to sync database changes back to code
Dapper strikes me as an excellent candidate for a generated data layer. If I get the opportunity to reimplement our data layer this is the approach I would take. Ironically I would likely use the EF generated model as the basis of generating the Dapper code.
I’ve done that. I love using EF to generate my database objects.
I like the idea of the video. This question is very important.
I just have one question.
If we already have EF in the project, what the reason to add Dapper?
We can just do a raw SQL query with EF and don't even need to think about Open/Close connection.
Are there something I missed with Dapper?
If it’s already baked in and working, I wouldn’t change it.
@@lukeconner Agree. But the question you raised affects the decition if I need to start using Dapper or EF.
With your expirience, do you think there are cases when Dapper still have benefits even you already have EF in the project?
If you think the vast majority of your project is fine with EF and you may need the 1 off raw sql, go EF. If you think your project is going to need more than the 1 off raw sql you might as well go dapper so it's more consistent.
Meaning raw sql is a Dapper strength, so if it's going to be needed more go for it's strength. EF can do raw sql, but it's not it's strength.
@@lukeconner thanks for the answer. I'm just wondering what EF can't do but Dapper can...
I'm a database first guy as well. Do you miss .edmx like me? 😆
Database first always just made more sense to me.
Look entity developer from devart, great tool, better then edmx in my opinion :) I use it for many years for EF 6 and dotnet core
@@salk52 The entity developer from devart is not a free product & hell expensive
@@LiakatHossain well it depends what you are doing. I'm software developer and it speeds my development process and time is money :)
*Controversial Opinion* - I enjoy using EF for code first and writing to the DB. Dapper is then used for my reads. However, I do all my testing in SSMS before I write any code for EF or Dapper. I'm coming across too many projects recently where the devs are using EF because they know LINQ but have no clue about DB's, indexes or even how to see what SQL EF is generating.
In my opinion, EF is a great tool that should only be used by experienced devs that understand the code behind it and how the DB works. If they don't then they're trying to run before they can walk which more often than not, leads to poor DB performance over time and the tool being blamed rather than the dev. This is also becoming worse with the more DB adapters that EF is supporting. Devs are saying they can work with many DB's just because they know EF supports it, but have no understanding of what differentiates each DB or their capabilities.
Rider supports SQL queries that must be as external files and have tests. If you replace T-SQL with something else, let's say PL/pgSQL, then it will be as hard to understand as an expression tree.
Expression trees in EF are tweekable. Also, your query is ugly and must be refactored. For instance, you can use the Specification Pattern.
To me EF only has two advantages. 1 specific column updates 2. Dynamic queries from ui where,sort,count,skip,page
SqlKata on top of dapper is much better, than writing raw sql in code, I think. Gives u syntax sugar
I have always used EF + LINQ. If the query is slow etc - IMO better to optimise the overall architecture than tinkering with a query itself. Cuz when you pass the Pareto's 80% in particular app development, codebase becomes insane to manage and such help as models / query management by EF is a game changer at that point. If the the app reaches complexity beyond the ability to maintain it - it's game over. EF pushes that edge far away. Second must have is LINQ + expression trees, when multiple frameworks can dynamically extend the query. Let's say you have UI framework on FE to manage complex grid (filtering, pagination, etc) - it does push that filter to the backend, where its translated into query with expression trees, and then you can chain another additional filters into that query, there are so many things that can be done with FE, you can write your own filters for json db fields so easily, translators, etc. Dapper offers performance only, and the gap constantly shrinks with new EF versions, so for me, like, dapper is 0xdeadbeef.
TLDR: EF Core feels much better if you stop trying to be the DB guy, and just use embrace it's object nature. And please use lambda based LINQ
Very good video, but I think that the reason why you seem not like EF Core that much, is because you think about the database too much. It sound weird, but hear me out :)
I've been in many projects using EF and after we moved from the old EF to EF Core, there is almost no need to use Dapper (with exceptions of using like a db view, but that will change soon also with new versions of EF Core). At least 95% of the queries is select, filter, maybe count. Most of them need full related objects. Writing queries for that is as simple as slapping Include on the query.
Sometimes the query comes out wonky, but you can still execute raw SQL with EF Core and map it to objects (but they have to be in your DbContext). You can enable query logging so in developlment (or in prod if you want) you can see every SQL query that the framework executes, with parameters.
On top of that, if you cannot trust unexperienced dev with EF Core, you definetely cannot trust them with dapper. Even you in your video showed a query vulnerable to SQL injection to showcase how to achieve a simple Where cluase. For dapper something like SQLKata is a must, but it gets a bit unergonomic if you use a lot of parameters for your query and you want to build your query depending on some conditions.
As for the left joins.... Why do you use that flavor of LINQ? I know why, because it's like SQL. So you want to write it like sql. You want a left join? It's Include(x => x.AnotherTable). If there is nothing there, you get a null property. You want a right join? That means that you can start with the other table and include the first one., if you set up the relations between models correctly. You can use conventions, like CategoryId and Category will be in sync. Category can have List, so you get both ways "navigation". For free. The Db shape is exactly the same.
Then, about the repositories, I personally find them mostly redundand. EF Core is a repository in itself, why double the layer? Just pass IQueryable around and you can get very efficient with calls that further filter down results, or even just check if there is any result. You don't have to write a repository method for that, just finish the query with Any(). You do things like automatic updates of ModifiedAt etc in interceptors either way (very cool feature btw :)).
For this specific query you prefer Dapper? Inject DbConnecion, and use dapper. DbConnection has nothing to do with EF Core. Or write a Dapper QueryProvider, there is so many ways to solve this.
How would you call yourself a backend developer if you know nothing about the database? You can profile the queries EF is generating and EF has tools that allow you to fine-tune the queries being generated. You could write bad queries in SQL as well. So you need a good understanding of LINQ and how it works rather than blaming it for bad queries. There are actually tutorials on Left Joins and Group By clauses on Microsoft Learn and many other sites. I think its a false con.
I think it's a bad idea to use a raw sql in your code, I would suggest to use stored procedures instead, much cleaner and you move the responsibility to the db side.
Awful quality of code text (not to mention it's small)
Honestly, if you want to be a real developer, you should know both and be able to use both efficiently and correctly. If you don't, time to get crackin.