Multi mapping SQL Server results using Dapper in C#

Question

These are my classes:

public class InvoiceModel
{
    public int Id { get; set; }
    public CustomerModel Customer { get; set; }
    public string DateCreated { get; set; }
    public ObservableCollection<InvoiceItemModel> Items { get; set; }
    public ObservableCollection<InvoicePaymentModel> Payments { get; set; }
}

public class CustomerModel
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public ObservableCollection<PhoneNumberModel> PhoneNumbers { get; set; }
}

public class PhoneNumberModel
{
    public int CustomerId { get; set; }
    public string PhoneNumber { get; set; }
}

public class InvoiceItemModel
{
    public int Id { get; set; }
    public int InvoiceId { get; set; }
    public int ProductId { get; set; }
    public long SellPrice { get; set; }
    public int Count { get; set; }
}

public class InvoicePaymentModel
{
    public int InvoiceId { get; set; }
    public string DateCreated { get; set; }
    public double PayAmount { get; set; }
}

I also have these tables in SQL Server: Customers, PhoneNumbers, Invoices, InvoiceItems and InvoicePayments which you can guess the columns.

How can I map these classes using Dapper?

I know I can use SplitOn for map Customer in Invoices, but I'm looking for a solution to do all these in one go (Least overhead on server and client). I know I should use QueryMultiple but I don't know how.

I use C# and SQL Server 2017

I use ObservableCollection because I use Caliburn Micro. EDIT I am fetching a set of InvoiceModel from DB (ObservableCollection<InvoiceModel>)

Answer 1

Use this syntax

var model = new InvoiceModel
{
    Customer = new CustomerModel
    {
        PhoneNumbers = new ObservableCollection<PhoneNumberModel>()
    }

};

var sql = "Your Select  Query";

using (var connection = new SqlConnection("Your Connection String"))
{
    connection.Open();

    var parameters = new DynamicParameters();

    //If you have parameteres add like here
    //parameters.Add("@Id", 1, DbType.Int32);
            
    using (var multi = connection.QueryMultiple(sql, parameters))
    {
         model = multi.Read<InvoiceModel>().FirstOrDefault();
         model.Customer = multi.Read<CustomerModel>().FirstOrDefault();
         var items = multi.Read<InvoiceItemModel>().ToList();
         var payments = multi.Read<InvoicePaymentModel>().ToList();

         model.Items = new ObservableCollection<InvoiceItemModel>(items);
         model.Payments = new ObservableCollection<InvoicePaymentModel>(payments);

     }
}

EDIT :

To Load ObservableCollection<InvoiceModel>

Use This Syntax

using (var connection = new SqlConnection("Your Connection string"))
{
       connection.Open();

          
       var sqlCommand = "select * from InvoiceModels as ic  inner JOIN" +
         " CustomerModel AS cm on ic.CustomerId = cm.Id ";


       var invoices = connection.Query(sqlCommand, new[] { typeof(InvoiceModel), typeof(CustomerModel), }, obj =>
                 {
                     var invoiceModel = obj[0] as InvoiceModel;
                     invoiceModel.Customer = obj[1] as CustomerModel;
                     return invoiceModel;

                 }).ToList();

       var obserInvoices = new ObservableCollection<InvoiceModel>(invoices);

       for (int i = 0; i < obserInvoices.Count; i++)
       {
             var phoneNumbers = connection.Query<PhoneNumberModel>
                   ("Select * from PhoneNumberModel Where CustomerModelId=@Id", new { @Id = obserInvoices[i].CustomerId }).ToList();
             if (phoneNumbers.Any())
                    obserInvoices[i].Customer.PhoneNumbers = new ObservableCollection<PhoneNumberModel>(phoneNumbers);

             var invoiceItemModel = connection.Query<InvoiceItemModel>
                   ("Select * from InvoiceItemModel Where InvoiceModelId=@Id", new { @Id = obserInvoices[i].Id }).ToList();
             if (invoiceItemModel.Any())
                    obserInvoices[i].Items = new ObservableCollection<InvoiceItemModel>(invoiceItemModel);


             var invoicePaymentModel = connection.Query<InvoicePaymentModel>
                  ("Select * from InvoicePaymentModel Where InvoiceModelId=@Id", new { @Id = obserInvoices[i].Id }).ToList();
             if (invoicePaymentModel.Any())
                    obserInvoices[i].Payments = new ObservableCollection<InvoicePaymentModel>(invoicePaymentModel);

       }


}