public void SimpleFilter() { var dataSource = _context.Things.Where(x => x.Id > 95); var data = dataSource.Take(PageSize).ToList(); var request = new GridDataRequest() { Take = PageSize, Skip = 0, Search = new Filter(), Columns = Thing.GetColumnsWithIdFilter() }; var response = request.CreateGridDataResponse(dataSource); Assert.AreEqual(data.Count, response.Payload.Count, "Same length"); Assert.AreEqual(dataSource.Count(), response.FilteredRecordCount, "Total filtered rows matching"); }
public void SimpleList() { var dataSource = _context.Things; var data = dataSource.Take(PageSize).ToList(); Assert.AreEqual(PageSize, data.Count, "Set has 10 items"); var request = new GridDataRequest() { Take = PageSize, Skip = 0, Search = new Filter(), Columns = Thing.GetColumns() }; var response = request.CreateGridDataResponse(dataSource); Assert.AreEqual(data.Count, response.Payload.Count, "Same length"); Assert.AreEqual(data.First().Id, response.Payload.First().First(), "Same first item"); Assert.AreEqual(dataSource.Count(), response.TotalRecordCount, "Total rows matching"); }
public void SimpleSearch() { var dataSource = _context.Things; var data = dataSource.Where(x => x.Name.Contains(SearchText)).Take(PageSize).ToList(); var request = new GridDataRequest() { Take = PageSize, Skip = 0, Search = new Filter() { Operator = CompareOperators.Auto, Text = SearchText }, Columns = Thing.GetColumns() }; var response = request.CreateGridDataResponse(dataSource); Assert.AreEqual(data.Count, response.Payload.Count, "Same length"); Assert.AreEqual(data.First().Id, response.Payload.First().First(), "Same first item"); Assert.AreEqual(dataSource.Count(x => x.Name.Contains(SearchText)), response.FilteredRecordCount, "Total filtered rows matching"); }
public void TestSimpleAggregate() { var dataSource = _context.Things; var data = dataSource.Take(PageSize).ToList(); Assert.AreEqual(PageSize, data.Count, "Set has 10 items"); var request = new GridDataRequest() { Take = PageSize, Skip = 0, Search = new Filter(), Columns = Thing.GetColumnsWithAggregate() }; var response = request.CreateGridDataResponse(dataSource); Assert.AreEqual(data.Count, response.Payload.Count, "Same length"); Assert.AreEqual(data.First().Id, response.Payload.First().First(), "Same first item"); Assert.AreEqual(dataSource.Sum(x => x.Number), (double)response.AggregationPayload["Number"], "Same average number"); Assert.AreEqual(dataSource.Sum(x => x.DecimalNumber), (decimal)response.AggregationPayload["DecimalNumber"], "Same average decimal number"); }
public void TestMultipleAggregate() { var dataSource = _context.Things; var data = dataSource.Take(PageSize).ToList(); Assert.AreEqual(PageSize, data.Count, "Set has 10 items"); var request = new GridDataRequest() { Take = PageSize, Skip = 0, Search = new Filter(), Columns = Thing.GetColumnsWithMultipleCounts() }; var response = request.CreateGridDataResponse(dataSource); Assert.AreEqual(data.Count, response.Payload.Count, "Same length"); Assert.AreEqual(data.First().Id, response.Payload.First().First(), "Same first item"); Assert.AreEqual(dataSource.Select(x => x.Id).Distinct().Count(), (int)response.AggregationPayload["Id"], "Id same distinct count"); Assert.AreEqual(dataSource.Select(x => x.Number).Distinct().Count(), (int)response.AggregationPayload["Number"], "Number same distinct count"); Assert.AreEqual(dataSource.Select(x => x.DecimalNumber).Distinct().Count(), (int)response.AggregationPayload["DecimalNumber"], "DecimalNumber same distinct count"); Assert.AreEqual(dataSource.Select(x => x.Name).Distinct().Count(), (int)response.AggregationPayload["Name"], "Name same distinct count"); Assert.AreEqual(dataSource.Select(x => x.Date).Distinct().Count(), (int)response.AggregationPayload["Date"], "Date same distinct count"); Assert.AreEqual(dataSource.Select(x => x.Bool).Distinct().Count(), (int)response.AggregationPayload["Bool"], "Bool same distinct count"); }
public void TestListSimpleSearch() { var dataSource = new List<Thing> { new Thing { Name= SearchText + "1" }, new Thing { Name= SearchText.ToLower() + "2" }, new Thing { Name= SearchText.ToUpper() + "3" }, new Thing { Name= SearchText + "4" }, new Thing { Name= "ODOR" } }; var data = dataSource.Where(x => x.Name.ToLowerInvariant().Contains(SearchText.ToLowerInvariant())).Take(PageSize).ToList(); var request = new GridDataRequest() { Take = PageSize, Skip = 0, Search = new Filter() { Operator = CompareOperators.Auto, Text = SearchText }, Columns = Thing.GetColumns() }; var response = request.CreateGridDataResponse(dataSource.AsQueryable()); Assert.AreEqual(data.Count, response.Payload.Count, "Same length"); Assert.AreEqual(data.First().Id, response.Payload.First().First(), "Same first item"); Assert.AreEqual(dataSource.Count(x => x.Name.ToLowerInvariant().Contains(SearchText.ToLowerInvariant())), response.FilteredRecordCount, "Total filtered rows matching"); }
private static IQueryable FilterResponse(GridDataRequest request, IQueryable subset, GridDataResponse response) { // Perform Searching var searchLambda = new StringBuilder(); var searchParamArgs = new List<object>(); switch (request.Search.Operator) { case CompareOperators.Auto: foreach (var column in request.Columns.Where(x => x.Searchable)) { searchLambda.AppendFormat("{0}.Contains(@{1}) ||", column.Name, searchParamArgs.Count); searchParamArgs.Add(request.Search.Text); } break; } // Perform Filtering foreach (var column in request.Columns.Where(x => x.Filter != null)) { if (String.IsNullOrWhiteSpace(column.Filter.Text) && column.Filter.Argument == null) continue; // TODO: Handle null? switch (column.Filter.Operator) { case CompareOperators.Equals: case CompareOperators.NotEquals: if (String.IsNullOrWhiteSpace(column.Filter.Text)) continue; // TODO: This code is for use with Dynamic LINQ, but It needs EF //if (column.DataType == DataType.Date.ToString().ToLower()) // searchLambda.AppendFormat("EntityFunctions.TruncateTime({0}) == @{1} &&", column.Name, searchParamArgs.Count); //else searchLambda.AppendFormat("{0} {2} @{1} &&", column.Name, searchParamArgs.Count, GetSqlOperator(column.Filter.Operator)); if (String.Equals(column.DataType, DataType.Numeric.ToString(), StringComparison.CurrentCultureIgnoreCase)) { searchParamArgs.Add(Decimal.Parse(column.Filter.Text)); } else if ( String.Equals(column.DataType, DataType.DateTime.ToString(), StringComparison.CurrentCultureIgnoreCase) || String.Equals(column.DataType, DataType.Date.ToString(), StringComparison.CurrentCultureIgnoreCase)) { searchParamArgs.Add(DateTime.Parse(column.Filter.Text)); } else if (String.Equals(column.DataType, DataType.Boolean.ToString(), StringComparison.CurrentCultureIgnoreCase)) { searchParamArgs.Add(Boolean.Parse(column.Filter.Text)); } else { searchParamArgs.Add(column.Filter.Text); } break; case CompareOperators.Contains: searchLambda.AppendFormat("{0}.Contains(@{1}) &&", column.Name, searchParamArgs.Count); searchParamArgs.Add(column.Filter.Text); break; case CompareOperators.StartsWith: searchLambda.AppendFormat("{0}.StartsWith(@{1}) &&", column.Name, searchParamArgs.Count); searchParamArgs.Add(column.Filter.Text); break; case CompareOperators.EndsWith: searchLambda.AppendFormat("{0}.EndsWith(@{1}) &&", column.Name, searchParamArgs.Count); searchParamArgs.Add(column.Filter.Text); break; case CompareOperators.Gte: case CompareOperators.Gt: case CompareOperators.Lte: case CompareOperators.Lt: searchLambda.AppendFormat("{0} {2} @{1} &&", column.Name, searchParamArgs.Count, GetSqlOperator(column.Filter.Operator)); if (String.Equals(column.DataType, DataType.Numeric.ToString(), StringComparison.CurrentCultureIgnoreCase)) searchParamArgs.Add(decimal.Parse(column.Filter.Text)); else searchParamArgs.Add(DateTime.Parse(column.Filter.Text)); break; case CompareOperators.Multiple: if (column.Filter.Argument == null || column.Filter.Argument.Length == 0) continue; var filterString = "("; foreach (var filter in column.Filter.Argument) { filterString += String.Format("{0} == @{1} ||", column.Name, searchParamArgs.Count); searchParamArgs.Add(filter); } if (filterString.Length == 1) continue; searchLambda.AppendFormat(filterString.Remove(filterString.Length - 3, 3) + ") &&"); break; case CompareOperators.Between: if (column.Filter.Argument == null || column.Filter.Argument.Length == 0) continue; searchLambda.AppendFormat("(({0} >= @{1}) && ({0} <= @{2})) &&", column.Name, searchParamArgs.Count, searchParamArgs.Count + 1); if (String.Equals(column.DataType, DataType.Numeric.ToString(), StringComparison.CurrentCultureIgnoreCase)) { searchParamArgs.Add(decimal.Parse(column.Filter.Text)); searchParamArgs.Add(decimal.Parse(column.Filter.Argument[0])); } else { searchParamArgs.Add(DateTime.Parse(column.Filter.Text)); searchParamArgs.Add(DateTime.Parse(column.Filter.Argument[0])); } break; } } if (searchLambda.Length > 0) { subset = subset.Where(searchLambda.Remove(searchLambda.Length - 3, 3).ToString(), searchParamArgs.ToArray()); response.FilteredRecordCount = subset.Count(); } return subset; }