I am happy that you started this. Actually we need a combination of efcore with dapper contrib and insert data there should be condition for class like write(false) to protect data insert exception for nesting data
Hi, I have a quick question. Do I really need DTO models in my clean CQRS application? I mean if I use Command with properties to create and records response to return data is there anywhere place for typical DTO models?
Nice! Didn't use dapper so far. I really like sql statements but I don't like them in code somehow (same with sql like Linq queries) But good to see that EF Core 7 can keep up with Dapper 😁. BTW... You broke your clean architecture because of the sql and I broke my smartphone because of that heavy smashing. Please staaaahp Good job 🙂
I still think it's Clean Architecture regardless! Dogmatic design be damned 😁 I also dislike SQL in code, but there's no better way, to be honest. And no, I won't stop! I will intensify 🔥
@@MilanJovanovicTech Yeah I'm with you. Clean Architecture is more an orientation to me. I really like it and the separation. But each project has different requirements that can't always fit into that dogmatic approach. Same with DDD, it's not always suitable, but you can still use clean architecture without domain and put everything into your application layer. As long as the team is fine with the "violations", I'm too 😁 I'll send you my broken phone to fix it if you do 😋
I think this is just an example. In real world you would probably use a repository that is implemented and lives in your infra layer which is where the SQL query would live.
@@pilotboba I absolutely agree with you! But for the sake of the video title, it was okay. The bad thing and also what really hurt me is, that @Milan removed the repository from the handler. He has already a repository, and also a infrastructure and even a persistence layer. In a real world, he just should've replaced the code within the repository on one of those layers or decorated a new member repository with a "dapper proxy".
hello milan, why dont we Use dapper in the infrastructure layer ?? so we dont violate the rule that "The application layer should not have access to external resources" ??
good study. how could take advantage of this professional architecture with oracle using sql queries with transaction and commit correctly. as well as a clean architecture project in .Net 7.0
Might be a dumb question but why are you making a separate connection factory class? Could we just create a new connection normally in the method? I am probably missing something obvious here.
Of course. I mainly create these abstractions for testability. Sometimes I may want to do additional things with the connection, so it makes sense to have it one place.
Kind of weird comparison. First you are not showing the EF Code, so there could be all kind of stuff behind that repository. Second Dapper does not have change tracking and EF does (or did you use AsNoTracking, again we can't see).
@@MilanJovanovicTech It's not about using SQL in handlers, purpose of having repository is to have your data access logic inside them, so that underlying data store is changed or data access logic is changed you still can introduce new repository implementation while keeping the repository interface as it is and its dependency. So in this case what you could have done is to introduce new repository implementation with Dapper
Рік тому+1
I usually use the linq2db library instead of dapper. That's pretty good on performance, and less abstract than efcore.
Yes indeed. I had a chance to work with it while doing a project based on nopCommerce. They used to go with EF but switched to linq2db recently. I didn’t play too much with it but it looks promising.
Its 10% faster but 1000% less maintainable. Try refactoring your raw sql once you have 1000 queries in your application. In a big company i would do it this way. There you're not allowed to change the database anyway and once the project gets unmaintainable it will get scrapped or replaced. If you love your work then don't write raw sql. Same goes for stored procedures that you can never change again cuz now 10 apps rely on them without telling you.
Again. Just because you don't like raw SQL doesn't mean it's bad or "less maintainable". In fact, people that are good with SQL will argue that it's more maintainable than C# code. Maybe there's a learning curve here?
@@MilanJovanovicTech i've written raw sql for about 5 years then i switched and never looked back. I'm not a total pro but probably more proficient in sql than the avg dev. Unless you have great tools its not maintainable. Strings are not maintainable.
Thanks for compiling this video. How does the abstraction you suggested would look like in the infrastructure? would be a repository like or service abstraction? I hope that abstraction would help us solving the unit test challenges of the handler?
I only now noticed that you have World of Warcraft. I used to play Burning Crusade in year 2010. I've hit 2200 rate points on arena 2v2 back there (I was playing mage). It was one of the best moments in my life
I was a kid when original Wrath released, just entered high school. Enjoying Wrath classic very much right now. 😁 I was never really much into PvP but that is an impressive rating buddy!
Hi Milan, i have a question about parameters. What is the best way to work with nullable params? For example if i want to be able to filter table by username and email, but in case that email is null i want to ignore it and filter it only by username. I know its possible to use sql OR but does dapper has some functionality to handle that kind of case?
Thanks for the content. Good demonstration but the conclusion is misleading. What you see 20ms response time in swagger UI includes routing, model binding, serialization etc. If the aim is to compare EF to Dapper on speed, using benchmark with multiple types of commands and queries would be a better approach. By querying only one object with first result, there will be no big difference especially if you eyeball the result based on api response time.
Hello, I am creating a project using dapper with onion architecture. Since I was using Dapper, I could not adapt the identity and jwt token packages to the project. Can you help with this?
I cannot fathom why would someone willingly use something that will crash if the order of properties in the MemberResponse is swapped, without any indication of an error. And it's a runtime error on top.
@@MilanJovanovicTech So, can i register that implementation as singleton in my IHost worker which use background service that is processing a file and then save in database?
@@MilanJovanovicTech Why Singleton? If there are multiple concurrent requests to API endpoint then how this Singleton connection will handle all those? So for Singleton this Sql connection will remain open forever till application closes?
@@etechguide7251 Well, no... Notice that the SqlConnectionFactory just creates a new connection and returns it. It's up to the consumer to dispose of it.
It would be better to use DbDataSource (or DbProviderFactory at least) instead of SqlConnectionFactory. Btw, Dapper doesn't support positional parameters and DbBatch that are useful for Npgsql.
@@MilanJovanovicTech yes, but if you just need to run a raw SQL - is required to install one more dependency? I hope I will find some time to do different comparisons. I like Dapper but I don't have an answer to that question.
Such a good video, Milan! And, thinking of such usage of Dapper, I always have a question in such use cases: can we just put Dapper stuff into Repository or it's bad idea? Because I think that it would be a great idea, but I have strugglings about mixing EF Core and Dapper together in one class
Thanks a lot! :) I've done that before - putting Dapper & EF in same repository. However, I mostly used Dapper for UPDATE/DELETE queries in such cases.
Jumping in, are there any reasons not to use queries within the repository? One benefit of putting it in a repository is that it can be used by many handles
Thanks for your video! I didn't get why you said Dapper is 'definitely' faster. In this particular scenario, the AVG and Median produced by EF were better and Min was just 3 ms faster, so in your 'performance testing' EF is the definite winner. (Of course, I'm not taking into consideration the cold start of EF query) I've just done the same simple comparison using K6 with my pet project, and I can say that I can't see any difference comparing the same query executing with EF and Dapper (or maybe my PC is just too noise due to big amount of background work to show correct result as dapper might be a little bit faster in theory) So the conclusion is not very informative in my opinion, cause I think it is not about the performance of the same queries, it's about the 'linq or code first approach' and 'database first approach'. IMHO, I believe this performance boost using Dapper is so miserable compared to the features that are provided by EF. Projects that use 'raw SQL with dapper' or 'procedures with dapper' become unsupportable very easily, so I think EF should be picked as a primary tool in the first place and dapper can be used in some corner cases when we need to write something really complex. Also, we are all .Net developers and most of us don't know how to write effective SQL queries.
@@MilanJovanovicTech I'm a big proponent of EF vs Dapper in my teams, whereas most aren't and they favour Dapper. One of the touted reasons is that with EF, you don't need to know SQL and I think that's such a simplification. Yes, you can build quick projects using EF and see quick results, but when getting near a large-scale production database you absolutely need to know SQL to make sure you aren't killing the database while executing any EF stuffs. You need to know SQL for both approaches if you want to go further than entry-level stuff, basically. One thing I have noticed is that intermediate and senior developers in my company do not know EF past the simple stuff and they don't see the value in learning it when they can just use something like Dapper paired with SQL knowledge. Which I think is a shame. The age-old "Dapper is faster" is definitely not as valid as it once was and I much prefer the abstractions that EF gives.
Another great video. Just out of curiosity, how would you implement the UOW when using Dapper? I'm not a fan of EF in any of it's versions and I'm not convinced that EF hadn't cached the result from the first call in your test (I'm pretty sure SQL server would have at least cached the execuption plan), so I'm not sure of the validity of the test, but I know that EF has improved over the years, I just remember it when it was a pile of horse manure. Anyway, I tend to just use raw ADO as I'm old school, but have quite often struggled with the UOW, which is much easier to achieve in EF, so any thoughts you have on this would be appreciated.
Great video as always :) can you do video about registering and configuring class library in web app ConfigureServices method of Startup. For example we have some class library in which we have api client calling some external api's. and we need to add it to our web app and configure stuff like url, name itc ....
@@MilanJovanovicTech not HttpClient per se. I am talking about pattern for registering and configuring nuget packages. For example we have class library and we wanna use options patter to enable users of our package to configure some stuff, like we do is startup class when we import nuget package. That was my idea, i think it would be really informative
One big benefit of using dapper is you can access internal sql functions that is not accessible when using EF. I think a combination is good where easy non critical db operations can use EF while on the more performance critical db operations you can opt in for dapper. Good video 👍⭐️
You can access internal functions in sql by creating a static method in the db context and decorating it with [DbFunction(Name="")]. You use it in your ef core query function when you need it
Want to master Clean Architecture? Go here: bit.ly/3PupkOJ
Want to unlock Modular Monoliths? Go here: bit.ly/3SXlzSt
I am happy that you started this. Actually we need a combination of efcore with dapper contrib and insert data there should be condition for class like write(false) to protect data insert exception for nesting data
Thanks!
I've used multiple times, thanks to you.. keep the great work lad💪🏻
Thanks!
Thanks... Here we are fetching only records. If we try to get more 50-100 records at time then it will more clarity... Which is Faster.
Dapper is pretty much always faster
Hi, I have a quick question. Do I really need DTO models in my clean CQRS application? I mean if I use Command with properties to create and records response to return data is there anywhere place for typical DTO models?
If you are fine with exposing your command to the API, you don't need a request DTO
Very nice video.
What theme do you use for VS 2022?
ReSharper syntax highlighting
Nice! Didn't use dapper so far. I really like sql statements but I don't like them in code somehow (same with sql like Linq queries)
But good to see that EF Core 7 can keep up with Dapper 😁.
BTW... You broke your clean architecture because of the sql and I broke my smartphone because of that heavy smashing. Please staaaahp
Good job 🙂
I still think it's Clean Architecture regardless! Dogmatic design be damned 😁
I also dislike SQL in code, but there's no better way, to be honest.
And no, I won't stop! I will intensify 🔥
@@MilanJovanovicTech Yeah I'm with you. Clean Architecture is more an orientation to me. I really like it and the separation. But each project has different requirements that can't always fit into that dogmatic approach. Same with DDD, it's not always suitable, but you can still use clean architecture without domain and put everything into your application layer.
As long as the team is fine with the "violations", I'm too 😁
I'll send you my broken phone to fix it if you do 😋
I think this is just an example. In real world you would probably use a repository that is implemented and lives in your infra layer which is where the SQL query would live.
@@pilotboba I absolutely agree with you! But for the sake of the video title, it was okay.
The bad thing and also what really hurt me is, that @Milan removed the repository from the handler.
He has already a repository, and also a infrastructure and even a persistence layer. In a real world, he just should've replaced the code within the repository on one of those layers or decorated a new member repository with a "dapper proxy".
Did u try calling stored procedure in dapper?
Looking pretty great
Thanks!
Task which package you use for the "Result" keyword, or is it a model in Domain or another layer
I made my own, but you can take a look at FluentResults or something like that
Like the ErrorOr library by @amantinband😇
You didn't show the dependency injection for the SQL connection factory, do you inject as singleton, scoped or transient?
Could be transient/singleton - since it always returns a new SqlConnection
hello milan, why dont we Use dapper in the infrastructure layer ?? so we dont violate the rule that "The application layer should not have access to external resources" ??
Go for it
What is better milan to return in handler result or throw exception if something wrong ?
Both are okay, if you understand the pros and cons. So just stick to one of them.
good study. how could take advantage of this professional architecture with oracle using sql queries with transaction and commit correctly. as well as a clean architecture project in .Net 7.0
Dapper works with Oracle just fine
Might be a dumb question but why are you making a separate connection factory class? Could we just create a new connection normally in the method? I am probably missing something obvious here.
Of course. I mainly create these abstractions for testability. Sometimes I may want to do additional things with the connection, so it makes sense to have it one place.
@@MilanJovanovicTech Thanks for the response 💪
Kind of weird comparison. First you are not showing the EF Code, so there could be all kind of stuff behind that repository. Second Dapper does not have change tracking and EF does (or did you use AsNoTracking, again we can't see).
It's using AsNoTracking, yeah
Why didn't you implement another version of Repository instead of putting all SQL queries and connections into the handler?
Do you think it's wrong to use SQL in handlers?
@@MilanJovanovicTech It's not about using SQL in handlers, purpose of having repository is to have your data access logic inside them, so that underlying data store is changed or data access logic is changed you still can introduce new repository implementation while keeping the repository interface as it is and its dependency. So in this case what you could have done is to introduce new repository implementation with Dapper
I usually use the linq2db library instead of dapper. That's pretty good on performance, and less abstract than efcore.
I actually never worked with linq2db, quite interesting
Yes indeed. I had a chance to work with it while doing a project based on nopCommerce. They used to go with EF but switched to linq2db recently. I didn’t play too much with it but it looks promising.
Hi Milan, nice walk through. I am getting an issue of "The ConnectionString property has not been initialized." Am i missing something?
You're missing the connection string it seems?
Its 10% faster but 1000% less maintainable.
Try refactoring your raw sql once you have 1000 queries in your application.
In a big company i would do it this way. There you're not allowed to change the database anyway and once the project gets unmaintainable it will get scrapped or replaced.
If you love your work then don't write raw sql. Same goes for stored procedures that you can never change again cuz now 10 apps rely on them without telling you.
Again. Just because you don't like raw SQL doesn't mean it's bad or "less maintainable".
In fact, people that are good with SQL will argue that it's more maintainable than C# code.
Maybe there's a learning curve here?
@@MilanJovanovicTech i've written raw sql for about 5 years then i switched and never looked back. I'm not a total pro but probably more proficient in sql than the avg dev.
Unless you have great tools its not maintainable. Strings are not maintainable.
Interesting
Thank you!
SqlKata is a good alternative as well (actually, it uses dapper under the hood to build queries)
So is the overhead worth it?
@@MilanJovanovicTech With SqlKata you can make queries which are independent of database provider. In some cases it may be benefitial.
If you are manually specifying/writing SQL statements to query the database using Dapper, do you have to worry about SQL Injection issues?
No, Dapper takes care of that as long as you use parameterized queries
Thanks for compiling this video.
How does the abstraction you suggested would look like in the infrastructure? would be a repository like or service abstraction?
I hope that abstraction would help us solving the unit test challenges of the handler?
Either is fine, and yes, it will help with unit testing for sure
I only now noticed that you have World of Warcraft. I used to play Burning Crusade in year 2010. I've hit 2200 rate points on arena 2v2 back there (I was playing mage). It was one of the best moments in my life
I was a kid when original Wrath released, just entered high school. Enjoying Wrath classic very much right now. 😁
I was never really much into PvP but that is an impressive rating buddy!
Hi Milan, i have a question about parameters. What is the best way to work with nullable params?
For example if i want to be able to filter table by username and email, but in case that email is null i want to ignore it and filter it only by username.
I know its possible to use sql OR but does dapper has some functionality to handle that kind of case?
Just use some sort of query builder
Wondering the performance difference between Microsoft.Data.Sqlite and System.Data.SQLite.Core
I have no idea
Thanks for the content. Good demonstration but the conclusion is misleading.
What you see 20ms response time in swagger UI includes routing, model binding, serialization etc.
If the aim is to compare EF to Dapper on speed, using benchmark with multiple types of commands and queries would be a better approach. By querying only one object with first result, there will be no big difference especially if you eyeball the result based on api response time.
This is not a scientific conclusion by any means. 😁
But I'm more interested in the full flow that includes the API than just raw data access.
Out of curiousty, did you explain your DomainErrors calls in any of your videos? I'd like to rewatch it.
Check out this:
ua-cam.com/video/KgfzM0QWHrQ/v-deo.html
@@MilanJovanovicTech Thanks mate!
Hello, I am creating a project using dapper with onion architecture. Since I was using Dapper, I could not adapt the identity and jwt token packages to the project. Can you help with this?
Nope
what if using ef core raw sql with asnotracking ? Dapper still faster ?
I didn't measure, but most likely Dapper is slightly faster or they're the same
I cannot fathom why would someone willingly use something that will crash if the order of properties in the MemberResponse is swapped, without any indication of an error. And it's a runtime error on top.
It it has a parameterless ctor, it should be fine
what is cancelation token it is all over the places ?
Used for propagating request cancellation down the call chain
How should i register the implementation of ISqlConnectionFactory as a scoped or transient Lifetime?
Transient or Singleton is perfectly fine.
@@MilanJovanovicTech So, can i register that implementation as singleton in my IHost worker which use background service that is processing a file and then save in database?
@@MilanJovanovicTech Why Singleton? If there are multiple concurrent requests to API endpoint then how this Singleton connection will handle all those?
So for Singleton this Sql connection will remain open forever till application closes?
@@etechguide7251 Well, no... Notice that the SqlConnectionFactory just creates a new connection and returns it. It's up to the consumer to dispose of it.
Can you please share link to download code for this video.
This one is available on Patreon
Why no sprocs. Imbeded sql is hard to debug in production
I don't like using stored procedures
It would be better to use DbDataSource (or DbProviderFactory at least) instead of SqlConnectionFactory. Btw, Dapper doesn't support positional parameters and DbBatch that are useful for Npgsql.
Naming things is hard 😁
Awesome content as always!!
Just wonder if is there any way to write prepared statements like in Java?
I think Dapper does something similar to that internally, with the SQL I specified in the example
Why nobody compare performance of Raw SQL in EF with Dapper?
That would be a honest comparison.
Because it's not how you would use EF normally, right?
@@MilanJovanovicTech yes, but if you just need to run a raw SQL - is required to install one more dependency?
I hope I will find some time to do different comparisons.
I like Dapper but I don't have an answer to that question.
Such a good video, Milan!
And, thinking of such usage of Dapper, I always have a question in such use cases: can we just put Dapper stuff into Repository or it's bad idea? Because I think that it would be a great idea, but I have strugglings about mixing EF Core and Dapper together in one class
Thanks a lot! :)
I've done that before - putting Dapper & EF in same repository. However, I mostly used Dapper for UPDATE/DELETE queries in such cases.
@@MilanJovanovicTech thanks for the reply, it's worthy! Have a great day
Jumping in, are there any reasons not to use queries within the repository?
One benefit of putting it in a repository is that it can be used by many handles
@@adisilagy On read side, no.
But if you read to modfy, you won't have change tracking out of the box as you would with EF.
Thanks for your video!
I didn't get why you said Dapper is 'definitely' faster. In this particular scenario, the AVG and Median produced by EF were better and Min was just 3 ms faster, so in your 'performance testing' EF is the definite winner. (Of course, I'm not taking into consideration the cold start of EF query)
I've just done the same simple comparison using K6 with my pet project, and I can say that I can't see any difference comparing the same query executing with EF and Dapper (or maybe my PC is just too noise due to big amount of background work to show correct result as dapper might be a little bit faster in theory)
So the conclusion is not very informative in my opinion, cause I think it is not about the performance of the same queries, it's about the 'linq or code first approach' and 'database first approach'.
IMHO, I believe this performance boost using Dapper is so miserable compared to the features that are provided by EF. Projects that use 'raw SQL with dapper' or 'procedures with dapper' become unsupportable very easily, so I think EF should be picked as a primary tool in the first place and dapper can be used in some corner cases when we need to write something really complex.
Also, we are all .Net developers and most of us don't know how to write effective SQL queries.
We should do a proper benchmark
And not knowing how to write SQL is not a valid excuse!
@@MilanJovanovicTech I'm a big proponent of EF vs Dapper in my teams, whereas most aren't and they favour Dapper. One of the touted reasons is that with EF, you don't need to know SQL and I think that's such a simplification. Yes, you can build quick projects using EF and see quick results, but when getting near a large-scale production database you absolutely need to know SQL to make sure you aren't killing the database while executing any EF stuffs. You need to know SQL for both approaches if you want to go further than entry-level stuff, basically.
One thing I have noticed is that intermediate and senior developers in my company do not know EF past the simple stuff and they don't see the value in learning it when they can just use something like Dapper paired with SQL knowledge. Which I think is a shame. The age-old "Dapper is faster" is definitely not as valid as it once was and I much prefer the abstractions that EF gives.
Another great video. Just out of curiosity, how would you implement the UOW when using Dapper? I'm not a fan of EF in any of it's versions and I'm not convinced that EF hadn't cached the result from the first call in your test (I'm pretty sure SQL server would have at least cached the execuption plan), so I'm not sure of the validity of the test, but I know that EF has improved over the years, I just remember it when it was a pile of horse manure. Anyway, I tend to just use raw ADO as I'm old school, but have quite often struggled with the UOW, which is much easier to achieve in EF, so any thoughts you have on this would be appreciated.
You gotta implement the Identity Map pattern on your own, too much trouble
Great video as always :) can you do video about registering and configuring class library in web app ConfigureServices method of Startup. For example we have some class library in which we have api client calling some external api's. and we need to add it to our web app and configure stuff like url, name itc ....
You mean an HttpClient?
@@MilanJovanovicTech not HttpClient per se. I am talking about pattern for registering and configuring nuget packages. For example we have class library and we wanna use options patter to enable users of our package to configure some stuff, like we do is startup class when we import nuget package. That was my idea, i think it would be really informative
One big benefit of using dapper is you can access internal sql functions that is not accessible when using EF.
I think a combination is good where easy non critical db operations can use EF while on the more performance critical db operations you can opt in for dapper.
Good video 👍⭐️
I agree, I also like to combine them!
Not sure what do you mean internal, but you can map even custom function in EF.
You can access internal functions in sql by creating a static method in the db context and decorating it with [DbFunction(Name="")].
You use it in your ef core query function when you need it
👋