public async Task <DataTablesResponseModel> PopulateTable <T>(DataTablesRequestModel request, DbSet <T> data, Func <T, T> project) where T : class { // build select var sql = new StringBuilder(); sql.AppendFormat("SELECT * FROM {0} ", GetTableName(data)); // build where if (!string.IsNullOrEmpty(request.Search.Value)) { sql.Append("WHERE "); foreach (var column in request.Columns) { if (!column.Searchable) { continue; } sql.AppendFormat("{0} LIKE '%{1}%' OR", column.Name, request.Search.Value); } sql.Remove(sql.Length - 2, 2); } // count filtered data var countSql = "SELECT COUNT(*) " + sql.ToString(9, sql.Length - 9); var filterCount = ExecuteScalar(data, countSql); // build order by if (request.Ordering.Count > 0) { sql.Append("ORDER BY "); foreach (var order in request.Ordering) { sql.AppendFormat("{0} {1}, ", request.Columns[order.Column].Name, order.Direction == DataTablesOrdering.Ascending ? "ASC" : "DESC"); } sql.Remove(sql.Length - 2, 2); sql.Append(' '); } // build paging sql.AppendFormat("LIMIT {0} OFFSET {1}", request.Length, request.Start); var sqlString = sql.ToString(); var recordset = await data.FromSqlRaw(sqlString).ToListAsync(); if (project != null) { recordset = recordset.Select(x => project(x)).ToList(); } return(new DataTablesResponseModel { Draw = request.Draw + 1, Data = recordset, RecordsFiltered = filterCount, RecordsTotal = await data.CountAsync() }); }
public Task <DataTablesResponseModel> PopulateTable <T>(DataTablesRequestModel request, DbSet <T> data) where T : class { return(PopulateTable(request, data, null)); }