This is the base class that handles List requests originating from client side, e.g. from grids.
Let's first sample when and how this class handles list requests:
-
First a list request must be triggered from client side. Possible options are:
a) You open a list page that contains a grid. Right after your grid object is created it builds up a ListRequest object, based on currently visible columns, initial sort order, filters etc. and submits it to server side.
b) User clicks on a column header to sort, clicks paging buttons or refresh button to trigger same events in option A.
c) You might manually call a list service using XYZService.List method.
-
A service request (AJAX) to MVC XYZController (in file XYZEndpoint.cs) arrives at server. Request parameters are deserialized from JSON into a ListRequest object.
-
XYZEndpoint calls XYZRepository.List method with retrieved ListRequest object.
-
XYZRepository.List method creates a subclass of ListRequestHandler (XYZRepository.MyListHandler) and invokes its Process method with the ListRequest.
-
ListRequestHandler.Process method builds up a dynamic SQL query, based on the ListRequest, metadata in its entity type (Row) and other information and executes it.
-
ListRequestHandler.Process returns a ListResponse with Entities member that contains rows to be returned.
-
XYZEndpoint receives this ListResponse, returns it from action.
-
ListResponse is serialized to JSON, sent back to client
-
Grid receives entities, updates its displayed rows and other parts like paging status.
We'll cover how grids build and submit a list request in another chapter. Let's focus on ListRequestHandler for now.
First we should have a look at what members a ListRequest object have:
public class ListRequest : ServiceRequest, IIncludeExcludeColumns
{
public int Skip { get; set; }
public int Take { get; set; }
public SortBy[] Sort { get; set; }
public string ContainsText { get; set; }
public string ContainsField { get; set; }
public Dictionary<string, object> EqualityFilter { get; set; }
[JsonConverter(typeof(JsonSafeCriteriaConverter))]
public BaseCriteria Criteria { get; set; }
public bool IncludeDeleted { get; set; }
public bool ExcludeTotalCount { get; set; }
public ColumnSelection ColumnSelection { get; set; }
[JsonConverter(typeof(JsonStringHashSetConverter))]
public HashSet<string> IncludeColumns { get; set; }
[JsonConverter(typeof(JsonStringHashSetConverter))]
public HashSet<string> ExcludeColumns { get; set; }
}These options are used for paging and similar to Skip and Page extensions in LINQ.
There is one little difference about Take. If you Take(0), LINQ will return you zero records, while Serenity will return ALL records. There is no point in calling a LIST service and requesting 0 records.
So, SKIP and TAKE has default values of 0, and they are simply ignored when 0 / undefined.
// returns all customers as Skip and Take are 0 by default
CustomerService.List(new ListRequest
{
}, response => {});If you have a grid that has page size 50 and switch to page number 4, SKIP will be 200 while TAKE is 50.
// returns customers between row numbers 201 and 250 (in some default order)
CustomerService.List(new ListRequest
{
Skip = 200,
Take = 50
}, response => {});These parameters are converted to relevant SQL paging statements based on SQL dialect.
This parameter takes an array to sort results on. Sorting is performed by generating SQL.
SortBy parameter expects a list of SortBy objects:
[JsonConverter(typeof(JsonSortByConverter))]
public class SortBy
{
public SortBy()
{
}
public SortBy(string field)
{
Field = field;
}
public SortBy(string field, bool descending)
{
Field = field;
Descending = descending;
}
public string Field { get; set; }
public bool Descending { get; set; }
}When calling a List method of XYZRepository server side to sort by Country then City descending, you might do it like this:
new CustomerRepository().List(connection, new ListRequest
{
SortBy = new[] {
new SortBy("Country"),
new SortBy("City", descending: true)
}
});SortBy class has a custom JsonConverter so when building a list request client side, you should use a simple string array:
// CustomerEndpoint and thus CustomerRepository is accessed from
// client side (YourProject.Script) through CustomerService class static methods
// which is generated by ServiceContracts.tt
CustomerService.List(connection, new ListRequest
{
SortBy = new[] { "Country", "City DESC" }
}, response => {});This is because ListRequest class definition at client side has a bit different structure:
[Imported, Serializable, PreserveMemberCase]
public class ListRequest : ServiceRequest
{
public int Skip { get; set; }
public int Take { get; set; }
public string[] Sort { get; set; }
// ...
}Column names used here should be Property names of corresponding fields. Expressions are not accepted. E.g. this won't work!:
CustomerService.List(connection, new ListRequest
{
SortBy = new[] { "t0.FirstName + ' ' + t0.LastName" }
}, response => {});These parameters are used by quick search funtionality which is search input on top left of grids.
When only ContainsText is specified and ContainsField is empty, searching is performed on all fields with [QuickSearch] attribute on them.
It is possible to define some specific field list to perform searches on grid client side, by overriding GetQuickSearchField() methods. So when such a field is selected in quick search input, search is only performed on that column.
If you set ContainsField to a field name that doesn't have QuickSearch attribute on it, system will raise an exception. This is for security purposes.
As usual, searching is done with dynamic SQL by LIKE statements.
CustomerService.List(connection, new ListRequest
{
ContainsText = "the",
ContainsField = "CompanyName"
}, response => {});SELECT ... FROM Customers t0 WHERE t0.CompanyName LIKE '%the%' If ContainsText is null or empty string it is simply ignored.
EqualityFilter is a dictionary that allows quick equality filtering by some fields. It is used by quick filter dropdowns on grids (ones that are defined with AddEqualityFilter helper).
CustomerService.List(connection, new ListRequest
{
EqualityFilter = new JsDictionary<string, object> {
{ "Country", "Germany" }
}
}, response => {});SELECT * FROM Customers t0 WHERE t0.Country = "Germany" Again, you should use property names as equality field keys, not expressions. Serenity doesn't allow any arbitrary SQL expressions from client side, to prevent SQL injections.
Please note that null and empty string values are simply ignored, similar to ContainsText, so it's not possible to filter for empty or null values with EqualityFilter. Such a request would return all records:
CustomerService.List(connection, new ListRequest
{
EqualityFilter = new JsDictionary<string, object> {
{ "Country", "" }, // won't work, empty string is ignored
{ "City", null }, // won't work, null is ignored
}
}, response => {});Use Criteria parameter if you intent to filter customers with empty countries.
This parameter accepts criteria objects similar to server side Criteria objects we talked about in Fluent SQL chapter. Only difference is, as these criteria objects are sent from client side, they have to be validated and can't contain any arbitrary SQL expressions.
Service request below will only return customers with empty country or null city values
CustomerService.List(connection, new ListRequest
{
Criteria = new Criteria("Country") == "" |
new Criteria("City").IsNull()
}, response => {});You could set Criteria parameter of generated ListRequest that is about to be submitted in your XYZGrid.cs like below:
protected override bool OnViewSubmit()
{
// only continue if base class didn't cancel request
if (!base.OnViewSubmit())
return false;
// view object is the data source for grid (SlickRemoteView)
// this is an EntityGrid so view.Params is a ListRequest
var request = (ListRequest)view.Params;
// we use " &= " here because otherwise we might clear
// filter set by an edit filter dialog if any.
request.Criteria &=
new Criteria(ProductRow.Fields.UnitsInStock) > 10 &
new Criteria(ProductRow.Fields.CategoryName) != "Condiments" &
new Criteria(ProductRow.Fields.Discontinued) == 0;
return true;
}You could also set other parameters of ListRequest in your grids similarly.
This parameter is only useful with rows that implements IIsActiveDeletedRow interface. If row has such an interface, list handler by default only returns rows that are not deleted (IsActive != -1). It is a way to not delete rows actually but mark them as deleted.
If this parameter is True, list handler will return all rows without looking at IsActive column.
Some grids for such rows have a little eraser icon on top right to toggle this flag, thus show deleted records or hide them (default).
Serenity tries hard to load only required columns of your entities from SQL server to limit network traffic to minimum between SQL Server < - > WEB Server and thus keep data size transferred to client as low as possible.
ListRequest has a ColumnSelection parameter for you to control the set of columns loaded from SQL.
ColumnSelection enumeration has following values defined:
public enum ColumnSelection
{
List = 0,
KeyOnly = 1,
Details = 2,
}By default grid requests records from List service in "ColumnSelection.List" mode (can be changed). Thus, its list request looks like this:
new ListRequest
{
ColumnSelection = ColumnSelection.List
}In ColumnSelection.List mode, ListRequestHandler returns table fields, thus fields that actually belong to the table, not view fields that are originating from joined tables.
One exception is expression fields that only contains reference to table fields, e.g. (t0.FirstName + ' ' + t0.LastName). ListRequestHandler also loads such fields.
ColumnSelection.KeyOnly only includes ID / primary key fields.
ColumnSelection.Details includes all fields, including view ones, unless a field is explicitly excluded or marked as "sensitive", e.g. a password field.
Dialogs loads edited records in Details mode, thus they also include view fields.
We told that grid requests records in List mode, so loads only table fields, then how it can show columns that originate from other tables?
Grid sends list of visible columns to List service with IncludeColumns, so these columns are included in selection even if they are view fields.
In memory grids can't do this. As they don't call services directly, you have to put [MinSelectLevel(SelectLevel.List)] to view fields that you wan't to load for in memory detail grids.
If you have a ProductGrid that shows SupplierName column its actual ListRequest looks like this:
new ListRequest
{
ColumnSelection = ColumnSelection.List,
IncludeColumns = new List<string> {
"ProductID",
"ProductName",
"SupplierName",
"..."
}
}Thus, these extra view fields are also included in selection.
If you have a grid that should only load visible columns for performance reasons, override its ColumnSelection level to KeyOnly. Note that non-visible table fields won't be available in client side row.
Opposite of IncludeColumns is ExcludeColumns. Let's say you have a nvarchar(max) Notes field on your row that is never shown in the grid. To lower network traffic, you may choose to NOT load this field in product grid:
new ListRequest
{
ColumnSelection = ColumnSelection.List,
IncludeColumns = new List<string> {
"ProductID",
"ProductName",
"SupplierName",
"..."
},
ExcludeColumns = new List<string> {
"Notes"
}
}OnViewSubmit is a good place to set this parameter (and some others):
protected override bool OnViewSubmit()
{
if (!base.OnViewSubmit())
return false;
var request = (ListRequest)view.Params;
request.ExcludeColumns = new List<string> { "Notes" }
return true;
}You might want to exclude some fields like Notes from ColumnSelection.List, without excluding it explicitly in grid. This is possible with MinSelectLevel attribute:
[MinSelectLevel(SelectLevel.Details)]
public String Note
{
get { return Fields.Note[this]; }
set { Fields.Note[this] = value; }
}There is a SelectLevel enumeration that controls when a field is loaded for different ColumnSelection levels:
public enum SelectLevel
{
Default = 0,
Always = 1,
Lookup = 2,
List = 3,
Details = 4,
Explicit = 5,
Never = 6
}SelectLevel.Default, which is the default value, corresponds to SelectLevel.List for table fields and SelectLevel.Details for view fields.
By default, table fields have a select level of SelectLevel.List while view fields have SelectLevel.Details.
SelectLevel.Always means such a field is selected for any column selection mode, even if it is explicitly excluded using ExcludeColumns.
SelectLevel.Lookup is obsolete, avoid using it. Lookup columns are determined with [LookupInclude] attribute.
SelectLevel.List means such a field is selected for ColumnSelection.List and ColumnSelection.Details modes or if it is explicitly included with IncludeColumns parameter.
SelectLevel.Details means such a field is selected for ColumnSelection.Details mode, or if it is explicitly included with IncludeColumns parameter.
SelectLevel.Explicit means such a field shouldn't be selected in any mode, unless it is explicitly included with IncludeColumns parameter. Use this for fields that are not meaningful for grids or edit dialogs.
SelectLevel.Never means never load this field! Use it for fields that shouldn't be sent to client side, like a password hash.