Using SQL with C# or ASP.NET, without an ORM
I have a question about using SQL with C# or ASP.NET, without an ORM
What are the most common ways this is done? What advantages would manual queries have over ORM generated ones
6 Replies
Without an ORM, or without EF Core specifically?
Because Dapper is an ORM, but relies on raw SQL queries
The advantage is that, uh, you get to write raw queries. So if you like using SQL, you can continue to do that
The disadvantage is that you have to write SQL. So if you don't like that, you're screwed
And in case of an actual no-ORM approach (using ADO.NET for example) the disadvantage is that you discard any and all type safety and have to write 76lines of code for something that takes a single line in Dapper
The advantages are none
Dapper however being a micro or mini ORM or whatever its called
It does the minimal job of mapping sql results to C# objects, thats it
Which may be the only thing you would want, given the following cons:
ORMs (Writing no sql)
Pros:
- No or little to worry about sql, team members might not even need to know the sql flavour at all
- Database Schema maintenance can be done purely with C#
- Easily synchronized database schemas on various platforms via in-code database migrations (Local, Staging, UAT, Prod, etc)
- No Database Connection Pooling necessary
- It can easily do complex mapping of sql columns to properties
Cons:
- Your Database querying is pretty much limited to only what the ORM framework offers to you
- As an ORM envelopes so much more than just making bare-minimum database connections, it is more vulnerable for bugs to be introduced, even though unlikely, but you rely on updates, support and maintenance of the framework owners (EFCore for example is being maintained by only 2 people at the moment iirc)
- If your app grows bigger, your performance is limited to what EFCore generates, and it may or may not generate something disadvantageous, and you are not able to adjust it with table or query hints for example, or just writing the query more efficiently
You can always get started with raw ADO.NET, https://learn.microsoft.com/en-us/sql/connect/ado-net/microsoft-ado-net-sql-server?view=sql-server-ver16 as long as your project is small and simple. Once you do have a need to migrate to ORM then move.
Microsoft ADO.NET - ADO.NET Provider for SQL Server
Microsoft ADO.NET, for SQL Server and Azure SQL, is the core data access technology for .NET languages. Use the Microsoft.Data.SqlClient library to access SQL Server.
I was moreso confused about companies that say they explicitly use SQL to optimize their systems. I am getting conflicting answers from ORM users and SQL users, ORM users say there is no difference whereas SQL users say there is
I mean, as I said
Using raw sql and using an orm doesnt make a difference; ORM leans into ease of use, while raw sql lets you write the queries in the most optimal way possible
ORM frameworks are there to free you from writing manual queries, and that works very well in many cases. The current limitation of such frameworks is that on edge cases the generated queries are not the optimal but you can intervene. "Any commands automatically generated by the Entity Framework may be more complex than similar commands written explicitly by a database developer. If you need explicit control over the commands executed against your data source, consider defining a mapping to a table-valued function or stored procedure." https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/performance-considerations