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?
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.