public IEnumerable <Motor> GetAllMotors()
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionStrHelper.CnnVal("TestDB")))
{
var lookupMotors = new Dictionary <string, MotorRecord>();
var lookupParts = new Dictionary <string, Part>();
var allMotorsWithBomItems = connection.Query <MotorRecord, BOMItemRecord, Part, MotorRecord>
(@"select m.*, bi.*, p.* from Motors m
inner join BOMItems bi on m.MotorNumber = bi.MotorNumber
inner join Parts p on bi.PartNumber = p.PartNumber",
(m, bi, p) =>
{
MotorRecord motor;
Part part;
if (!lookupMotors.TryGetValue(m.MotorNumber, out motor))
{
lookupMotors.Add(m.MotorNumber, motor = m);
}
if (!lookupParts.TryGetValue(p.PartNumber, out part))
{
lookupParts.Add(p.PartNumber, part = p);
}
if (motor.BOM == null)
{
motor.BOM = new List <BOMItemRecord>();
}
bi.PartItem = part;
motor.BOM.Add(bi);
return(motor);
}, splitOn: "MotorNumber,PartNumber").AsQueryable();
var resultInRecords = lookupMotors.Values;
var result = MotorFactory.BulidMotor(resultInRecords);
return(result);
}
}
public Motor GetMotorByMotorNumber(string motorNumber)
{
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionStrHelper.CnnVal("TestDB")))
{
var queryResult = connection.Query <MotorRecord, BOMItemRecord, Part, MotorRecord>
(@"select m.*, bi.*, p.* from Motors m
inner join BOMItems bi on m.MotorNumber = bi.MotorNumber
inner join Parts p on bi.PartNumber = p.PartNumber
where m.MotorNumber = @motorNumber",
(m, bi, p) =>
{
if (m.BOM == null)
{
m.BOM = new List <BOMItemRecord>();
}
m.BOM.Add(bi);
return(m);
}, splitOn: "MotorNumber,PartNumber").AsQueryable();
var result = MotorFactory.BulidMotor(queryResult.FirstOrDefault(), queryResult.FirstOrDefault().BOM, queryResult.FirstOrDefault().BOM.Select(bi => bi.PartItem));
return(result);
}
}
