Working with multiple resultset in .net core

Question

Working with multiple resultset in .net core

asp.net-core asp.net-core-mvc c# entity-framework-core

Question

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 ?

XamDev

Popular Answer

You can use this:

using (SqlCommand cmd = new SqlCommand("sp_db",
            new SqlConnection("your_db_connection")))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@param", SqlDbType.VarChar).Value = param;

            cmd.Connection.Open();
            cmd.ExecuteNonQuery();

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    string a = (string)reader["ATTRIBUTE_A"];
                    string b = (string)reader["ATTRIBUTE_B"];
                }
            }
        }

I didn't set output params for the attributes in my SP, but you can extract them by using SqlCommand.

Hope this helps.

Alan Uscanga

View more on Stack Overflow

Ricky G · Accepted Answer · 9/11/2018 10:55:55 PM

Accepted Answer

Currently, EF Core doesn't not support this. There is an open issue to address this.

https://github.com/aspnet/EntityFramework/issues/8127

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

Ricky G

Working with multiple resultset in .net core

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related