在使用存储过程检索结果时，如何在 .net 核心的视图模型中检索和存储多个结果集
While retrieving the results using stored procedure how can I retrieve and store multiple result set in view model in .net core
例如从存储过程中，我返回以下两个查询的记录
For e.g. from stored procedure I am returning records for below two queries
Select * LMS_Survey
Select * from LMS_SurveyQuestion
Select * from LMS_SurveyQuestionOptionChoice
下面是两个表的视图模型
and below is view model for two tables
public class LMS_SurveyTraineeViewModel
{
public LMS_SurveyDetailsViewModel SurveyDetailsViewModel { get; set; }
public LMS_SurveyQuestionsViewModel SurveyQuestionsViewModel { get; set; }
public LMS_SurveyQuestionOptionChoiceViewModel SurveyQuestionOptionChoiceViewModel { get; set; }
}
这就是我执行存储过程的方式
This is how I am executing the stored procedure
public List<LMS_SurveyTraineeViewModel> GetTraineeSurvey(int surveyID)
{
try
{
List<LMS_SurveyTraineeViewModel> modelList = new List<LMS_SurveyTraineeViewModel>();
modelList = dbcontext.Set<LMS_SurveyTraineeViewModel>().FromSql("LMSSP_GetTraineeSurvey @surveyID = {0},@LanguageID = {1}", surveyID, AppTenant.SelectedLanguageID).ToList();
return modelList;
}
catch (Exception ex)
{
throw ex;
}
}
如何在视图模型中使用存储过程存储多个结果集?
How can stored the multiple result set using stored procedure in view model ?
目前，EF Core 不支持这个.有一个未解决的问题可以解决.
Currently, EF Core doesn't not support this. There is an open issue to address this.
https://github.com/aspnet/EntityFramework/issues/8127
2018 年 9 月 12 日更新:即使对于 3.0 版，这仍然不是 EF Core 的优先事项；因此，当您有多个结果场景时，最好使用 Dapper 或普通 ADO.NET
Update 12th Sep 2018: This is still not a priority for EF Core even for release 3.0; so best use Dapper or plain ADO.NET when you have multiple results scenario
2020 年 6 月 25 日更新:即使是 5.0 版，EF Core 仍处于积压状态；因此，当您有多个结果场景时，最好使用 Dapper 或普通 ADO.NET
Update 25th Jun 2020: still on the backlog for EF Core even for release 5.0; so best use Dapper or plain ADO.NET when you have multiple results scenario
2021 年 2 月 7 日更新: EF Core 仍在积压中
Update 7th Feb 2021: still on the backlog for EF Core
在此期间，GitHub 问题上发布了一个出色的解决方案，供任何通过扩展方法寻找替代方案的人使用
public static async Task<IList<IList>> MultiResultSetsFromSql(this DbContext dbContext, ICollection<Type> resultSetMappingTypes, string sql, params object[] parameters)
{
var resultSets = new List<IList>();
var connection = dbContext.Database.GetDbConnection();
var parameterGenerator = dbContext.GetService<IParameterNameGeneratorFactory>()
.Create();
var commandBuilder = dbContext.GetService<IRelationalCommandBuilderFactory>()
.Create();
foreach (var parameter in parameters)
{
var generatedName = parameterGenerator.GenerateNext();
if (parameter is DbParameter dbParameter)
commandBuilder.AddRawParameter(generatedName, dbParameter);
else
commandBuilder.AddParameter(generatedName, generatedName);
}
using var command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
command.Connection = connection;
for (var i = 0; i < commandBuilder.Parameters.Count; i++)
{
var relationalParameter = commandBuilder.Parameters[i];
relationalParameter.AddDbParameter(command, parameters[i]);
}
var materializerSource = dbContext.GetService<IEntityMaterializerSource>();
if (connection.State == ConnectionState.Closed)
await connection.OpenAsync();
using var reader = await command.ExecuteReaderAsync();
foreach (var pair in resultSetMappingTypes.Select((x, i) => (Index: i, Type: x)))
{
var i = pair.Index;
var resultSetMappingType = pair.Type;
if (i > 0 && !(await reader.NextResultAsync()))
throw new InvalidOperationException(string.Format("No result set at index {0}, unable to map to {1}.", i, resultSetMappingType));
var type = resultSetMappingType;
var entityType = dbContext.GetService<IModel>()
.FindEntityType(type);
if (entityType == null)
throw new InvalidOperationException(string.Format("Unable to find a an entity type (or query type) matching '{0}'", type));
var relationalTypeMappingSource = dbContext.GetService<IRelationalTypeMappingSource>();
var columns = Enumerable.Range(0, reader.FieldCount)
.Select(x => new
{
Index = x,
Name = reader.GetName(x)
})
.ToList();
var relationalValueBufferFactoryFactory = dbContext.GetService<IRelationalValueBufferFactoryFactory>();
int discriminatorIdx = -1;
var discriminatorProperty = entityType.GetDiscriminatorProperty();
var entityTypes = entityType.GetDerivedTypesInclusive();
var instanceTypeMapping = entityTypes.Select(et => new
{
EntityType = et,
Properties = et.GetProperties()
.Select(x =>
{
var column = columns.FirstOrDefault(y => string.Equals(y.Name,
x.GetColumnName() ?? x.Name, StringComparison.OrdinalIgnoreCase)) ?? throw new InvalidOperationException(string.Format("Unable to find a column mapping property '{0}'.", x.Name));
if (x == discriminatorProperty)
discriminatorIdx = column.Index;
return new TypeMaterializationInfo(x.PropertyInfo.PropertyType, x, relationalTypeMappingSource, column.Index);
})
.ToArray()
})
.Select(x => new
{
EntityType = x.EntityType,
Properties = x.Properties,
ValueBufferFactory = relationalValueBufferFactoryFactory.Create(x.Properties)
})
.ToDictionary(e => e.EntityType.GetDiscriminatorValue() ?? e.EntityType, e => e)
;
var resultSetValues = (IList)Activator.CreateInstance(typeof(List<>).MakeGenericType(type));
while (await reader.ReadAsync())
{
var instanceInfo = discriminatorIdx < 0 ? instanceTypeMapping[entityType] : instanceTypeMapping[reader[discriminatorIdx]];
var valueBuffer = instanceInfo.ValueBufferFactory.Create(reader);
var materializationAction = materializerSource.GetMaterializer(instanceInfo.EntityType);
resultSetValues.Add(materializationAction(new MaterializationContext(valueBuffer, dbContext)));
}
resultSets.Add(resultSetValues);
}
return resultSets;
}
以及扩展类型的方法
public static async Task<(IReadOnlyCollection<T1> FirstResultSet, IReadOnlyCollection<T2> SecondResultSet)> MultiResultSetsFromSql<T1, T2>(this DbContext dbContext, string sql, params object[] parameters)
{
var resultSetMappingTypes = new[]
{
typeof(T1), typeof(T2)
};
var resultSets = await MultiResultSetsFromSql(dbContext, resultSetMappingTypes, sql, parameters);
return ((IReadOnlyCollection<T1>)resultSets[0], (IReadOnlyCollection<T2>)resultSets[1]);
}
public static async Task<(IReadOnlyCollection<T1> FirstResultSet, IReadOnlyCollection<T2> SecondResultSet, IReadOnlyCollection<T3> ThirdResultSet)> MultiResultSetsFromSql<T1, T2, T3>(this DbContext dbContext, string sql, params object[] parameters)
{
var resultSetMappingTypes = new[]
{
typeof(T1), typeof(T2), typeof(T3)
};
var resultSets = await MultiResultSetsFromSql(dbContext, resultSetMappingTypes, sql, parameters);
return ((IReadOnlyCollection<T1>)resultSets[0], (IReadOnlyCollection<T2>)resultSets[1], (IReadOnlyCollection<T3>)resultSets[2]);
}
这篇关于在 .net 核心中使用多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！