SqlKata vs. Entity Framework
This is the number one question that I always get asked concerning SqlKata.
Why should we use it? How it differs from Entity Framework and What kind of competition is it?
You can compare SqlKata to Entity Framework on the conceptual level only, like what tool should I use for this task? but when you go a bit deeper the comparison will easily become invalid 🍏 != 🥧.
Read below to know the top 4 differences.
1. SqlKata is an “SQL Query Builder” while Entity Framework is an “Object-Relational Mapping”
SqlKata is considered a lower level tool that let you write SQL string in an Object Oriented manner, the idea first came, when I wanted to write complex SQL strings taking into consideration multiple user-provided conditions.
I will not explain why you should not do this, but if you tried this before, you know how’s messy it will be, manipulating strings is the last thing you should consider as an OOP programmer.
SqlKata was created to help writing free SQL statements without the mess of string manipulation.
On the other hand, Entity Framework (or ORMs in general) solves the problem by inventing another approach, here is the deal: keep away from SQL and deal with Plain C# objects (aka models), this approach is very cool, but it has its own downside also.
2. SqlKata has no clue about your database, Entity Framework does
In Entity Framework, you write C# expressions against your strongly typed models, then EF translates these expressions to SQL string based on it’s (column/properties) mapping, so it’s not possible to produce a SQL string with a nonexisting column written by mistake.
The bad part here about EF is that you can’t access the full power of your database, and you are always searching “How can I do X in EF”, while the Entity Framework Team did a great job on improving this side, this will never beat the flexibility of a lower level tool like SqlKata.
In SqlKata there is no runtime check since it has no clue about your database schema, for example, you can query by mistake a non-existing table or column and this is by design.
This flexibility unlocks the strong barriers forced by the abstracted layers, think of complex JOIN, UNION, HAVING statements, native SQL functions, etc…
This may be a problem for novice developers who heavily rely on intellisense while giving the superpower for experts if following a good naming convention.
3. SqlKata ❤️ SQL, Entity Framework ❤️ C#
If you have a strong SQL experience, you will easily understand SqlKata, since it’s API is very similar to the SQL standards, time to unleash your superpower ⚡️.
Moreover, you can always pass your RAW SQL statements when you need that (pay attention to SQL injection).
In Entity Framework, you are a bit far from SQL and closer to C#, you can feel the taste of elegance provided by Entity Framework 🎩 since you write in the language you love at the cost of learning how EF translates your C# code to SQL to handle outperforming queries.
4. SqlKata focuses on building SQL queries, Entity Framework focuses on your Database Eco-System
Entity Framework provides a set of solutions related to accessing and managing your database, to name a few, Migration Generation tool (code first approach), the Schema Management API in addition to the built-in database executor.
On the other hand, SqlKata does a great job in accessing the database, but its main focus is how easily you can build an SQL query and leave other details, for instance, you can use it just to build SQL strings and execute them by yourself using your preferred method.
For the execution part, it uses the battle-tested database executor Dapper, Dapper is handling the top heaviest sites on the world like StackOverflow.
5. Extending SqlKata functionality is easier
Dealing with SQL Query Builder API is easier than dealing with C# expressions tree, this is why you can easily extend the query builder functionalities, let’s say you want to add a whereTokyoTime method, you can do this by adding a simple extension method
From now on you have a WhereTokyoTime at your premises
Also extending SQL compilers can be achieved with few lines of code, let’s say you want to write a compiler for http://prestodb.github.io, you can start simply by extending the Compiler class and then adapting as you go
You can take a look at the existing compiler's code to get an idea
Final words
I tried to clear up the differences between these two tools, so you can easily choose the right one for the right job, while I am the creator of SqlKata, I tried to be unbiased as much as possible.
Personally, I like both Entity Framework and SqlKata, but if I were in a situation where I have to choose one, I choose SqlKata.
What about you ?, Let me know your thoughts in the comments section.
Thank you for reading,
I am Ahmad Moussawi, follow me on https://twitter.com/ahmadmuzavi for the latest updates about SqlKata.
