public Customer GetProfile(int id) { string sqlQuery = @"SELECT * FROM Customer LEFT JOIN Indent ON Indent.CustomerId = Customer.CustomerId LEFT JOIN Recall ON Recall.RecallId = Indent.IndentId LEFT JOIN Executor ON Executor.ExecutorId = Indent.ExecutorId WHERE Customer.CustomerId = @CustomerId" ; using (SqlConnection connection = new SqlConnection(_connectionString)) { var customers = new Dictionary <int, Customer>(); connection.Query <Customer, Indent, Recall, Executor, Customer>(sqlQuery, (Customer, Indent, Recall, Executor) => { Customer customerEntry; if (!customers.TryGetValue(Customer.CustomerId, out customerEntry)) { customerEntry = Customer; customerEntry.Indents = new HashSet <Indent>(); customers.Add(customerEntry.CustomerId, customerEntry); } IndentComparer comparerIndent = new IndentComparer(); if (Indent != null && !customerEntry.Indents.Contains(Indent, comparerIndent)) { if (Executor != null) { Indent.Executor = new Executor(); Indent.Executor = Executor; } if (Recall != null) { if (Recall.ExecutorCommentForCustomer != null) { Indent.Recall = new Recall(); Indent.Recall = Recall; } } customerEntry.Indents.Add(Indent); } return(customerEntry); }, new { CustomerId = id }, splitOn: "CustomerId, IndentId, RecallId, ExecutorId" ).Distinct(); Customer customer = new Customer(); customer = customers.FirstOrDefault().Value; return(customer); } }
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); } }