public async Task <int> UpdateManyAsync(IEnumerable <Category> categories)
{
if (categories == null)
{
return(0);
}
var updated = 0;
var comparer = new CategoryComparer();
foreach (var category in categories)
{
var original = await _categoryRepository.GetAsync(category.Id);
if (original == null || comparer.Equals(original, category))
{
continue;
}
await _categoryRepository.UpdateAsync(category);
updated++;
}
return(updated);
}
public async Task <IEnumerable <Executor> > SearchInNames(string searchString)
{
string sqlQuery = @" SELECT * FROM Executor
JOIN CategoryExecutor ON CategoryExecutor.ExecutorId = Executor.ExecutorId
WHERE Executor.FirstName = @Name OR Executor.Surname = @Name OR Executor.Patronymic = @Name";
Dictionary <int, Executor> executors = new Dictionary <int, Executor>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
await connection.QueryAsync <Executor, CategoryExecutor, Executor> (sqlQuery, (Executor, CategoryExecutor) =>
{
Executor executor;
if (!executors.TryGetValue(Executor.ExecutorId, out executor))
{
executor = Executor;
executor.Categories = new HashSet <CategoryExecutor>();
executors.Add(executor.ExecutorId, executor);
}
CategoryComparer comparerCategory = new CategoryComparer();
if (CategoryExecutor != null && !executor.Categories.Contains(CategoryExecutor, comparerCategory))
{
executor.Categories.Add(CategoryExecutor);
}
return(executor);
},
new { Name = searchString },
splitOn : "ExecutorId, Id");
}
return(executors.Values.ToList());
}
public Executor GetById(int id)
{
string sqlQuery = @"SELECT * FROM Executor
LEFT JOIN CategoryExecutor ON CategoryExecutor.ExecutorId = Executor.ExecutorId
WHERE Executor.ExecutorId = @Id AND Executor.EmailConfirmed = 1";
using (SqlConnection connection = new SqlConnection(_connectionString))
{
var executorDictionary = new Dictionary <int, Executor>();
connection.Query <Executor, CategoryExecutor, Executor>(sqlQuery, (Executor, CategoryExecutor) =>
{
Executor executor;
if (!executorDictionary.TryGetValue(Executor.ExecutorId, out executor))
{
executor = Executor;
executor.Categories = new HashSet <CategoryExecutor>();
executorDictionary.Add(executor.ExecutorId, executor);
}
CategoryComparer comparerCategory = new CategoryComparer();
if (CategoryExecutor != null && !executor.Categories.Contains(CategoryExecutor, comparerCategory))
{
executor.Categories.Add(CategoryExecutor);
}
return(executor);
},
new { Id = id },
splitOn: "Id"
);
Executor finalExecutor = new Executor();
executorDictionary.TryGetValue(id, out finalExecutor);
return(finalExecutor);
}
}
public Executor GetProfile(int id)
{
string sqlQuery = @"SELECT * FROM Executor
LEFT JOIN Indent ON Indent.ExecutorId = Executor.ExecutorId
LEFT JOIN Recall ON Recall.RecallId = Indent.IndentId
LEFT JOIN Customer ON Customer.CustomerId = Indent.CustomerId
LEFT JOIN CategoryExecutor ON CategoryExecutor.ExecutorId = Executor.ExecutorId
WHERE Executor.ExecutorId = @ExecutorId" ;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
var executors = new Dictionary <int, Executor>();
connection.Query <Executor, Indent, Recall, Customer, CategoryExecutor, Executor>(sqlQuery, (Executor, Indent, Recall,
Customer, Category) =>
{
Executor executorEntry;
if (!executors.TryGetValue(Executor.ExecutorId, out executorEntry))
{
executorEntry = Executor;
executorEntry.Indents = new HashSet <Indent>();
executorEntry.Categories = new HashSet <CategoryExecutor>();
executors.Add(executorEntry.ExecutorId, executorEntry);
}
CategoryComparer comparerCategory = new CategoryComparer();
if (Category != null && !executorEntry.Categories.Contains(Category, comparerCategory))
{
executorEntry.Categories.Add(Category);
}
IndentComparer comparerIndent = new IndentComparer();
if (Indent != null && !executorEntry.Indents.Contains(Indent, comparerIndent))
{
if (Customer != null)
{
Indent.Customer = new Customer();
Indent.Customer = Customer;
}
if (Recall != null)
{
if (Recall.CustomerCommentForExecutor != null)
{
Indent.Recall = new Recall();
Indent.Recall = Recall;
}
}
executorEntry.Indents.Add(Indent);
}
return(executorEntry);
},
new { ExecutorId = id },
splitOn: "ExecutorId, IndentId, RecallId, CustomerId, Id"
).Distinct();
Executor executor = new Executor();
executor = executors.FirstOrDefault().Value;
return(executor);
}
}
