How to Execute Raw SQL Queries with Custom Data Types in Entity Framework Core?

Executing Raw SQL Queries with Custom Data Types in Entity Framework Core

Entity Framework Core's evolution has altered how raw SQL queries are handled, especially concerning custom data types. The dbData.Database.SqlQuery<somemodel> method is no longer available in recent versions. This guide details how to execute raw SQL queries returning custom types in various EF Core versions.

EF Core 8 and Later

EF Core 8 and subsequent releases significantly enhance the SqlQuery method, directly supporting the return of arbitrary types. This simplifies the process considerably.

EF Core 3.0 and Later

Leveraging Keyless Entity Types:

The recommended approach for handling custom data types in raw SQL queries within EF Core 3.0 and later is to employ keyless entity types. This is achieved using the [Keyless] attribute or the .HasNoKey() configuration method.

<code class="language-csharp">[Keyless]
public class SomeModel
{
    // Properties
}</code>

Execute your query using either FromSqlRaw or FromSql:

<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 (RC1)

Utilizing DbQuery:

In EF Core 2.1 RC1, define a DbQuery<T> property within your DbContext and use the FromSql method:

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

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<SomeModel>().HasNoKey();
}

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

EF Core 2.0

Alternative Strategies:

EF Core 2.0 requires workarounds:

• Custom DTOs: Create dedicated Data Transfer Objects (DTOs) to map query results. This necessitates manual mapping of data.
• SqlCommand: Directly use SqlCommand. This bypasses EF Core's features, reducing its benefits.

The above is the detailed content of How to Execute Raw SQL Queries with Custom Data Types in Entity Framework Core?. For more information, please follow other related articles on the PHP Chinese website!