How Can I Execute Raw SQL Queries Without DbSets in Entity Framework Core?

Workarounds for Raw SQL Queries without DbSets in Entity Framework Core

Entity Framework Core's removal of dbData.Database.SqlQuery<somemodel> presents a challenge when executing raw SQL queries that return unmapped entities, particularly for tasks like full-text searches with ranking. Here's how to overcome this limitation:

EF Core 8 and Later:

The simplest solution in EF Core 8 and later versions is to use SqlQuery directly. It now supports returning arbitrary types, eliminating the need for keyless entities in many cases.

EF Core 3.0 and Above:

The recommended approach for EF Core 3.0 and later is to utilize keyless entity types:

1. Define a keyless entity type using the [Keyless] attribute or fluent API's .HasNoKey().
2. Execute your SQL query using FromSqlRaw or FromSqlAsync:

<code class="language-csharp">var result = context.SomeModels.FromSqlRaw("SQL SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();</code>

EF Core 2.1 and Above:

For older versions (EF Core 2.1 and up), consider using query types:

1. Declare a DbQuery<T> property in your DbContext, where T is a custom class matching your query's output structure.
2. Use FromSql on this DbQuery property:

<code class="language-csharp">public DbQuery<SomeModel> SomeModels { get; set; }

var result = context.SomeModels.FromSql("SQL_SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();</code>

These methods offer flexibility in retrieving data from raw SQL queries, accommodating scenarios involving ranking and non-mapped entities within Entity Framework Core.

The above is the detailed content of How Can I Execute Raw SQL Queries Without DbSets in Entity Framework Core?. For more information, please follow other related articles on the PHP Chinese website!