EF Core FromSql — Complete Guide

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

You need to execute a custom SQL query that returns entities — perhaps using a view, a complex join, or calling a table-valued function. FromSql lets you run raw SQL and map the results back to entity types with full EF Core tracking.

The Problem

// LINQ cannot express this efficiently:
var results = db.OrderDetails
    .FromSqlRaw(@"SELECT d.*, o.CustomerName
                  FROM OrderDetails d
                  INNER JOIN Orders o ON d.OrderId = o.Id
                  WHERE d.Quantity > 10")
    .ToList();

Output: The SQL query is executed as-is. Results are mapped to OrderDetail entities (and possibly additional types).

FromSql Variants

// Raw (with positional parameters):
var orders = db.Orders
    .FromSqlRaw("SELECT * FROM Orders WHERE Amount > {0}", minAmount)
    .ToList();

// Interpolated (safer — auto-parameterizes):
var orders = db.Orders
    .FromSqlInterpolated($"SELECT * FROM Orders WHERE Amount > {minAmount}")
    .ToList();

// With LINQ composition:
var recent = db.Orders
    .FromSqlRaw("SELECT * FROM Orders WHERE Amount > 0")
    .Where(o => o.Date > cutoff)
    .OrderBy(o => o.Date)
    .ToList();

Output: Entities are tracked by default (no AsNoTracking). LINQ composition adds to the SQL query.

Prevention

• Use FromSqlRaw when you need full control over the SQL text.
• Use FromSqlInterpolated to avoid SQL injection with complex parameter values.
• Always use parameters — never use string concatenation for user input.
• Ensure the SQL result columns match the entity properties (same names).
• Use AsNoTracking() after FromSql for read-only queries.
• Use LINQ composition for additional filtering, sorting, and pagination.
• Use SqlQueryRaw (without entity) for non-entity result types.

Common Mistakes with core from sql

1. Misunderstanding that String is [Char] with poor performance for large text operations
2. Using foldl instead of foldl' causing stack overflow on large lists
3. Forgetting deriving (Show, Eq) on custom data types needed for debugging

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

FromSql powers DodaTech's custom reporting views. For more EF Core, visit DodaTech.