/// <summary> /// Handles generic DataTablesRequest for the TSource Database using Entity Framework and LINQ-to-SQL /// </summary> /// <param name="request">DataTables Ajax Request</param> /// <returns>DataTablesResponse for TSource data</returns> public static async Task <DataTablesResponse <TSource> > DataTablesRequestAsync <TSource>( DataTablesRequest request, DbSet <TSource> baseDbSet) where TSource : class { // prepare the data and count queries var totalQuery = baseDbSet; var filterQuery = totalQuery.WhereDataTables(request); var dataQuery = filterQuery.OrderByDataTables(request).Skip(request.Start).Take(request.Length); // run the queries and return the response return(new DataTablesResponse <TSource>() { Draw = request.Draw, RecordsTotal = totalQuery.Count(), RecordsFiltered = filterQuery.Count(), Data = await dataQuery.ToListAsync(), Error = request.Error, }); }
/// <summary> /// Applies DataTables search filtering to the LINQ-to-SQL query. /// </summary> /// <typeparam name="TSource">The type of the elements of source.</typeparam> /// <param name="source">An System.Linq.IQueryable to filter.</param> /// <param name="request">A DataTablesRequest containing the search condition.</param> /// <returns>An System.Linq.IQueryable containing the filter predicate to satisfy the condition.</returns> /// <remarks> /// This method builds and applies an expression tree predicate using iQueryable.Where() to search /// each of the searchable columns (TSource properties) named in the DataTablesRequest for the search value. /// Expression trees are used (instead of anonymous methods) allowing IQueryable LINQ-to-SQL to translate our /// predicate into the respective SQL statements. Using anonymous methods, for example: IQueryable.Where(s => /// s.column1.Contains(value) || s.column2.Contains(value) ...) would result in IEnumerable LINQ-to-Objects /// reading the entire DbSet into memory before applying the filter. Using expression trees is more efficient /// since the filter may be applied prior to reading records from the DbSet. /// </remarks> public static IQueryable <TSource> WhereDataTables <TSource>( this IQueryable <TSource> source, DataTablesRequest request) { var result = source; if (string.IsNullOrWhiteSpace(request.Search?.Value) == false) { // foreach searchable column name in the DataTablesRequest, build a where-clause // predicate searching each property of TSource for the search value. // IQueryable.Where(s => s.Column1.Contains(Value) || s.Column2.Contains(Value) ...) // => WHERE [Column1] LIKE "%Value%" OR [Column2] LIKE "%Value%" ... ParameterExpression parameter = Expression.Parameter(typeof(TSource), "s"); MethodInfo contains = typeof(string).GetMethod("Contains", new[] { typeof(string) }); Expression value = Expression.Constant(request.Search.Value, typeof(string)); Expression predicate = null; foreach (var propertyName in request.Columns.Where(c => c.Searchable).Select(c => c.Name)) { try { Expression property = Expression.Property(parameter, propertyName); Expression containsExp = Expression.Call(property, contains, value); predicate = (predicate == null) ? containsExp : Expression.OrElse(predicate, containsExp); } catch (Exception ex) { request.Error += string.Format("{0}: {1}\n{2}", ex.GetType().Name, ex.Message.TrimEnd('.'), ex.StackTrace); return(source); } } result = Queryable.Where(source, Expression.Lambda <Func <TSource, bool> >(predicate, parameter)); } return(result); }
/// <summary> /// Sorts the elements of a sequence using DataTables order criteria. /// </summary> /// <typeparam name="TSource">The type of the elements of source.</typeparam> /// <param name="source">A sequence of values to order.</param> /// <param name="request">A DataTablesRequest containing the sorting criteria.</param> /// <returns>An System.Linq.IOrderedQueryable whose elements are sorted according to criteria.</returns> /// <remarks> /// This method applies one or more sorting criteria using Order items from DataTablesRequest. /// </remarks> public static IOrderedQueryable <TSource> OrderByDataTables <TSource>(this IQueryable <TSource> source, DataTablesRequest request) { if (request.Order.Count > 0) { // foreach column order criteria in the DataTablesRequest, apply element sorting. // IQueryable.OrderByDescending(s => s.Column1).ThenBy(s => s.Column2).ThenByDescending(s => s.Column3) ... // => ORDER BY [Column1] DESC, [Column2], [Column3] DESC ... var expression = source.Expression; var parameter = Expression.Parameter(typeof(TSource), string.Empty); var method = string.Empty; foreach (var order in request.Order) { try { method = string.IsNullOrEmpty(method) ? "OrderBy" : "ThenBy"; method += (order.Descending ? "Descending" : string.Empty); var property = Expression.PropertyOrField(parameter, request.Columns[order.Column].Name); var keyType = new[] { typeof(TSource), property.Type }; var keyName = Expression.Quote(Expression.Lambda(property, parameter)); expression = Expression.Call(typeof(Queryable), method, keyType, expression, keyName); } catch (Exception ex) { request.Error += string.Format("{0}: {1}\n{2}", ex.GetType().Name, ex.Message.TrimEnd('.'), ex.StackTrace); return((IOrderedQueryable <TSource>)source); } } return((IOrderedQueryable <TSource>)source.Provider.CreateQuery <TSource>(expression)); } return((IOrderedQueryable <TSource>)source); }
/// <summary> /// Handles the DataTablesRequest for the Customer Database /// </summary> /// <param name="request">DataTables Ajax Request</param> /// <param name="dbContext">CustomerContext for Customer data</param> /// <returns>DataTablesResponse for Customer</returns> public static async Task <DataTablesResponse <Customer> > DataTablesCustomerRequestAsync(DataTablesRequest request, CustomerContext dbContext) { var dbset = dbContext.Customers; return(await DataTablesRequestAsync(request, dbset)); }