public Indent GetById(int id)
{
string sqlQuery = @"SELECT * FROM Indent
JOIN Customer ON Customer.CustomerId = Indent.CustomerId
LEFT JOIN Executor AS A ON A.ExecutorId = Indent.ExecutorId
LEFT JOIN Responce ON Responce.IndentId = Indent.IndentId
LEFT JOIN Recall ON Recall.RecallId = Indent.IndentId
LEFT JOIN Executor AS B ON B.ExecutorId = Responce.ExecutorId
WHERE Indent.IndentId = @IndentId" ;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
var indents = new Dictionary <int, Indent>();
connection.Query <Indent, Customer, Executor, Responce, Recall, Executor, Indent>(sqlQuery, (Indent, Customer, ExecutorForIndent,
Responce, Recall, ExecutorForResponce) =>
{
Indent indentEntry;
if (!indents.TryGetValue(Indent.IndentId, out indentEntry))
{
indentEntry = Indent;
indentEntry.Customer = new Customer();
indentEntry.Executor = new Executor();
indentEntry.Responces = new HashSet <Responce>();
indentEntry.Recall = new Recall();
indents.Add(indentEntry.IndentId, indentEntry);
}
indentEntry.Customer = Customer;
indentEntry.Executor = ExecutorForIndent;
indentEntry.Recall = Recall;
ResponceComparer comparerResponce = new ResponceComparer();
if (Responce != null && !indentEntry.Responces.Contains(Responce, comparerResponce))
{
Responce.Executor = ExecutorForResponce;
indentEntry.Responces.Add(Responce);
}
return(indentEntry);
},
new { IndentId = id },
splitOn: "CustomerId, ExecutorId, ResponceId, RecallId, ExecutorId"
).Distinct();
Indent indent = new Indent();
indent = indents.FirstOrDefault().Value;
return(indent);
}
}
public async Task <IEnumerable <Indent> > GetIndentsWithExecutorResponce(int executorId)
{
string sqlQuery = @" SELECT * FROM Indent
LEFT JOIN Executor ON Executor.ExecutorId = Indent.ExecutorId
JOIN Responce ON Responce.IndentId = Indent.IndentId
WHERE Responce.ExecutorId = @ExecutorId ORDER BY ResponceId DESC";
using (SqlConnection connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
var indents = new Dictionary <int, Indent>();
await connection.QueryAsync <Indent, Executor, Responce, Indent>(sqlQuery, (Indent, Executor, Responce) =>
{
Indent indentEntry;
if (!indents.TryGetValue(Indent.IndentId, out indentEntry))
{
indentEntry = Indent;
indentEntry.Executor = new Executor();
indentEntry.Responces = new HashSet <Responce>();
indents.Add(indentEntry.IndentId, indentEntry);
}
indentEntry.Executor = Executor;
ResponceComparer comparerResponce = new ResponceComparer();
if (Responce != null && !indentEntry.Responces.Contains(Responce, comparerResponce))
{
indentEntry.Responces.Add(Responce);
}
return(indentEntry);
},
new { ExecutorId = executorId },
splitOn : "IndentId, ExecutorId, ResponceId");
return(indents.Values);
}
}
