Exemple #1
0
        /// <summary>
        /// Generates a GridDataReponse using the GridDataRequest and an IQueryable source,
        /// like a DataSet in Entity Framework.
        /// </summary>
        /// <param name="request">The Tubular's grid request</param>
        /// <param name="dataSource">The IQueryable source</param>
        /// <returns></returns>
        public static GridDataResponse CreateGridDataResponse(this GridDataRequest request, IQueryable dataSource)
        {
            var response = new GridDataResponse
            {
                Counter = request.Counter,
                TotalRecordCount = dataSource.Count(),
                FilteredRecordCount = dataSource.Count()
            };

            var properties = ExtractProperties(dataSource.ElementType);
            var columnMap = MapColumnsToProperties(request.Columns, properties);

            var subset = FilterResponse(request, dataSource, response);

            // Perform Sorting
            var orderingExpression = string.Empty;
            foreach (var column in request.Columns.Where(x => x.SortOrder > 0).OrderBy(x => x.SortOrder))
            {
                orderingExpression += column.Name + " " +
                                      (column.SortDirection == SortDirection.Ascending ? "ASC" : "DESC") + ", ";
            }

            // Apply the sorting expression if supplied
            if (!string.IsNullOrWhiteSpace(orderingExpression))
            {
                subset = subset.OrderBy(orderingExpression.Substring(0, orderingExpression.Length - 2));
            }
            else
            {
                subset = subset.OrderBy(request.Columns.First().Name + " ASC");
            }

            subset = subset.Skip(request.Skip);
            var pageSize = request.Take;

            // Take with value -1 represents entire set
            if (request.Take == -1)
            {
                response.TotalPages = 1;
                response.CurrentPage = 1;
                pageSize = subset.Count();
            }
            else
            {
                subset = subset.Take(request.Take);
                response.TotalPages = (response.FilteredRecordCount + pageSize - 1)/pageSize;

                if (response.TotalPages > 0)
                {
                    response.CurrentPage = 1 +
                                           (int)
                                               ((request.Skip/(float) response.FilteredRecordCount)*response.TotalPages);
                }
            }

            response.Payload = CreateGridPayload(subset, columnMap, pageSize, request.TimezoneOffset);

            return response;
        }
Exemple #2
0
        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;
        }