更新时间:2023-09-11 21:31:10
在 EF Core 中,您还不能从原始 SQL 查询中返回即席类型(他们正在解决这个问题),因此您需要一个解决方法问题.将此类添加到您的项目中:
In EF Core you can't return ad-hoc types from raw SQL queries yet (they are working on this), so you will need a workaround for this issue. Add this class to your project:
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Threading;
using System.Threading.Tasks;
namespace Microsoft.EntityFrameworkCore
{
public static class RDFacadeExtensions
{
public static RelationalDataReader ExecuteSqlQuery(this DatabaseFacade databaseFacade, string sql, params object[] parameters)
{
var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();
using (concurrencyDetector.EnterCriticalSection())
{
var rawSqlCommand = databaseFacade
.GetService<IRawSqlCommandBuilder>()
.Build(sql, parameters);
return rawSqlCommand
.RelationalCommand
.ExecuteReader(
databaseFacade.GetService<IRelationalConnection>(),
parameterValues: rawSqlCommand.ParameterValues);
}
}
}
}
然后您可以调用下面的方法并从您的 SP 获取 OUTPUT,这是一个示例:
Then you can call the method below and get the OUTPUT from you SP, here's a sample:
var _sMsg = new SqlParameter("sMsg", "")
{
Direction = ParameterDirection.Output,
DbType = DbType.String,
Size = 500
};
var sql = "exec sp_foo @sUserId, @sMsg OUTPUT";
using (var dr = _ctx.Database.ExecuteSqlQuery(sql, _sUserID, _sMsg))
{
//here you can retrive your table
while (dr.DbDataReader.Read())
{
var bar = dr.DbDataReader[0].ToString();
}
//here is your OUTPUT
return _sMsg.Value.ToString();
}