Raw SQL Queries without DbSet in Entity Framework Core

Problem Background and Challenges

Throughout Entity Framework Core's development, developers frequently encounter scenarios requiring raw SQL queries, particularly when query results include computed fields (such as full-text search rankings) that cannot be directly mapped to existing entity models. The removal of the traditional dbData.Database.SqlQuery<SomeModel> method in EF Core has necessitated alternative approaches for handling these complex query requirements.

EF Core 8.0 and Newer Solutions

Starting with EF Core 8.0, Microsoft introduced support for raw SQL queries returning unmapped types. The SqlQuery method can now return arbitrary types, not just scalar values. This enables developers to directly execute SQL queries returning complex objects (including computed fields) without pre-defining entity mappings.

// EF Core 8.0+ Example
var results = await context.Database
    .SqlQuery<SearchResult>("SELECT Name, RANK() OVER (ORDER BY Score DESC) AS Rank FROM Products")
    .ToListAsync();

public class SearchResult
{
    public string Name { get; set; }
    public int Rank { get; set; }
}

EF Core 3.0-7.0: Keyless Entity Types

For EF Core versions 3.0 through 7.0, keyless entity types are recommended for handling such scenarios. Keyless entity types were known as query types in EF Core 2.1 and were renamed and enhanced in version 3.0.

Configuring Keyless Entity Types

First, configure entity classes as keyless types using data annotations or fluent configuration:

// Using Data Annotations
[Keyless]
public class ProductSearchResult
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public decimal SearchRank { get; set; }
}

// Or configure in DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProductSearchResult>().HasNoKey();
}

// Register DbSet
public DbSet<ProductSearchResult> ProductSearchResults { get; set; }

Executing Raw SQL Queries

After configuration, use FromSqlRaw or FromSql methods to execute queries:

// Synchronous version
var results = context.ProductSearchResults
    .FromSqlRaw("SELECT ProductId, Name AS ProductName, RANK() OVER (ORDER BY Relevance) AS SearchRank FROM Products WHERE CONTAINS(Name, @p0)", searchTerm)
    .ToList();

// Asynchronous version
var results = await context.ProductSearchResults
    .FromSqlInterpolated($"SELECT ProductId, Name AS ProductName, RANK() OVER (ORDER BY Relevance) AS SearchRank FROM Products WHERE CONTAINS(Name, {searchTerm})")
    .ToListAsync();

EF Core 2.1: Query Types Approach

For projects still using EF Core 2.1, query types provide similar functionality:

// Define DbQuery in DbContext
public DbQuery<ProductSearchResult> ProductSearchResults { get; set; }

// Execute query
var results = context.ProductSearchResults
    .FromSql("SELECT ProductId, Name AS ProductName, RANK() OVER (ORDER BY Relevance) AS SearchRank FROM Products")
    .ToList();

Parameterized Queries and Security

Parameterization is crucial for preventing SQL injection attacks when executing raw SQL queries. EF Core provides secure parameterization mechanisms:

// Secure parameterized query
var searchTerm = "electronics";
var minRank = 0.5;

var results = await context.ProductSearchResults
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchProducts({searchTerm}) WHERE Rank > {minRank}")
    .ToListAsync();

// Using named parameters
var searchParam = new SqlParameter("searchTerm", "electronics");
var rankParam = new SqlParameter("minRank", 0.5);

var results = await context.ProductSearchResults
    .FromSqlRaw("SELECT * FROM dbo.SearchProducts(@searchTerm) WHERE Rank > @minRank", searchParam, rankParam)
    .ToListAsync();

Dynamic SQL and FromSqlRaw Usage

In scenarios requiring dynamic SQL construction, use FromSqlRaw with careful attention to security:

// Dynamic column name query (handle with caution)
var columnName = GetSafeColumnName(userInput); // Must validate and sanitize input
var value = new SqlParameter("value", searchValue);

var results = await context.ProductSearchResults
    .FromSqlRaw($"SELECT * FROM Products WHERE {columnName} = @value", value)
    .ToListAsync();

Combining with LINQ

Raw SQL queries can be combined with LINQ operators, with EF Core treating the SQL as a subquery:

var searchTerm = "laptop";
var results = await context.ProductSearchResults
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchProducts({searchTerm})")
    .Where(r => r.SearchRank > 0.8)
    .OrderByDescending(r => r.SearchRank)
    .Take(10)
    .ToListAsync();

Performance Considerations and Best Practices

When using raw SQL queries, consider the following performance factors:

• Always use parameterized queries to prevent SQL injection
• For frequently used queries, consider database views or stored procedures
• Use AsNoTracking for read-only queries to improve performance
• Implement proper pagination to reduce data transfer

// Read-only query optimization
var results = await context.ProductSearchResults
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchProducts({searchTerm})")
    .AsNoTracking()
    .Where(r => r.SearchRank > 0.5)
    .Skip(0)
    .Take(20)
    .ToListAsync();

Version Compatibility Summary

Raw SQL query support across different EF Core versions:

• EF Core 8.0+: Full SqlQuery support for arbitrary types
• EF Core 7.0: SqlQuery support for scalar type queries
• EF Core 3.0-6.0: Use keyless entity types
• EF Core 2.1-2.2: Use query types

By selecting the appropriate solution for your current EF Core version, developers can effectively handle complex SQL queries with computed fields while maintaining code security and performance.