[C#] Executing Raw SQL in EF Core (ExecuteSql / FromSql)

2026年1月15日

Overview

This implementation shows how to execute SQL commands directly instead of using LINQ. This is useful for performance tuning or writing complex queries that are difficult to express in LINQ. You use ExecuteSqlAsync for updating data and FromSql for retrieving data. By using C# string interpolation ($), Entity Framework Core automatically handles SQL injection protection.

Specifications (Input/Output)

Input: User ID, message text, and timestamp.
Output: Registers data using a SQL INSERT statement and displays results retrieved via a SELECT statement in the console.
Prerequisites: .NET 6.0 or higher. Executing Raw SQL requires a relational database (this example uses SQLite).

Basic Usage

In EF Core 7.0 and later, ExecuteSqlAsync and FromSql are the recommended methods. Variables included in interpolated strings ($) are automatically parameterized.

int userId = 1;
string name = "gushwell";

// Update operations (INSERT, UPDATE, DELETE)
// This is safe because values are processed as parameters (@p0, @p1...)
await context.Database.ExecuteSqlAsync($"UPDATE Users SET Name = {name} WHERE Id = {userId}");

// Retrieval operations (SELECT)
var users = await context.Users
    .FromSql($"SELECT * FROM Users WHERE Id = {userId}")
    .ToListAsync();

Full Code Example

This code uses SQLite. You need the Microsoft.EntityFrameworkCore.Sqlite NuGet package.

dotnet add package Microsoft.EntityFrameworkCore.Sqlite

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

public class Program
{
    public static async Task Main()
    {
        // Setup options to use a SQLite database
        var options = new DbContextOptionsBuilder<BlogDbContext>()
            .UseSqlite("Data Source=blog.db")
            .Options;

        // --- 1. Database Initialization ---
        using (var context = new BlogDbContext(options))
        {
            // Create database and tables
            await context.Database.EnsureDeletedAsync();
            await context.Database.EnsureCreatedAsync();
        }

        // --- 2. Operations using Raw SQL ---
        using (var context = new BlogDbContext(options))
        {
            var worker = new SqlWorker(context);
            await worker.RunAsync();
        }
    }
}

// Business logic class
public class SqlWorker
{
    private readonly BlogDbContext _context;

    public SqlWorker(BlogDbContext context)
    {
        _context = context;
    }

    public async Task RunAsync()
    {
        var message = "I will attend tomorrow's study session.";
        var sentTime = new DateTime(2025, 11, 22, 12, 5, 7);
        var userId = 101;

        Console.WriteLine("--- 1. INSERT (ExecuteSqlAsync) ---");

        // Execute an INSERT statement using ExecuteSqlAsync
        // Using string interpolation ($"...") automatically converts variables into safe SQL parameters
        int affectedRows = await _context.Database.ExecuteSqlAsync($@"
            INSERT INTO Posts (UserId, Message, SentTime) 
            VALUES ({userId}, {message}, {sentTime})
        ");

        Console.WriteLine($"{affectedRows} record(s) inserted.");

        Console.WriteLine("--- 2. SELECT (FromSql) ---");

        // Execute a SELECT statement using FromSql
        // Results are retrieved as entities, so Change Tracking is enabled
        var posts = await _context.Posts
            .FromSql($"SELECT * FROM Posts WHERE UserId = {userId}")
            .ToListAsync();

        foreach (var post in posts)
        {
            Console.WriteLine($"[ID:{post.PostId}] User:{post.UserId} / Time:{post.SentTime}");
            Console.WriteLine($"  Message: {post.Message}");
        }
    }
}

// Entity definition
public class Post
{
    [Key]
    public int PostId { get; set; }
    public int UserId { get; set; }
    public string Message { get; set; } = string.Empty;
    public DateTime SentTime { get; set; }
}

// DbContext definition
public class BlogDbContext : DbContext
{
    public BlogDbContext(DbContextOptions<BlogDbContext> options)
        : base(options) { }

    public DbSet<Post> Posts => Set<Post>();
}

Customization Points

Stored Procedures: You can map the results of a stored procedure to entities by using FromSql($"EXEC GetPosts {userId}").
Retrieving Scalar Values: If you only need a specific value like a “count” or a “list of IDs,” use context.Database.SqlQuery<int>($"SELECT Count(*) ...") (available in EF Core 8+).
No-Tracking Queries: If you only intend to display data, combine .FromSql(...).AsNoTracking() to improve performance.

Important Notes

SQL Injection: Using $ (FormattableString) is safe. However, never use string concatenation (e.g., "SELECT ... WHERE Name = '" + name + "'") with FromSqlRaw. Always use parameterized queries via interpolated strings.
Column Name Mismatch: When using SELECT *, the database column names must match the entity property names. If they differ, use aliases like SELECT user_id AS UserId ... or configure the mapping in the entity settings.
InMemory Provider Limitations: Microsoft.EntityFrameworkCore.InMemory does not support executing raw SQL. For testing, you must use a relational provider like SQLite (In-Memory mode).

Advanced Application

Getting a List of Primitive Types (Scalars) – EF Core 8.0+

Use this when you want to extract specific columns instead of entire entities.

// Retrieve only a list of IDs
List<int> ids = await context.Database
    .SqlQuery<int>($"SELECT PostId FROM Posts WHERE UserId = {userId}")
    .ToListAsync();