Using Dapper to return list of objects, each parent containing two other objects-CodePudding

I'm new to Dapper, and have a 3 model class example. The SQL Database has associated tables for each model below.

My aim is to populate a list of Job objects (ie. all records in the Job table), each containing its associated Status & Client objects. How would this be achieved using Dapper?

Thank you kindly for any help you can give me!

The SQL will be :

SELECT j.*, s.*, c.* FROM Job j INNER JOIN Status s ON j.StatusId = s.Id INNER JOIN Client ON j.ClientId = c.Id;

The models are :

    public class StatusModel
    {
          public int Id { get; set; }
          public string StatusName { get; set; }
    }


   public class ClientModel
   {
      public int Id { get; set; }
      public string ClientName { get; set; }
      
   }


    public class JobModel
    {

      public int Id { get; }

      public string Description { get; set; }

      public StatusModel Status {get; set; }
      public ClientModel Client { get; set; }
    }

CodePudding user response：

You can use Dapper's multimap feature.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    var jobs = await connection.QueryAsync<JobModel, StatusModel, ClientModel, JobModel>(sql, (job, status, client) =>
    {
        job.Status = status;
        job.Client = client;
        return job;
    }, splitOn: "StatusID,ClientID");   
}

You can read more here.