How to handle data set having multiple tables in Dotnet core using Fromsql()

Question

How to handle data set having multiple tables in Dotnet core using Fromsql()

.net .net-core asp.net asp.net-core-webapi entity-framework-core

Question

I am creating WebApi using dot net core

I have created procedure which returns data from 2 tables AS:-

select * from table1
select * from table2

I have model list as:-

public class ModelList
{
public model1 m1 { get; set; }
public model2 m2 { get; set; }
}

i am executing procedure and receiving result in mode list as:-

List<ModelList> ML= new List<ModelList>();
ML= db.ModelList.FromSql("EXECUTE [dbo].[usp]").ToList();

I am not getting the result of those two tables in these two separate objects.

it works with single table but not with multiple.

please suggest where i am making mistake ?

or whether it is doable in dot net core ?

Munish Sharma

View more on Stack Overflow

Yared · Accepted Answer · 9/21/2017 11:48:30 AM

The following implementation will provide the desired result:

public ModelList ExecuteMultiple(string query){

             using( IDbConnection connection = new SqlConnection(YOUR_CONNECTION_STRING)){
                try{
                    connection.Open();
                    var multi = connection.QueryMultiple(query);
                    var modelList = new ModelList();
                    modelList.m1 = multi.Read<model1>();
                    modelList.m2 = multi.Read<model2>();
                    return modelList;
                }
                catch( Exception ex){
                    Console.WriteLine(ex.Message);
                }finally{
                    connection.Close();
                }
             }

             return null;
         }

Usage:

var modelList = ExecuteMultiple("EXECUTE [dbo].[usp]");

How to handle data set having multiple tables in Dotnet core using Fromsql()

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related