EF Core Raw SQL — Complete Guide

In this tutorial, you'll learn about EF Core Raw SQL. We cover key concepts, practical examples, and best practices.

You need a complex SQL query with window functions, CTEs, or database-specific features that EF Core's LINQ provider cannot generate. Or you need to execute a stored procedure. Raw SQL lets you execute arbitrary SQL while still using EF Core's infrastructure.

Wrong

// Trying to force LINQ for something it doesn't support well:
var query = db.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new { Customer = g.Key, Total = g.Sum(o => o.Amount) });
// Works, but some complex queries are impossible in LINQ

Output: Works for simple cases. Window functions, CTEs, and recursive queries are not expressible in LINQ.

Right

var sql = @"SELECT CustomerId, SUM(Amount) as Total,
                   RANK() OVER (ORDER BY SUM(Amount) DESC) as Rank
            FROM Orders
            GROUP BY CustomerId";

var results = await db.Database.SqlQueryRaw<CustomerSummary>(sql).ToListAsync();

For parameterized queries (always use parameters!):

var sql = @"SELECT * FROM Orders WHERE CustomerId = {0} AND Amount > {1}";
var orders = await db.Orders
    .FromSqlRaw(sql, customerId, minAmount)
    .ToListAsync();

Or with named parameters (SQL Server):

var sql = @"SELECT * FROM Orders WHERE CustomerId = @id";
var param = new SqlParameter("@id", customerId);
var orders = await db.Orders.FromSqlRaw(sql, param).ToListAsync();

Prevention

• Use raw SQL for operations LINQ cannot express (window functions, CTEs, full-text search, stored procedures).
• Always use parameters to prevent SQL injection — never use string concatenation.
• Use FromSqlRaw for raw SQL that returns entity types.
• Use SqlQueryRaw for raw SQL that returns custom types (not entities).
• Use ExecuteSqlRaw for non-query commands (INSERT, UPDATE, DELETE).
• Use FromSqlInterpolated / ExecuteSqlInterpolated for safer parameterized syntax.
• Verify raw SQL queries are supported by your database provider.

Common Mistakes with core raw sql

1. Using head and tail instead of pattern matching, causing runtime errors on empty lists
2. Forgetting that lazy evaluation defers computation until the value is forced, causing space leaks with unevaluated thunks
3. Using return to exit a function early instead of wrapping a pure value in the monad

These mistakes appear frequently in real-world EF code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ

Raw SQL is used in DodaTech's reporting engine for complex analytical queries. For more EF Core, visit DodaTech.