Write Faster SQL Queries With Dapper In .NET | Clean Architecture

Dapper is a very popular micro-ORM library that you can use to write queries for any relational database. Dapper works as a great alternative to EF Core, but it doesn't have to be. Dapper can also be complementary to EF Core, and that's how I like to use it. Dapper gives you more control over what you want to execute since you are writing the SQL that is executed in the database.

Join my weekly .NET newsletter:

Read my Blog here:

Subscribe for more:

Chapters
0:00 Examining baseline EF performance
1:01 Creating a SqlConnection
4:04 Querying the database with Dapper
8:37 Implementing the SqlConnectionFactory
9:41 Examining Dapper performance
10:25 Breaking the Clean Architecture?

[mohamed-hassan-]

I've used multiple times, thanks to you.. keep the great work lad💪🏻

[ashwanibhardwaj]

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

[Cornet]

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?

[cbo]

Task<Result<T>> which package you use for the "Result" keyword, or is it a model in Domain or another layer

[Real-Hindu-Us]

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.

[tony-ma]

You didn't show the dependency injection for the SQL connection factory, do you inject as singleton, scoped or transient?

[jcyepes]

Very nice video.
What theme do you use for VS 2022?

[onedev]

Hi Milan, nice walk through. I am getting an issue of "The ConnectionString property has not been initialized." Am i missing something?

[amrelsher]

What is better milan to return in handler result<> or throw exception if something wrong ?

[emwagner]

If you are manually specifying/writing SQL statements to query the database using Dapper, do you have to worry about SQL Injection issues?

[mahanog]

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

[KingOfBlades]

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.

[FGomesFabio]

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

[crazyfxy]

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).

[MarcusKaseder]

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 🙂

[krccmsitp]

Out of curiousty, did you explain your DomainErrors calls in any of your videos? I'd like to rewatch it.

[NikolaGolijanin-ms]

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?

[bahtiyarozdere]

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.

[ransandu]

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?

[vinr]

Why didn't you implement another version of Repository instead of putting all SQL queries and connections into the handler?