Extending Query to more than 7 types

Question

Extending Query to more than 7 types

c# dapper generics mapping sql

Question

I am using Dapper to fetch a list of results. I have a lot of "value"-tables that only contains ID and a name.

I have this SQL;

SELECT 
    MedicationScheduleItem.*,
    MedicationSchedule.*,
    Patient.*,
    Medication.*,
    MedicationBrand.*,
    MedicationScheduleItemRepeat.*,
    MedicationType.*,
    Measure.*

FROM MedicationScheduleItem
JOIN MedicationSchedule ON (MedicationSchedule.Id = MedicationScheduleItem.MedicationScheduleId)
JOIN Patient ON (Patient.Id = MedicationSchedule.PatientId)
JOIN Medication ON (Medication.Id = MedicationScheduleItem.MedicationId)
JOIN MedicationBrand ON (MedicationBrand.Id = Medication.MedicationBrandId)
JOIN MedicationType ON (MedicationType.Id = Medication.MedicationTypeId)
JOIN Measure ON (Measure.Id = Medication.MeasureId)
LEFT JOIN MedicationScheduleItemRepeat ON (MedicationScheduleItemRepeat.MedicationScheduleItemId = MedicationScheduleItem.Id)

and I am trying to map that SQL to a model like this;

using Dapper;

//... 

private readonly IDbConnection _connection;

//... 

private IEnumerable<MedicationScheduleResultModel> QueryMedicationScheduleResultModels(string sql, object parameters = null)
{
    var results = new Dictionary<int, MedicationScheduleResultModel>();

    _connection.Query<MedicationScheduleItem, MedicationSchedule, Patient, Medication, MedicationBrand, MedicationScheduleItemRepeat, MedicationType, Measure, MedicationScheduleResultModel>(sql,
                (medicationScheduleItem, medicationSchedule, patient, medication, medicationBrand, medicationScheduleItemRepeat, medicationType, measure) =>
                {
                    MedicationScheduleResultModel viewModel;
                    if (!results.TryGetValue(medicationSchedule.Id, out viewModel))
                    {
                        results.Add(medicationSchedule.Id, new MedicationScheduleResultModel
                        {
                            Id = medicationSchedule.Id,
                            Patient = patient,
                            TimeCreated = medicationSchedule.TimeCreated,
                            StartDate = medicationSchedule.StartDate,
                            Schedules = new List<MedicationScheduleItemResultModel>()
                            {
                                BuildMedicationScheduleItemResultModel(medicationScheduleItem,
                                    medicationScheduleItemRepeat, medication, medicationBrand, medicationType, measure)
                            }
                        });
                    }
                    else
                    {
                        viewModel.Schedules.Add(BuildMedicationScheduleItemResultModel(medicationScheduleItem,
                            medicationScheduleItemRepeat, medication, medicationBrand));
                    }

                    return viewModel;
                }, parameters, splitOn: "Id, MedicationScheduleItemId");

    return results.Values;
}

private static MedicationScheduleItemResultModel BuildMedicationScheduleItemResultModel(MedicationScheduleItem medicationScheduleItem, MedicationScheduleItemRepeat medicationScheduleItemRepeat, Medication medication, MedicationBrand medicationBrand, MedicationType medicationType, Measure measure)
{
    return new MedicationScheduleItemResultModel()
    {
        Id = medicationScheduleItem.Id,
        ExecuteTime = medicationScheduleItem.ExecuteTime,
        RepeatTimeSpan = medicationScheduleItemRepeat != null
            ? (TimeSpan?)TimeSpan.FromTicks(medicationScheduleItemRepeat.RepeatTimeSpan)
            : null,
        Medication = new MedicationResultModel()
        {
            Id = medication.Id,
            Name = medication.Name,
            Brand = new MedicationBrand()
            {
                Id = medicationBrand.Id,
                Name = medicationBrand.Name
            },
            Measure = measure.Name,
            Type = medicationType.Name,
            Weight = medication.Weight
        }
    };
}

But the _connection.Query<T, ..> fails because there is to many generics types.

Is there a way to get this to work, or am I doing it wrong?

TryingToImprove

View more on Stack Overflow

Accepted Answer

Instead of mapping your SQL result to all those types only to use them in initializing another type, you could try to map directly to the eventual type you are interested in.

So instead of this:

_connection.Query<MedicationScheduleItem, MedicationSchedule, Patient, 
    Medication, MedicationBrand, MedicationScheduleItemRepeat, MedicationType,
    Measure, MedicationScheduleResultModel>(...

this might be sufficient to build the object graph you need:

_connection.Query<MedicationScheduleResultModel, Patient, 
    MedicationScheduleItemResultModel, MedicationResultModel, MedicationBrand>(...

Of course, this would require modifying your SELECT in order to return the appropriate fields. Something like...

SELECT
    -- columns that directly map to MedicationScheduleResultModel properties
    MedicationSchedule.id,
    MedicationSchedule.time_created TimeCreated, 
    MedicationSchedule.start_date StartDate,

    -- columns that directly map to Patient properties
    -- (to be used as MedicationScheduleResultModel.Patient)
    Patient.id, 
    Patient.name,

    -- columns that directly map to MedicationScheduleItemResultModel properties
    MedicationScheduleItem.id,
    MedicationScheduleItem.execute_time ExecuteTime,
    MedicationScheduleItemRepeat.repeat_time RepeatTime,

    -- columns that directly map to MedicationResultModel properties
    Medication.id,
    Medication.name,
    Measure.name,
    MedicationType.name,
    Medication.weight,

    -- columns that directly map to MedicationBrand properties
    MedicationBrand.id, 
    MedicationBrand.name
...

Hope you get the idea.

You might even go further and flatten, for example, the MedicationResultModel so that instead of containing a complex MedicationBrand property, it contains MedicationBrandId and MedicationBrandName properties. But that depends on whether this is appropriate for the rest of your design or not.

Extending Query to more than 7 types

Question

Accepted Answer

Performance

Expression Evaluator