Raw SQL

Forge.Repository exposes raw SQL access at two levels — via IRepositoryAsync<T> for most cases, and via IDataContext for advanced scenarios like cross-entity reports and stored procedure execution. Both levels support full async execution, cancellation tokens, and positional parameters.

Raw SQL bypasses EF Core’s query pipeline. It does not apply global query filters (e.g. soft-delete filters), does not track returned entities, and does not validate or sanitize SQL. Always use parameterised queries to prevent SQL injection.

IRepositoryAsync — Raw SQL Methods

FindAllBySql<TF>

Executes a raw SELECT statement and materialises the result set into a list of TF. TF can be your entity type, a DTO, or any class whose properties match the column names in the result set.


// Without parameters
Task<IList<TFEntity>> FindAllBySql<TFEntity>(
    string sql,
    CancellationToken cancellationToken);
 
// With positional parameters (@p0, @p1, ...)
Task<IList<TFEntity>> FindAllBySql<TFEntity>(
    string sql,
    CancellationToken cancellationToken,
    params object[] parameters);

Returning entities


// Simple — no parameters
var allActive = await _products.FindAllBySql<Product>(
    "SELECT * FROM Products WHERE IsDeleted = 0",
    ct);
 
// Parameterised — use @p0, @p1 positional placeholders
var byCategoryAndPrice = await _products.FindAllBySql<Product>(
    "SELECT * FROM Products WHERE CategoryId = @p0 AND Price < @p1 AND IsDeleted = 0",
    ct,
    "electronics", 500m);

Returning a DTO

The result columns must map to writable properties on TF. Property names are matched case-insensitively.


public record StockLevelDto(string ProductId, string ProductName, int StockQuantity);
 
var stockLevels = await _products.FindAllBySql<StockLevelDto>(
    @"SELECT p.Id         AS ProductId,
             p.Name       AS ProductName,
             s.Quantity   AS StockQuantity
      FROM   Products p
      JOIN   Stock s ON s.ProductId = p.Id
      WHERE  p.IsDeleted = 0
      ORDER  BY s.Quantity ASC",
    ct);

ExecuteSqlRaw

Executes a non-query SQL statement and returns the number of rows affected. Use for INSERT, UPDATE, DELETE, or stored procedure calls that do not return a result set.


Task<int> ExecuteSqlRaw(string sql, CancellationToken cancellationToken);
Task<int> ExecuteSqlRaw(string sql, CancellationToken cancellationToken, params object[] parameters);


// Bulk soft-delete expired products
var rows = await _products.ExecuteSqlRaw(
    "UPDATE Products SET IsDeleted = 1 WHERE ExpiresAt < @p0",
    ct,
    DateTime.UtcNow);
 
// Call a stored procedure
await _products.ExecuteSqlRaw(
    "EXEC sp_ArchiveProducts @cutoffDate = @p0",
    ct,
    DateOnly.FromDateTime(DateTime.UtcNow.AddYears(-2)));

IDataContext — Advanced Raw SQL

IDataContext is exposed via your AppDbContext which implements it. Inject IDataContext directly when you need raw SQL that spans multiple entity types — for example, reporting queries that join across tables.


namespace Forge.Interfaces;
 
public interface IDataContext
{
    Task<IList<TFEntity>> GetEntitiesFromSqlRaw<TFEntity>(
        string sql, CancellationToken cancellationToken);
 
    Task<IList<TFEntity>> GetEntitiesFromSqlRaw<TFEntity>(
        string sql, CancellationToken cancellationToken, params object[] parameters);
 
    Task<int> ExecuteSqlRaw(string sql, CancellationToken cancellationToken);
 
    Task<int> ExecuteSqlRaw(string sql, CancellationToken cancellationToken,
        params object[] parameters);
 
    // ... additional DbContext members
}

Registering IDataContext

Your AppDbContext inherits from Forge.Repository.DbContext which implements IDataContext. Register it explicitly if you need to inject it in services:


// Program.cs
builder.Services.AddScoped<IDataContext>(sp =>
    sp.GetRequiredService<AppDbContext>());

Cross-entity reporting


