Writing Raw SQL When Entity Framework Isn’t Enough

Writing Raw SQL When Entity Framework Isn’t Enough

Entity Framework has become the preferred data access technology for many .NET developers. It abstracts much of the complexity involved in communicating with databases and allows developers to focus on business logic rather than SQL syntax. While this approach works exceptionally well for most applications, there are occasions where Entity Framework's abstraction becomes a limitation rather than an advantage.

Understanding when and how to use raw SQL alongside Entity Framework can help developers solve performance issues, leverage advanced database features, and maintain greater control over their applications.

Why Entity Framework May Not Be Enough

Entity Framework translates LINQ queries into SQL statements that are executed against the database. In most scenarios, the generated SQL is efficient and reliable. However, complex queries can sometimes result in SQL that is less than optimal.

Applications dealing with large datasets, complex joins, reporting requirements, or database-specific functionality may encounter situations where the generated SQL becomes difficult to optimise. Developers may also need access to stored procedures, common table expressions, window functions, or vendor-specific features that are not easily expressed through LINQ.

In these cases, writing raw SQL can provide a more direct route to the required functionality.

Executing Raw SQL Queries

Entity Framework Core provides several methods for executing SQL directly. One of the most common approaches is using FromSqlRaw to populate entity objects from a custom query.

For example, suppose you need to retrieve all active customers:

var customers = context.Customers
    .FromSqlRaw(
        "SELECT * FROM Customers WHERE IsActive = 1")
    .ToList();

The results are automatically mapped to the Customer entity, allowing the rest of the application to work with strongly typed objects.

Using Parameters Safely

One of the biggest concerns when writing raw SQL is SQL injection. User input should never be concatenated directly into a SQL statement.

Entity Framework provides parameterised SQL execution to help prevent security vulnerabilities.

var customers = context.Customers
    .FromSqlInterpolated(
        $"SELECT * FROM Customers WHERE Country = {country}")
    .ToList();

The framework automatically converts the supplied value into a database parameter, helping to protect the application from malicious input.

Calling Stored Procedures

Many organisations continue to rely on stored procedures for reporting, auditing, and business logic. Entity Framework can execute these procedures directly.

var orders = context.Orders
    .FromSqlRaw(
        "EXEC GetRecentOrders")
    .ToList();

This approach allows existing database assets to be reused without rewriting functionality in application code.

Executing Commands That Do Not Return Data

Sometimes you need to execute UPDATE, INSERT, or DELETE statements directly.

Entity Framework provides the ExecuteSqlRaw method for this purpose.

var rowsAffected = context.Database.ExecuteSqlRaw(
    "UPDATE Products SET IsActive = 0 WHERE Discontinued = 1");

The method returns the number of rows affected, making it useful for maintenance operations and bulk updates.

Improving Performance with Raw SQL

Performance is often the primary reason developers turn to raw SQL. LINQ provides excellent productivity, but highly complex queries can sometimes generate inefficient execution plans.

Writing SQL manually allows developers to fine-tune joins, indexes, filtering logic, and aggregations. Database administrators can also review and optimise the exact query being executed rather than analysing generated SQL.

That said, raw SQL should be introduced only when there is a measurable performance benefit. Premature optimisation can increase maintenance costs without delivering meaningful improvements.

Combining Entity Framework and Raw SQL

Using raw SQL does not mean abandoning Entity Framework entirely. Many successful applications use a hybrid approach where standard CRUD operations continue to use LINQ while specialised queries utilise hand-written SQL.

This strategy provides the productivity benefits of Entity Framework alongside the flexibility and control of direct SQL execution. Developers can continue to leverage change tracking, migrations, and strongly typed entities while addressing specific performance or functionality requirements.

When to Choose Raw SQL

Raw SQL should be considered when a query is difficult to express in LINQ, when advanced database features are required, or when performance testing demonstrates that the generated SQL is not meeting expectations.

It is particularly useful for reporting systems, data migrations, bulk operations, and applications that rely heavily on database-specific functionality. However, developers should balance the benefits against the increased responsibility of maintaining SQL statements manually.

Conclusion

Entity Framework remains an excellent choice for the majority of database interactions in modern .NET applications. Nevertheless, there are situations where direct control over SQL provides significant advantages. By understanding how to execute raw SQL safely and effectively, developers can take advantage of the best features of both approaches, delivering applications that are both maintainable and performant.