View on GitHub

QueryTables

A small library that enables the fast creation of GridViews with filter and multiple sorting features with Entity Framework support.

Download this project as a .zip file Download this project as a tar.gz file

QueryTables

QueryTables can:

You can find an example application in https://github.com/andreschort/query-sample

Demo

demo

TODO

QueryTables.Core

QueryTables.Web

Basic projection

Let's say you have this Employee class: 

public class Employee  
{  
  public string Name { get; set; }  
  public string LastName { get; set; }  
}

You create a Query object like this:

var query = new Query<Employee>();
query.AddField("FullName").Select(x => x.Name + " " + x.LastName); // FieldName = "FullName"

var employees = //some IQueryable<Employee>
IQueryable<EmployeeDTO> theResult = query.Project<EmployeeDTO>(employees);

Where EmployeeDTO is something like this:

public class EmployeeDTO
{
  public string FullName;
}

If you do not want to define an EmployeeDTO class you can use an annonymous type like this:

IQueryable theResult = query.Project(employees);

theResult is an untyped IQueryable. The ElementType is an annonymous type of the form:

public class FullName;String;
{
  public string FullName;
}

Basic filtering

If you want to filter the IQueryable by some user-entered value.

The filters values are defined using a Dictionary. The key is the name of the field and the value is the filter value. You can use a set of operators to filter.

IQueryable<Employee> employees = ...
var filters = new Dictionary<string, string> {{"FullName", "Car%"}};   // '%' acts as the wildcard
employees = query.Filter(employees, filters);

This is equivalent to:

// by default it is case insensitive but you can override this
employees = employees.Where(x => x.LastName.ToLower().StartsWith("car"));

Basic sorting

You can also apply sorting to an IQueryable pretty much the same way that you apply filters:

var sortings = new List<KeyValuePair<string, SortDirection>>{
  new KeyValuePair<string, SortDirection>("FullName", SortDirection.Descending)
}
employees = query.OrderBy(employees, sortings);

This is equivalent to:

employees = employees.OrderByDescending(x => x.Name + " " + x.LastName);

Multiple sortings are supported.

Advance filtering

You can use a small set of operators to filter. The kind of expressions you can use to filter depends on the type of the property that the filter is targeting. This can be overriden using the FilterAsX methods.

For System.String you can use the wildcard % at the start, end or both to create 'like' expressions. For example:

The filter is by default case insensitive but you can override this while creating the Query object.

For numeric types (int, double, decimal, etc) you can use these operators:

WebForms controls

Query includes a set of DataControlField classes that enables a GridView with filtering fields right below each header. Multiple sorting is also supported. The initial release of this project required jQuery but this requirement was removed. Now all the fields are customs AJAX client controls. You need AJAX Control toolkit as it is used to show a datepicker in the date filters.

The main entry point to this project is the GridExtender control:

<query:GridExtender ID="GridExtender" runat="server"
                    GridViewId="GridView"
                    AutoFilterDelay="2000"
                    EnableFilters="True"
                    OnFilter="GridExtender_Filter"
                    OnSort="GridExtender_Sort" />

These are the more important properties:

The GridExtender exposes all the current filters and sortings through two properties:

There are three kinds of DataControlField's:

All fields have a anchor as title above them which when clicked triggers the Sort event in GridExtender. The Filter event is triggered by hitting the enter key on a field's filter UI element or automatically by the AutoFilterDelay parameter.

These are the properties common to all fields:

The following are properties specific to DropDownField:

This is an example of a GridView using the DataControlField's provided:

<asp:GridView ID="GridView" runat="server" AutoGenerateColumns="False" ShowHeaderWhenEmpty="True"
              AllowPaging="True" AllowSorting="False" PageSize="8">
    <Columns>
        <query:TextField Name="Nombre" HeaderText="Nombre"
                         UrlFormat="https://www.google.com.ar/search?q={0} {1}" UrlFields="Nombre, Apellido" />
        <query:TextField Name="Apellido" HeaderText="Apellido" />
        <query:TextField Name="Dni" HeaderText="Dni" />
        <query:DropDownField Name="EstadoCivil" HeaderText="Estado civil" />
        <query:TextField Name="Edad" HeaderText="Edad" />
        <query:TextField Name="Salario" HeaderText="Salario" />
        <query:DateField Name="FechaNacimiento" HeaderText="Fecha Nacimiento" Format="d" />
        <query:TextField Name="AttachmentCount" HeaderText="Number of attachments" />
    </Columns>
</asp:GridView>