public class ReportService(IDataContext ctx)
{
    public Task<IList<SalesReportDto>> GetMonthlySalesAsync(
        int year, int month, CancellationToken ct)
        => ctx.GetEntitiesFromSqlRaw<SalesReportDto>(
            @"SELECT p.Name         AS ProductName,
                     c.Name         AS CategoryName,
                     SUM(oi.Qty)    AS TotalUnits,
                     SUM(oi.Qty * oi.UnitPrice) AS Revenue
              FROM   OrderItems oi
              JOIN   Products p   ON p.Id   = oi.ProductId
              JOIN   Categories c ON c.Id   = p.CategoryId
              JOIN   Orders o     ON o.Id   = oi.OrderId
              WHERE  YEAR(o.CreatedOn)  = @p0
              AND    MONTH(o.CreatedOn) = @p1
              AND    o.IsDeleted        = 0
              GROUP  BY p.Name, c.Name
              ORDER  BY Revenue DESC",
            ct,
            year, month);
 
    public Task<int> CleanupOrphanedDraftsAsync(CancellationToken ct)
        => ctx.ExecuteSqlRaw(
            @"DELETE FROM Orders
              WHERE  Status    = 'Draft'
              AND    CreatedOn < @p0",
            ct,
            DateTime.UtcNow.AddDays(-30));
}

Parameterisation

All raw SQL methods use positional parameters — @p0, @p1, @p2, and so on. The values are passed in order via the params object[] argument.


// SQL uses @p0, @p1, @p2
await _repo.ExecuteSqlRaw(
    "UPDATE Products SET Price = @p0, ModifiedOn = @p1 WHERE CategoryId = @p2",
    ct,
    newPrice,          // @p0
    DateTime.UtcNow,   // @p1
    categoryId);       // @p2

Never interpolate user input into raw SQL strings. Always use the params object[] overloads. EF Core passes these as true database parameters, which prevents SQL injection.


// ✅ Safe — user input is a parameter
await _repo.FindAllBySql<Product>(
    "SELECT * FROM Products WHERE Name = @p0", ct, userInput);
 
// ❌ Dangerous — never do this
await _repo.FindAllBySql<Product>(
    $"SELECT * FROM Products WHERE Name = '{userInput}'", ct);

Raw SQL Inside Criteria

You can use FindAllBySql or ExecuteSqlRaw inside a criteria object for advanced scenarios where even complex LINQ is insufficient.


public class FullTextSearchCriteria : ICriteriaAsync<Product>
{
    private readonly string _searchTerm;
    private readonly IRepositoryAsync<Product> _repo;
 
    public FullTextSearchCriteria(string searchTerm, IRepositoryAsync<Product> repo)
    {
        _searchTerm = searchTerm;
        _repo       = repo;
    }
 
    public async Task<IList<Product>> MatchQueryFromAsync(
        IQueryable<Product> data, CancellationToken ct)
        // Fall back to raw SQL for full-text search
        => await _repo.FindAllBySql<Product>(
            @"SELECT * FROM Products
              WHERE CONTAINS((Name, Description), @p0)
              AND   IsDeleted = 0",
            ct,
            $'"{_searchTerm}"');
}

Common Patterns

Pagination with total count


public record PagedResult<T>(IList<T> Items, int Total, int Page, int PageSize);
 
public class ProductService(IUnitOfWork uow, IDataContext ctx)
{
    private readonly IRepositoryAsync<Product> _products = uow.GetRepositoryAsync<Product>();
 
    public async Task<PagedResult<ProductSummaryDto>> GetPagedAsync(
        string? categoryId, int page, int pageSize, CancellationToken ct)
    {
        var where = categoryId is null
            ? "WHERE IsDeleted = 0"
            : "WHERE IsDeleted = 0 AND CategoryId = @p0";
 
        var countSql = $"SELECT COUNT(*) FROM Products {where}";
        var itemsSql = $@"SELECT Id, Name, Price, CreatedOn
                          FROM   Products
                          {where}
                          ORDER  BY Name
                          OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY";
 
        var total = (await ctx.GetEntitiesFromSqlRaw<CountResult>(
            countSql, ct, categoryId!)).FirstOrDefault()?.Count ?? 0;
 
        var items = await ctx.GetEntitiesFromSqlRaw<ProductSummaryDto>(
            itemsSql, ct, categoryId!, (page - 1) * pageSize, pageSize);
 
        return new PagedResult<ProductSummaryDto>(items, total, page, pageSize);
    }
}
 
private record CountResult(int Count);

When to Use Raw SQL

Situation	Recommended approach
Simple CRUD + filters	LINQ via `IRepositoryAsync<T>`
Complex joins + ordering	Criteria with LINQ + `.Include()`
Full-text search	`FindAllBySql`
Reporting / aggregations	`IDataContext.GetEntitiesFromSqlRaw`
Bulk UPDATE / DELETE	`ExecuteSqlRaw`
Stored procedure calls	`ExecuteSqlRaw`
Cross-database queries	Not supported — use a separate context