Almost every application stores data somewhere. It is very often stored in database. If you want to store data, you need to find a way to access database. Today I’ll describe possible methods to access database with advantages and problems.
Possible options
You should know that there is a lot of ways to access database in .Net. You can use ORM like EntityFramework or NHibernate, micro-ORM like Dapper and old ADO.Net, in my opinion in this game there are only 2 players, ORM and micro-ORM.
Main reason for it is that every ORM even micro-ORM can map your database columns into C# classes. This is the biggest strength of ORMs. When we load data it will be stored in memory in the form of model specified earlier.
Main difference between ORM and micro-ORM is manner to access data. ORM uses LinQ to object, so we can write C# code filter data using LinQ and ORM will translate C# code into SQL. Micro-ORM doesn’t have it, that’s why it is called lightweight ORM. You’ll need to write SQL code and it will only map your results to classes.
ORM – Entity Framework
You need to know that I like EF very much. I’m waiting for day when we start using it instead of raw SQL. The most important advantage is that you can write C# code. It’s easier for .Net developers than SQL. We can debug our code in IDE and we have full support for formatting, therefor it’s easier to refactor and change our code. Finally, we can test our code without using physical database, so tests are faster and easier to maintain.
Main reason against EF is performance. In most cases it’s just talking. Like every tool, someone can use it wrong way, so it may cause more problems that help, but if we know restrictions of our tool we can use it to help ourselves.
It’s true that EF can generate very inefficient SQL, but we have means to change our code and work on performance. If everything else fails, we can even write raw SQL instead of generating it, so EF doesn’t bind us to anything.
If you discuses with someone whether you should use EF or not, and he tells you that EF has bad performance, that means he didn’t use EF before. Performance isn’t the biggest issue, because as I said earlier you can write raw SQL if everything fails and have “good” performance with your SQLs. Problem is when you have complex database,or logic in database.
You can’t use dynamic tables with EF or create tables on the fly. Personally I didn’t have need to do that, but I worked on project where almost everything was done with dynamic tables, sometimes it was used to make link tables between two others or gather data in one place, which could’ve been achieved with normal link tables.
Far worse thing is when you need to update a lot of rows at the same time. This is very common task, and EF can’t deal with it. You would need to load data into memory, just to put it back to database, changed. This is place where you need to use raw SQL, because using EF here will generate insufficient SQL.
You can ask why do I like EF seeing all of this. Answer is simple, it is the future. Every tool is easier than tool before, programming is easier from one year to another. In future we’ll use spoken language to develop application. We should prepare for this right now, or we’ll not be needed.
Micro-ORM – Dapper
Dapper is opposite to EF. You’ll write everything using SQL from start. So every performance issue disappears, of course you can write bad code and performance still will be bad. The only problem here is that you need to write SQL and have every problem connected to it.
Your SQL code will be passed to methods as string, so there is no formatting or syntax checks. Refactor and code changes are hell, because you’ll have to use only your eyes, IDE can’t help us with anything. It’s like time travel to the past. Debugging and testing in C# are impossible.
Don’t get me wrong. Dapper is a great tool. Sooner or later you’ll need it, because EF has its limitations. But whenever I can, I would use EF.
Result
Both methods have advantages and disadvantages. The best is, you can combine both of them. My team was using EF and Dapper simultaneously. When we could, we used EF. If EF had problem or performance was critical we used connection created for EF and passed it to Dapper, so we could’ve use the same transaction.
You should remember that having too much raw SQL may cost you in future, because SQL is hard to maintain, it’s even worse for developers who aren’t used to it, like me.
I waste a lot of time to understand SQL code, when glance at LinQ tells me what’s happening. It doesn’t mean you can ignore SQL, because you don’t know it. I just can’t see a point in learning it actively. You’ll learn SQL using it in your projects, in most cases SQL basics are sufficient.
Unfortunately SQL won’t die and we will have to work with it. Even more as my friend used to say “you have to know SQL to use EF, because you have to know if code generated by EF is correct”. Still you can help yourself and create project which will be easier to maintain and test.