검색어 확장 7 종 이상

c# dapper generics mapping sql

문제

Dapper를 사용하여 결과 목록을 가져옵니다. ID와 이름 만 포함하는 '가치'표가 많습니다.

나는이 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)

이 SQL을 이와 같은 모델에 매핑하려고합니다.

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
        }
    };
}

그러나 _connection.Query<T, ..> 는 많은 generics 유형이 있기 때문에 실패합니다.

이 작업을 수행 할 수있는 방법이 있습니까, 아니면 잘못하고 있습니까?

수락 된 답변

다른 유형을 초기화 할 때만 SQL 결과를 모든 유형에 맵핑하는 대신, 관심있는 최종 유형에 직접 맵핑 할 수 있습니다.

그래서 이것 대신 :

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

필요한 객체 그래프를 작성하기에 충분할 수 있습니다.

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

물론 적절한 필드를 반환하려면 SELECT 를 수정해야합니다. 뭔가 ..

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

당신이 아이디어를 얻기를 바랍니다.

당신은 심지어 예를 들어, 더 나아가 평평하게 할 수는 MedicationResultModel 대신 복잡한 포함의 너무 MedicationBrand 속성을, 포함 된 MedicationBrandIdMedicationBrandName 속성을. 그러나 이것이 나머지 설계에 적합한 지 아닌지에 달려 있습니다.



아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow