查询到一个复杂的对象,具有小巧玲珑
问题描述:
我有一个Customer类具有以下属性:查询到一个复杂的对象,具有小巧玲珑
public int Id { get; set; }
public string Name { get; set; }
public int AddressId { get; set; }
public Address Address { get; set; }
我的目标是编写将使用一个内连接到每个客户内填充整个地址属性短小精悍查询这是返回。
这里是我有什么,它是工作,但我想知道如果这是这样做的干净/简单的方法:我有添加其它财产,如有些担心
StringBuilder sql = new StringBuilder();
using (var conn = GetOpenConnection())
{
sql.AppendLine("SELECT c.Id, c.Name, c.AddressId, a.Address1, a.Address2, a.City, a.State, a.ZipCode ");
sql.AppendLine("FROM Customer c ");
sql.AppendLine("INNER JOIN Address a ON c.AddressId = a.Id ");
return conn.Query<Customer, Address, Customer>(
sql.ToString(),
(customer, address) => {
customer.Address= address;
return userRole;
},
splitOn: "AddressId"
).ToList();
}
:
public Contact Contact { get; set; }
我不知道如何切换上面的语法来填充地址和联系人。
答
我使用的版本小巧玲珑的1.40版本,我写这篇文章的方式,没有问题填充不止一个对象,但我得到了使用填充的Maximo表是八
public class Customer {
public int Id { get; set; }
public string Name { get; set; }
public int AddressId { get; set; }
public int ContactId { get; set; }
public Address Address { get; set; }
public Contact Contact { get; set; }
}
public class Address {
public int Id { get; set; }
public string Address1 {get;set;}
public string Address2 {get;set;}
public string City {get;set;}
public string State {get;set;}
public int ZipCode {get;set;}
public IEnumerable<Customer> Customer {get;set;}
}
public class Contact {
public int Id { get; set; }
public string Name { get; set; }
public IEnumerable<Customer> Customer {get;set;}
}
using (var conn = GetOpenConnection())
{
var query = _contextDapper
.Query<Customer, Address, Contact, Customer>([email protected]"
SELECT c.Id, c.Name,
c.AddressId, a.Id, a.Address1, a.Address2, a.City, a.State, a.ZipCode,
c.ContactId, ct.Id, ct.Name
FROM Customer c
INNER JOIN Address a ON a.Id = c.AddressId
INNER JOIN Contact ct ON ct.Id = c.ContactId",
(c, a, ct) =>
{
c.LogType = a;
c.Contact = ct;
return c;
}, splitOn: "AddressId, ContactId")
.AsQueryable();
return query.ToList();
}
+0
帮了我很多:P谢谢! :)我也在这里用我的例子 –
答
采取看看我的例子与一个大的查询,请注意,每个查询线这是一个不同的对象。
public List<Appointment> GetList(int id)
{
List<Appointment> ret;
using (var db = new SqlConnection(connstring))
{
const string sql = @"SELECT AP.[Id], AP.Diagnostics, AP.Sintomns, AP.Prescription, AP.DoctorReport, AP.AddressId,
AD.Id, AD.Street, AD.City, AD.State, AD.Country, AD.ZIP, Ad.Complement,
D.Id, D.Bio, d.CRMNumber, D.CRMNumber, D.CRMState,
P.Id,
S.Id, S.Name,
MR.Id, MR.Alergies, MR.BloodType, MR.DtRegister, Mr.HealthyProblems, MR.HealthyProblems, MR.Height, MR.MedicalInsuranceNumber, MR.MedicalInsuranceUserName, MR.Medications, MR.Weight,
MI.Id, MI.Name
from Appointment AP
inner join [Address] AD on AD.Id = AP.AddressId
inner join Doctor D on D.Id = AP.DoctorId
inner join Patient P on P.Id = AP.PatientId
left join Speciality S on S.Id = D.IDEspeciality
left join MedicalRecord MR on MR.Id = P.MedicalRecordId
left join MedicalInsurance MI on MI.Id = MR.MedicalInsuranceId
where AP.Id = @Id
order by AP.Id desc";
ret = db.Query<Appointment, Address, Doctor, Patient, Speciality, MedicalRecord, MedicalInsurance, Appointment>(sql,
(appointment, address, doctor, patient, speciality, medicalrecord, medicalinsurance) =>
{
appointment.Address = address;
appointment.Doctor = doctor;
appointment.Patient = patient;
appointment.Doctor.Speciality = speciality;
appointment.Patient.MedicalRecord = medicalrecord;
appointment.Patient.MedicalRecord.MedicalInsurance = medicalinsurance;
return appointment;
}, new { Id = id }, splitOn: "Id, Id, Id, Id, Id, Id").ToList();
}
return ret;
}
诀窍是让分割的列具有相同的名称(如'Id'),然后它只是'Query'。 –
juharr