LINQ가 충분하지 않은 경우: Entity Framework Core의 Raw SQL

When LINQ Isn’t Enough: Raw SQL in Entity Framework Core

Sometimes LINQ can’t express what you need. A complex stored procedure. A database-specific function. A performance-critical query the ORM mangles. That’s when you reach for raw SQL — but EF Core still wants to help.

Let’s explore the escape hatches.

FromSqlRaw: Queries That Return Entities

When you need raw SQL but still want entity tracking and LINQ composition:

var products = dbContext.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > 100")
    .ToList();

Enter fullscreen mode Exit fullscreen mode

The results are tracked entities. You can modify and save them.

Even better — you can chain LINQ operators:

var products = dbContext.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > 100")
    .OrderBy(p => p.Name)   // Added by EF
    .Take(10)                // Added by EF
    .ToList();

Enter fullscreen mode Exit fullscreen mode

EF wraps your SQL as a subquery and adds the LINQ parts. The generated SQL looks like:

SELECT * FROM (
    SELECT * FROM Products WHERE Price > 100
) AS p
ORDER BY p.Name
LIMIT 10

Enter fullscreen mode Exit fullscreen mode

FromSqlInterpolated: Safe Parameterization

Never concatenate user input into SQL. Use interpolation:

var category = "Electronics";
var minPrice = 50m;

var products = dbContext.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Category = {category} AND Price > {minPrice}")
    .ToList();

Enter fullscreen mode Exit fullscreen mode

EF extracts the interpolated values as parameters. The actual SQL becomes:

SELECT * FROM Products WHERE Category = @p0 AND Price > @p1

Enter fullscreen mode Exit fullscreen mode

SQL injection? Prevented. The interpolation syntax looks like string building, but EF converts it to parameters.

Fun fact: FromSqlInterpolated uses C#’s FormattableString feature under the hood. The compiler treats $"..." differently when the method expects FormattableString instead of string. This trick lets EF intercept the interpolation values before they become a string, extracting them as parameters.

ExecuteSqlRaw: Commands That Don’t Return Entities

For UPDATE, DELETE, INSERT, or anything that doesn’t return rows you want as entities:

// Simple execution
int rowsAffected = dbContext.Database.ExecuteSqlRaw(
    "UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = 5");

// With parameters
int rowsAffected = dbContext.Database.ExecuteSqlInterpolated(
    $"UPDATE Products SET Price = Price * {multiplier} WHERE CategoryId = {categoryId}");

Enter fullscreen mode Exit fullscreen mode

Returns the number of affected rows. No entity tracking involved.

Stored Procedures

Procedures That Return Entities

var products = dbContext.Products
    .FromSqlRaw("EXEC GetProductsByCategory @p0", categoryId)
    .ToList();

// Or with named parameters
var products = dbContext.Products
    .FromSqlRaw("EXEC GetProductsByCategory @CategoryId = {0}", categoryId)
    .ToList();

Enter fullscreen mode Exit fullscreen mode

The procedure must return columns matching the entity shape.

Procedures That Return Scalars or Non-Entities

// Using ADO.NET through EF
var connection = dbContext.Database.GetDbConnection();
await connection.OpenAsync();

using var command = connection.CreateCommand();
command.CommandText = "EXEC GetProductCount @CategoryId";
command.Parameters.Add(new SqlParameter("@CategoryId", categoryId));

var count = (int)await command.ExecuteScalarAsync();

Enter fullscreen mode Exit fullscreen mode

For complex scenarios, you drop down to raw ADO.NET. EF manages the connection; you manage the command.

Keyless Entity Types for Custom Queries

When your SQL returns a shape that isn’t a table:

// Define a keyless type
[Keyless]
public class ProductSummary
{
    public string Category { get; set; }
    public int Count { get; set; }
    public decimal TotalValue { get; set; }
}

// Register it
modelBuilder.Entity<ProductSummary>().HasNoKey();

// Query it
var summaries = dbContext.Set<ProductSummary>()
    .FromSqlRaw(@"
        SELECT Category, COUNT(*) as Count, SUM(Price) as TotalValue 
        FROM Products 
        GROUP BY Category")
    .ToList();

Enter fullscreen mode Exit fullscreen mode

Keyless tells EF this type isn’t tracked and has no primary key. Perfect for aggregated results, views, or stored procedure results.

Database Functions

EF Core can call database-specific functions:

// SQL Server specific
var products = dbContext.Products
    .Where(p => EF.Functions.Like(p.Name, "%phone%"))
    .ToList();

// Date functions
var recent = dbContext.Orders
    .Where(o => EF.Functions.DateDiffDay(o.OrderDate, DateTime.Now) < 30)
    .ToList();

Enter fullscreen mode Exit fullscreen mode

EF.Functions provides database-specific operations that translate to native SQL functions.

When to Use Raw SQL

1. Complex stored procedures — especially legacy ones returning non-entity shapes
2. Database-specific features — window functions, CTEs, recursive queries
3. Performance-critical paths — when EF’s generated SQL is inefficient
4. Bulk operations — UPDATE/DELETE affecting many rows without loading entities
5. Administrative queries — database maintenance, statistics, metadata

When NOT to Use Raw SQL

1. Simple CRUD — LINQ handles this better with compile-time checking
2. Dynamic filters — LINQ composes; SQL concatenation leads to injection
3. When you haven’t measured — EF’s SQL is usually fine. Profile first.

The Hybrid Approach

Combine raw SQL for the complex part, LINQ for the rest:

var baseQuery = dbContext.Products
    .FromSqlRaw(@"
        SELECT p.* 
        FROM Products p
        INNER JOIN ProductMetrics pm ON p.Id = pm.ProductId
        WHERE pm.ViewCount > 1000");

// Add LINQ filtering
if (minPrice.HasValue)
    baseQuery = baseQuery.Where(p => p.Price >= minPrice.Value);

// Add LINQ projection
var results = baseQuery
    .Select(p => new { p.Id, p.Name, p.Price })
    .ToList();

Enter fullscreen mode Exit fullscreen mode

Raw SQL gets the complex join. LINQ handles dynamic filtering and projection safely.

The Rule

1. Returns entities? FromSqlRaw / FromSqlInterpolated + optional LINQ chain
2. Modifying data? ExecuteSqlRaw / ExecuteSqlInterpolated
3. Non-entity results? Keyless entity type or raw ADO.NET
4. User input? Always use Interpolated or parameters — never concatenate
5. Try LINQ first — raw SQL is an escape hatch, not the first tool

Next time, we’ll wrap up with LINQ performance tips — the tricks and pitfalls that separate a query that screams from one that crawls. Hope to see you!