在 Entity Framework Core 中使用 DbCommand 自由的執行 SQL 語法

在上一篇 在 Entity Framework Core 中使用 SQL 語法查詢 中我們透過 FromSqlInterpolated 和 FromSqlRaw 關鍵字在 Entity Framework Core 中執行 SQL 查詢，並把結果直接對應到 DbSet 當中。

但是假設我們要取得資料，就要先依照回傳值建立自訂類別，假設依照回傳值自訂 ResultModel 類別，就需要先在 ApplicationDbContext 類別(這裡指的是繼承 DbContext 的類別) 中使用類似下面的語法宣告

    
public virtual DbSet<ResultModel> Results { get; set; } = null!;
    

然後才能使用 FromSqlInterpolated 和 FromSqlRaw 來取得資料

    
// private readonly ApplicationDbContext _context;

ResultModel? result = _context.Set<ResultModel>().FromSqlInterpolated(
                $"select * from result_table"
            )
            .FirstOrDefault();
    

註： 本文中使用 EF Core 6.0，若使用 EF Core 7.0 或以上則會找不到 FromSqlInterpolated 關鍵字，需要使用 FromSql 取代。

如果少了 DbSet 宣告的那行，就會拋出類似下列錯誤：

    
[22:44:30 ERR] An unhandled exception has occurred while executing the request.
System.InvalidOperationException: Cannot create a DbSet for 'ResultModel' because this type is not included in the model for the context.
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.get_EntityType()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.CheckState()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.get_EntityQueryable()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.System.Linq.IQueryable.get_Provider()
   at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.FromSqlInterpolated[TEntity](DbSet`1 source, FormattableString sql)
   at ruyut.Controllers.RuyutController.Result(String categoryCode, String categoryCode2, String resolutionDate) in C:\Users\ruyut\Documents\RiderProjects\text\tcc_mis\ruyut\Controllers\RuyutController.cs:line
 239
   at lambda_method235(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
    

如果每個查詢都要先建立回傳資料結果物件，非常麻煩，有時候我們只是想要接收個 Json 資料，然後還要再加個 DbSet，好麻煩，有沒有其他方式？
在 Entity Framework Core 中也有支援 DbConnection ，可以使用「傳統」的查詢方式：

    
// private readonly ApplicationDbContext _context;

using var command = _context.Database.GetDbConnection().CreateCommand();
if (command.Connection == null) throw new Exception("Connection to database failed");
if (command.Connection?.State != ConnectionState.Open)
    command.Connection?.Open();

command.CommandText = sql;
if (parameters != null)
    foreach (var parameter in parameters)
    {
        command.Parameters.Add(new SqlParameter(parameter.Key, parameter.Value));
    }

var reader = command.ExecuteReader();

while (reader.Read())
{
    var str1 = reader.GetString(0);
}

if (command.Connection?.State != ConnectionState.Closed)
    command.Connection?.Close();