Dapper Dynamic QueryMultiple

Question

I'm wanting to query 2 tables and return 2 result sets using dapper.

Here is the code i'm trying.

This code builds but QueryMultiple looks like its not able to get the second result set or something. I have tried looping the results and using .Read() which is dynamic.

Do you think it could not be working because i'm trying to add 2 different sizes of tables to a single dynamic list?

    public virtual IEnumerable<dynamic> QueryDatabase(Report report)
    {
        if (report == null) return null;
        using (var conn = new SqlConnection(this.configurationHelper.GetConnectionStringByName(report.ConnectionName)))
        {
            conn.Open();
            var sql = report.Query;
            //var results = conn.QueryMultiple(sql).Read<dynamic>();

            using (var multi = conn.QueryMultiple(sql))
            {
                var result = new List<dynamic>();

                foreach (var item in multi.Read())
                {
                    result.Add(item);
                }
                return result;
            }
        }
    }

Answer 1

Why not create models for each returning table? example

A class to represent the "Hub" Table

public class Hub
    {
        public byte Hub_Id { get; set; }
        public string Hub_Name { get; set; }
        public bool Hub_IsEnabled { get; set; }
    }

A Class to represent "Opco" Table

public class Opco
    {
        public string Opco_Id { get; set; }
        public string Opco_Country { get; set; }
        public byte Opco_Hub_Id { get; set; }
        public bool Opco_IsEnabled { get; set; }
    }

A ViewModel to represent the ViewModel

public class ReportViewModel
    {
        public List<Opco> OpcoList { get; set; }
        public List<Hub> HubList { get; set; }

    }

The dapper code to return the view Model

 string connString = Utility.Common.GetConnectionString();
        public ReportViewModel GetReportViewModel()
        {
            ReportViewModel reportViewModel = new ReportViewModel();

            using (var conn = new SqlConnection(connString))
            {
                conn.Open();

                using (var multi =
                                conn.QueryMultiple("GetReportViewModel", null, null,
                                commandTimeout: 0,
                                commandType: CommandType.StoredProcedure))
                {
                    reportViewModel.HubList = multi.Read<Hub>().ToList();
                    reportViewModel.OpcoList = multi.Read<Opco>().ToList();
                }
            }
            return reportViewModel;
        }

The SP must be like the following, fields must be in same order as the class and the SELECT must be in the order of the ViewModel, I meant first Select the Hubs and later the Opcos.

ALTER PROCEDURE [dbo].[GetReportViewModel]

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT 
        Hub_Id,
        Hub_Name
    FROM [dbo].[Tbl_Hubs]
    WHERE [Hub_IsEnabled] = 1;

    SELECT
        OpCo_Id,
        OpCo_Country
    FROM [dbo].[Tbl_OpCos]
    WHERE [OpCo_IsEnabled] = 1;
END