Raw SQL and Dapper

Parameterised raw SQL in EF Core, Dapper for read-heavy paths, and why user input never goes into a SQL string.

Syntaxcontext.Blogs.FromSql($"SELECT * FROM Blogs WHERE Rating >= {minimum}")

Sometimes LINQ is the wrong tool: a window function, a recursive query, a hand-tuned report. EF Core lets you send SQL yourself, and nothing else about the context changes.

The safe methods

FromSql (EF Core 7 and later) starts a query from your SQL and returns entities. It takes an interpolated string, and each hole becomes a database parameter rather than text. Database.SqlQuery<T> does the same for values and for types that are not in your model, which has worked for unmapped classes since EF Core 8. Database.ExecuteSqlAsync runs a statement that returns no rows and reports the row count.

Dapper

Dapper is a micro-ORM: it runs SQL you wrote and copies each returned column into a matching property. It has no change tracker, no model and no migrations, so it is small and fast, and it suits read-heavy endpoints and reports. Keep EF Core for writes and the schema. Two libraries over one connection is a normal arrangement, not a contradiction.

The rule that never bends

Never concatenate or interpolate user input into SQL text. Doing so is SQL injection: a value such as '; DROP TABLE users; -- stops being data and becomes commands. Always pass values as parameters. The Raw methods take a plain string and separate parameters precisely because they cannot do that for you; EF Core 10 ships an analyzer that warns when a raw call contains concatenation. Table and column names cannot be parameterised at all, so when they must vary, map user input through a fixed list of allowed names.

Example

// dotnet add package Microsoft.EntityFrameworkCore.Sqlite
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

using var connection = new SqliteConnection("Data Source=:memory:");
connection.Open();

using var context = new BlogContext(
    new DbContextOptionsBuilder<BlogContext>().UseSqlite(connection).Options);
await context.Database.EnsureCreatedAsync();

context.Blogs.AddRange(
    new Blog { Name = "Ops diary", Rating = 4 },
    new Blog { Name = "SQL notes", Rating = 2 });
await context.SaveChangesAsync();

int minimum = 3;

// The {minimum} hole becomes a database parameter, not text in the statement.
List<Blog> good = await context.Blogs
    .FromSql($"""SELECT * FROM "Blogs" WHERE "Rating" >= {minimum}""")
    .AsNoTracking()
    .ToListAsync();

foreach (Blog blog in good)
{
    Console.WriteLine($"{blog.Name} ({blog.Rating})");
}

// ExecuteSqlAsync parameterises the same way and returns rows affected.
int rows = await context.Database.ExecuteSqlAsync(
    $"""UPDATE "Blogs" SET "Rating" = 5 WHERE "Rating" < {minimum}""");
Console.WriteLine($"rows updated: {rows}");

public class Blog
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public int Rating { get; set; }
}

public class BlogContext(DbContextOptions<BlogContext> options) : DbContext(options)
{
    public DbSet<Blog> Blogs => Set<Blog>();
}

// Output:
// Ops diary (4)
// rows updated: 1

When to use it

Run a recursive or window-function query that LINQ cannot express.
Serve a dashboard endpoint from hand-written SQL through Dapper while writes stay on EF Core.
Let an API caller choose a sort column without opening a SQL injection hole.

More examples

A read-heavy endpoint on Dapper

Reach for this when a report joins four tables and you want to write the SQL yourself. Dapper adds no tracking and no model, so it is a thin layer over the connection; keep writes in EF Core so migrations still describe the schema.

// dotnet add package Dapper
// dotnet add package Npgsql
using Dapper;
using Npgsql;

public record OrderRow(int Id, DateOnly PlacedAt, string CustomerName, int TotalCents);

public class OrderReports(string connectionString)
{
    private readonly string _connectionString = connectionString;

    public async Task<List<OrderRow>> RecentAsync(DateOnly since, int limit)
    {
        await using var connection = new NpgsqlConnection(_connectionString);

        // Column aliases match the record's parameter names, which is how
        // Dapper fills it in. @since and @limit come from the anonymous object,
        // so they are parameters and never part of the SQL text.
        const string sql = """
            SELECT o.id            AS Id,
                   o.placed_at     AS PlacedAt,
                   c.name          AS CustomerName,
                   o.total_cents   AS TotalCents
            FROM orders o
            JOIN customers c ON c.id = o.customer_id
            WHERE o.placed_at >= @since
            ORDER BY o.placed_at DESC
            LIMIT @limit
            """;

        IEnumerable<OrderRow> rows = await connection.QueryAsync<OrderRow>(
            sql, new { since, limit });

        return rows.ToList();
    }
}

Scalars and shapes that are not entities

Use SqlQuery for aggregates and report rows that have no table behind them. It saves inventing an entity, and the result is still an IQueryable, so paging operators can be added on top.

using Microsoft.EntityFrameworkCore;

// A single value. SqlQuery<T> returns an IQueryable, so LINQ still composes.
List<int> ids = await context.Database
    .SqlQuery<int>($"""SELECT "Id" FROM "Blogs" WHERE "Rating" >= {3}""")
    .ToListAsync();

// A class that is not in the model at all. Every column in the result set
// needs a matching property; the type needs no key and no DbSet.
public class RatingCount
{
    public int Rating { get; set; }
    public int Blogs { get; set; }
}

List<RatingCount> histogram = await context.Database
    .SqlQuery<RatingCount>($"""
        SELECT "Rating" AS "Rating", COUNT(*) AS "Blogs"
        FROM "Blogs"
        GROUP BY "Rating"
        ORDER BY "Rating"
        """)
    .ToListAsync();

// Returning unmapped types this way arrived in EF Core 8. Before that the
// result type had to be a keyless entity declared in the model.

When the column name itself must vary

This is the one case that tempts people into string building. An allow-list keeps it safe; the LINQ version avoids the question altogether and is what to write unless the SQL has to be hand-tuned. Note that an allow-list also stops a caller sorting by an unindexed column.

using Microsoft.EntityFrameworkCore;

// Databases cannot parameterise identifiers, so a sort column chosen by the
// caller cannot be passed as @column. Map the input through a fixed list
// instead; anything unrecognised falls back to a default.
static string SortColumn(string? requested) => requested switch
{
    "name"   => "\"Name\"",
    "rating" => "\"Rating\"",
    _        => "\"Id\""
};

string column = SortColumn(userInput);
int minimum = 3;

// The identifier comes from our own list, never from the request. The value
// still travels as a parameter.
List<Blog> blogs = await context.Blogs
    .FromSqlRaw($"SELECT * FROM \"Blogs\" WHERE \"Rating\" >= {{0}} ORDER BY {column}", minimum)
    .ToListAsync();

// Easier and safer: let LINQ sort, and keep raw SQL out of it entirely.
IQueryable<Blog> query = context.Blogs.Where(b => b.Rating >= minimum);
query = userInput switch
{
    "name"   => query.OrderBy(b => b.Name),
    "rating" => query.OrderByDescending(b => b.Rating),
    _        => query.OrderBy(b => b.Id)
};
List<Blog> sorted = await query.ToListAsync();

Discussion

Be the first to comment on this lesson.

The safe methods

Dapper

The rule that never bends

Example

When to use it

More examples

A read-heavy endpoint on Dapper

Scalars and shapes that are not entities

When the column name itself must vary

Related

Discussion