CODEWITHSUNDEEP
c#dapper
CodeMan03
CodeMan03

Reputation: 226

Dapper easiest and fastest way to map single columns to multiple classes?

I have a class that looks like this

    public Guid assessmentId { get; set; }
    public string? applicationNumber { get; set; }
    public ApplicationStatus? applicationStatus { get; set; }
    public List<CompanyInformationModel>? companyInformationModel { get; set; }
    public List<LocationInformationModel>? locationInformationModels { get; set; }
    public List<JobInformationModel>? jobInformationModel { get; set; }

public class CompanyInformationModel
{
        public Guid CompanyInformationId { get; set; }
        public string? companyName { get; set; }
        public string? contactName { get; set; }
        public string? primaryPhone { get; set; }
        public string? secondaryPhone { get; set; }
        public string? email { get; set; }
        public string? hourlyRate { get; set; }
}

public class LocationInformationModel
{
        public Guid LocationInformationId { get; set; }
        public string? address1 { get; set; }
        public string? address2 { get; set; }
        public string? city { get; set; }
        public string? state { get; set; }
        public string? zip { get; set; }
}

public class JobInformationModel
{
        Guid JobInformationId { get; set; }
        string? jobTitle { get; set; }
        string? jobDescription { get; set; }
}

public enum ApplicationStatus
{
        [Description("Pending")]
        Pending,
        [Description("Approved")]
        Approved,
        [Description("Declined")]
        Declined
}

I query my table with joins like so in dapper

 var sqlStatement = $@"Select * from SituationalAssessment s 
                                  join CompanyInformation c  on c.CompanyInformationId = s.AssessmentId
                                  join LocationInformation l  on l.LocationInformationId = s.AssessmentId
                                  join JobInformation j  on j.JobInformationId = s.AssessmentId";
            var retVal= await _dapperHelper.ExecuteQueryAsync<AssessmentModel>(sqlStatement);

My results will look like this

AssessmentId    ApplicationNumber   ApplicationStatus   DateCreated ModifiedDateCreated CompanyInformationId    CompanyName ContactName PrimaryPhone    SecondaryPhone  Email   HourlyRate  LocationInformationId   Address1    Address2    City    State   Zip JobInformationId    JobTitle    JobDescription
78FE1852-5A26-4624-925C-74653DE9DAD9    227114  Pending 2023-02-08 13:57:52.743 NULL    78FE1852-5A26-4624-925C-74653DE9DAD9    Company Test    Test Contact    123-234-2343    NULL    [email protected] NULL    78FE1852-5A26-4624-925C-74653DE9DAD9    123 Main Ste    Apt 201 Los Angeles CA  90210   78FE1852-5A26-4624-925C-74653DE9DAD9    TEst    Test Description

How do I map the results of CompanyInformation, LocationInformation and JobInformation to their own classes automatically in dapper?

The columns in the database are the exact same names as the fields in my classes

Upvotes: 4

Views: 393

Answers (1)

Eldar
Eldar

Reputation: 10790

I assume that assessmentId, CompanyInformationId, LocationInformationId, and JobInformationId are the primary keys of the related tables. If it is so then your relationships between these tables should be 1 to 1 not 1 to many. And your assessment model should look like this :


public class AssesmentModel {

    public Guid assessmentId { get; set; }
    public string? applicationNumber { get; set; }
    public ApplicationStatus? applicationStatus { get; set; }
    public CompanyInformationModel? companyInformationModel { get; set; }
    public LocationInformationModel? locationInformationModel { get; set; }
    public JobInformationModel? jobInformationModel { get; set; }
}

Once we fix that then we can use the dappers splitOn parameter to split the query between the models.

var assessments = await connection.QueryAsync<AssesmentModel, CompanyInformationModel, LocationInformationModel, JobInformationModel>(sqlStatement, (assesment, company, location, job) => {
        assesment.companyInformationModel  = company;
        assesment.locationInformationModel = location;
        assesment.jobInformationModel = job;

        return assesment;
    }, splitOn: "CompanyInformationId, LocationInformationId, JobInformationId");

Need to mention that using explicit column names (instead of just *) in your query would be nice since dapper uses string.Split like operation to split the columns, changes to the related tables might break the query later on.

Edit

If you have somehow 1 to many relationships between those same method can be applied in this scenario like this :


var assessments = await connection.QueryAsync<AssesmentModel, CompanyInformationModel, LocationInformationModel, JobInformationModel>(sqlStatement, (assesment, company, location, job) => {
        assesment.companyInformationModels.Add(company);
        assesment.locationInformationModels.Add(location);
        assesment.jobInformationModels.Add(job);

        return assesment;
    }, splitOn: "CompanyInformationId, LocationInformationId, JobInformationId");

Upvotes: 2

Related Questions