## The Question Nobody Gives a Straight Answer To
"Should I use Stored Procedures, raw SQL, or an ORM?"
The honest answer is: it depends. But "it depends" is only useful if you know what it depends on. Here's the actual framework I use.
## Option 1: Stored Procedures
Stored procedures are precompiled SQL that live in the database.
**Use stored procedures when:**
- Working on government or enterprise projects where DBAs review and own the database
- You need execution plan caching for performance-critical queries
- Security policies require all data access to go through procedures
- Complex business logic involves multiple tables and conditional flows
**Real example from my work:**
```sql
CREATE PROCEDURE sp_GetExperiencesByPortfolio
@PortfolioId INT
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Experiences
WHERE PortfolioId = @PortfolioId AND IsVisible = 1
ORDER BY IsCurrentlyWorking DESC, SortOrder ASC;
END
```
Called from C# via Dapper:
```csharp
return await db.QueryAsync<Experience>(
"sp_GetExperiencesByPortfolio",
new { PortfolioId = portfolioId },
commandType: CommandType.StoredProcedure
);
```
**Disadvantages:**
- Logic split between application and database
- Harder to version control
- Database migrations get complicated
## Option 2: Raw SQL in Application Code
Writing SQL strings directly in your C# code, executed via Dapper or ADO.NET.
**Use raw SQL when:**
- Queries are complex and ORM-generated SQL would be inefficient
- You want full control but don't want SP overhead
- Queries are specific to one use case and don't need reuse
```csharp
var sql = @"SELECT p.*, c.Name as CategoryName
FROM Projects p
INNER JOIN Categories c ON p.CategoryId = c.Id
WHERE p.PortfolioId = @PortfolioId
AND p.IsVisible = 1
ORDER BY p.IsFeatured DESC, p.SortOrder ASC";
return await db.QueryAsync<ProjectDto>(sql, new { PortfolioId = id });
```
**Disadvantages:**
- SQL injection risk if not parameterized (always use parameters)
- SQL strings in C# files feel messy
- No compile-time checking
## Option 3: ORM (Entity Framework)
Let the framework generate SQL from LINQ queries.
**Use ORM when:**
- Rapid development is the priority
- Domain model is complex with many relationships
- Team is more comfortable with C# than SQL
- You want built-in migrations for schema management
```csharp
var projects = await _context.Projects
.Include(p => p.Category)
.Where(p => p.PortfolioId == id && p.IsVisible)
.OrderByDescending(p => p.IsFeatured)
.ThenBy(p => p.SortOrder)
.ToListAsync();
```
**Disadvantages:**
- Generated SQL can be inefficient
- Performance overhead for read-heavy applications
- N+1 query problem if you're not careful
## My Personal Decision Framework