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