ORM vs TSQL (Benefits / Drawbacks)
I think this is a subjective matter, as there’s strong arguments for both. In my experience, I prefer using ORMs for simple applications in a small team. For enterprise apps with many developers, nothing beats the simplicity and performance of pure TSQL (or SQL for that matter). Yes, there could be a lot of boiler plate code, but that part can be automated.
And of course, doing both is still an option.
ORM
Benefits
- Potentially less coding for accessing database data and basic CRUD operations.
- Create a class and it writes out your database schema
- Caching facilities are available for often-accessed data
- Entity Framework is available to script out Database tabes to c# objects.
- ORM syntax may generate database-independent code
Drawbacks
- Does not use SQL, so new syntax has to be used for CRUD/maintenance operations.
- Could be harder to debug since the SQL is abstracted. Have to run SQL profiler to view what gets actually generated.
- May not be able to do everything that TSQL can do.
- Have to know SQL anyways for things that ORM cannot do.
- Because of its implicit caching nature, when dealing with huge amounts of data, caching may not be ideal (too much unnecessary caching)
TSQL
Benefits
- Know exactly what you’re getting, since there’s no middle layer writing out your SQL
- Potentially faster SQL and no need to go through the translation engine.
- Every developer knows SQL – it’s most widespread
- SQL is cross-platform (unless using specific Transact SQL commands)
- No barriers to optimize TSQL
- Instantly/efficiently troubleshoot with SQL Sever’s native tools (Management Studio/Query Analyzer)
- SQL Server objects could be used cross-platform. A TSQL UDF, View, Stored Procedure could be easily used by any application, regardless of the platform: e.g. a PHP/Java/Ruby/CF app can use the same SQL Server Stored Procedure that a C# app is using.
- Compiled stored procedures (written in SQL) are faster than ORM syntax.
Drawbacks
- May not implicitly cache data like ORM layer, but can be cached with traditional C# code.
- More TSQL/C# code may have to be potentially written using traditional TSQL and C#.
- More advanced TSQL code may be heavily dependent on SQL Server, and may make database migration more challenging.