Querying a custom type with Database.SqlQuery<T> in EF Core 7.0

Question

The recently released Entity Framework Core 7.0 offers a new DatabaseFacade.SqlQuery<T> function, which allows to run custom SQL queries directly on the underlying database context.

For custom SQL queries, I am currently using Dapper, which I would like to replace by EF's new native SqlQuery function. The respective queries cannot be easily expressed as LINQ, which EF could translate to SQL itself. Also, they return result objects that are not part of the database model.

As an illustrating example, take these simple classes and SQL query:

public class Book   // Mapped to database table 'books'
{
    public int Id { get; set; }
    public string Title { get; set; }

    public int AuthorId { get; set; }
    public Author Author { get; set; } // References some complex Author object

    // ...lots of other properties...
}

// Simple type for search results
public class BookSearchResult
{
    public string AuthorName { get; set; }
    public string BookTitle { get; set; }
}

// Search for books which satisfy a complex query
var books = _dbContext.Database.GetDbConnection().QueryAsync<BookSearchResult>(@"
    SELECT a.`Name` AS 'AuthorName', b.`Title` AS 'BookTitle'
    FROM `books` b
    JOIN `authors` a ON a.Id = b.AuthorId
    ...complex query...
");

I would like to replace this by

var books = _dbContext.Database.SqlQuery<BookSearchResult>(@"
    ...same query...
");

---

Now, the documentation of SqlQuery also states the following (bold formatting added by me):

Creates a LINQ query based on a raw SQL query, which returns a result set of a scalar type natively supported by the database provider.

I.e., it can/should only be used to query scalar types, but not custom objects.

There is a hint in the remarks that one can define a new default type mapping, but I don't really understand how this can be achieved in practice (or whether it is even possible).

Can I map the result of a DbConnection.Database.SqlQuery<T> call to a custom object? If so, how?

Answer 1

Currently (EF Core 7) you can't.

This is a long time asked feature, eventually coming with EF Core 8 SQL queries for unmapped types:

Tracked by Issue #10753: Support raw SQL queries without defining an entity type for the result

Value proposition: Applications can execute more types of SQL query without dropping down to ADO.NET or using third-party libraries.

Currently SQL queries must return a type in the model or a scalar type. In EF8, we plan to allow SQL queries that directly return types that are not contained in the EF model.