public IEnumerable <BookEM> GetBooks(BaseDataTableFilterEM filter, out int totalFiltered) { string spName = "USPGetBooks"; var order = filter.Order.First(); var column = filter.Columns[order.Column]; DynamicParameters sqlParams = new DynamicParameters(); sqlParams.Add("OrderBy", column.Name); sqlParams.Add("Direction", order.Dir); sqlParams.Add("Start", filter.Start); sqlParams.Add("Length", filter.Length); sqlParams.Add("TotalFiltered", DbType.Int32, direction: ParameterDirection.Output); using (IDbConnection db = new SqlConnection(base.CurrentContext.DbConnection)) { var books = db.Query <BookEM, AuthorEM, BookEM>(spName, (book, author) => { if (author != null) { book.Authors.Add(author); } else { book.Authors = new List <AuthorEM>(); } return(book); }, sqlParams, null, true, splitOn: "AuthorId", null, CommandType.StoredProcedure); var result = books.GroupBy(b => b.BookId).Select(g => { var groupedPost = g.First(); if (groupedPost.Authors.Any()) { groupedPost.Authors = g.Select(a => a.Authors.Single()).ToList(); } return(groupedPost); }).ToList(); totalFiltered = sqlParams.Get <int?>("@TotalFiltered") ?? 0; return(result); } }
public IEnumerable <AuthorEM> GetAuthors(BaseDataTableFilterEM filter, out int totalFiltered) { string spName = "USPGetAuthors"; var order = filter.Order.First(); var column = filter.Columns[order.Column]; DynamicParameters sqlParams = new DynamicParameters(); sqlParams.Add("OrderBy", column.Name); sqlParams.Add("Direction", order.Dir); sqlParams.Add("Start", filter.Start); sqlParams.Add("Length", filter.Length); sqlParams.Add("TotalFiltered", DbType.Int32, direction: ParameterDirection.Output); using (IDbConnection db = new SqlConnection(base.CurrentContext.DbConnection)) { var result = db.Query <AuthorEM>(spName, sqlParams, null, true, null, CommandType.StoredProcedure); totalFiltered = sqlParams.Get <int?>("@TotalFiltered") ?? 0; return(result); } }