Sorting on subtype fields
Posts
Posts: 95
Joined: 20-Jun-2009
Joined: 20-Jun-2009
I needed a day or two to get it all working so I'm just making a quick post here of my solution regarding the sorting of datagrid columns consisting of entitytype fields and fields of inherited types.
I also didn't want to use the clientside sorting of the datacontrol as it A. sorts on clientside 
and thus is less performant then sorting server side and B. it will only sort the rows in the current page of your gridview when using paging. (This leads to very weird grid paging sequences as the sorting is done on page level and not the entire set of data) I imagine you could rip the entire resultset from the datasource everytime you page, but that would obviously be a bad design decision.
On top of that, I have some display properties in my inherited types I use for convinience. In the code example, the 'FullAddressElevator' is actualy a read only property in my inherited type consisting of some fields from different related entitytypes. As the 'FullAddressElevator' field was being displayed in my grid, the user obviously also wanted to be able to sort on it.
This latter prerequisite was the one that realy had me stumbed as I knew I coudn't sort it serverside, as it's just a display property and not a database field and I also didn't want to use the clientside sorting as that would mean the above mentioned downside.
My solution:
Server side without a question; that I was unwilling to derive from as clientside sorting is, in my very very humble opinion
, just bad practise. In order to get this issue resolved, I read the posts on this forum where Frans, very correctly, explained that when you wanted to sort on a field of a subtype, you should use clientside sorting as the field isn't simply available serverside.
The solution for me was actualy very simple. My 'FullAddressElevator' display property uses a database field 'Street' in its logic, so I realised I could just as easely use the 'Street' property to sort on as my 'FullAddressElevator' property starts of with the value of the 'Street' field.
A last quirk I had to resolve was the following: Imagine the grid; sort on column 1 (an Id), sort goes as expected; sort on 'FullAddressElevator' column, sort as expected, page to the last page -> Exception
The problem here was that when the user sorts on the 'FullAddressElevator' column and then uses the paging functionality, the PerformSelect routine's e.Sorter will still have the 'FullAddressElevator' as sortclause and generates a problem as this field does not exist in the database. I solved this by interogating the e.Sorter and removing the faulty sortclause and replacing it with the correct one in runtime. (Correct one is the 'Street' field)
This took me some time to figure out and I'm still not sure that this is the actual way to go so I'd like to get some feedback on this. I realise this will not work if your property in your derived type has no link wotsoever to any database field. Eather way, I hope it may help someone.
I added the PrepareFetch method for completness.
protected void llblObjectDataSourceElevators_PerformSelect(object sender, PerformSelectEventArgs2 e)
{
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
PrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ElevatorEntity);
prefetchPath.Add(ElevatorEntity.PrefetchPathCustomer, new CustomerFactory()).SubPath.Add(CustomerEntity.PrefetchPathAddressCustomer, new AddressCustomerFactory());
prefetchPath.Add(ElevatorEntity.PrefetchPathAddressElevator, new AddressElevatorFactory());
ExcludeIncludeFieldsList excludedFields = new ExcludeIncludeFieldsList(true);
excludedFields.Add(ElevatorFields.Remarks);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
if (e.Filter != null) e.Filter.PredicateExpression.Clear();
PrepareFetch(bucket, (LLBLGenProDataSourceBase)sender);
bucket.Relations.Add(ElevatorEntity.Relations.AddressElevatorEntityUsingElevatorId);
llblGenProDataSourceElevators.SortingMode = DataSourceSortingMode.ServerSide;
if (_sortExpression.Contains("FullAddressElevator"))
{
SortExpression sorter = new SortExpression();
SortOperator sortOperator = (_sortDirection == SortDirection.Ascending ? SortOperator.Ascending : SortOperator.Descending);
sorter.Add(new SortClause(AddressElevatorFields.Street, null, sortOperator));
adapter.FetchEntityCollection(e.ContainedCollection, bucket, e.MaxNumberOfItemsToReturn, sorter, prefetchPath,
excludedFields, e.PageNumber, e.PageSize);
}
else
{
if(e.Sorter != null)
{
List<SortClause> sortClauses = new List<SortClause>();
IEnumerator enumerator = e.Sorter.GetEnumerator();
enumerator.Reset();
while (enumerator.MoveNext())
{
sortClauses.Add((SortClause)enumerator.Current);
}
foreach (SortClause sortClause in sortClauses)
{
if (sortClause.FieldToSortCore.Alias == "FullAddressElevator")
{
SortClause replacementSortClause = new SortClause(AddressElevatorFields.Street, null,
sortClause.SortOperatorToUse);
e.Sorter.Remove(sortClause);
e.Sorter.Add(replacementSortClause);
}
}
}
adapter.FetchEntityCollection(e.ContainedCollection, bucket, e.MaxNumberOfItemsToReturn, e.Sorter, prefetchPath,
excludedFields, e.PageNumber, e.PageSize);
}
}
}
protected void llblObjectDataSourceElevators_PerformGetDbCount(object sender, PerformGetDbCountEventArgs2 e)
{
PrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ElevatorEntity);
prefetchPath.Add(ElevatorEntity.PrefetchPathCustomer, new CustomerFactory()).SubPath.Add(CustomerEntity.PrefetchPathAddressCustomer, new AddressCustomerFactory());
prefetchPath.Add(ElevatorEntity.PrefetchPathAddressElevator, new AddressElevatorFactory());
IRelationPredicateBucket bucket = new RelationPredicateBucket();
if (e.Filter != null) e.Filter.PredicateExpression.Clear();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
PrepareFetch(bucket, (LLBLGenProDataSourceBase)sender);
e.DbCount = TotalRowCount = adapter.GetDbCount(e.ContainedCollection, bucket);
}
}
private void PrepareFetch(IRelationPredicateBucket bucket, LLBLGenProDataSourceBase llblGenProDataSourceBase)
{
if (OverviewType == ElevatorOverviewType.Overview)
{
return;
}
if (OverviewType == ElevatorOverviewType.Maintenance)
{
List<ShedualType> sheduals = ElevatorManager.GetShedualTypes();
FieldCompareRangePredicate filter = new FieldCompareRangePredicate(ElevatorFields.ShedualType, null, sheduals);
bucket.PredicateExpression.Add(filter);
return;
}
if (OverviewType == ElevatorOverviewType.Search)
{
if(SearchMask.Contains(ElevatorSearch.ElevatorSearchType.EndWaranty))
{
DateTime fromEndWarantyDate = (SearchEndWarantyDateFrom == DateTime.MinValue ? DateTime.Now : SearchEndWarantyDateFrom);
DateTime toEndWarantyDate = (SearchEndWarantyDateTo == DateTime.MinValue ? DateTime.Now : SearchEndWarantyDateTo);
FieldBetweenPredicate filterEndWaranty = new FieldBetweenPredicate(ElevatorFields.EndWaranty, null, fromEndWarantyDate, toEndWarantyDate);
bucket.PredicateExpression.Add(filterEndWaranty);
}
if (SearchMask.Contains(ElevatorSearch.ElevatorSearchType.EndWaranty))
{
DateTime fromstartDate = (SearchStartDateFrom == DateTime.MinValue ? DateTime.Now : SearchStartDateFrom);
DateTime toStartDate = (SearchStartDateTo == DateTime.MinValue ? DateTime.Now : SearchStartDateTo);
FieldBetweenPredicate filterStartDate = new FieldBetweenPredicate(ElevatorFields.StartDate, null, fromstartDate, toStartDate);
bucket.PredicateExpression.Add(filterStartDate);
}
((ElevatorMasterPage)Page.Master).PageDescriptionType = ElevatorMasterPage.ElevatorPageDescriptionType.SearchResult;
List<string> searchedFields = new List<string>(SearchMask.Count);
foreach (string searchedField in searchedFields)
{
searchedFields.Add(searchedField);
}
((ElevatorMasterPage)Page.Master).SearchedFields = searchedFields;
return;
}
ParameterCollection parameters = llblGenProDataSourceBase.SelectParameters;
foreach (Parameter parameter in parameters)
{
if (parameter.Name == "ContractNumber" && textboxContractNumberFind.Text != string.Empty)
{
FieldLikePredicate filter = new FieldLikePredicate(ElevatorFields.ContractNumber, null, "%" + textboxContractNumberFind.Text + "%");
bucket.PredicateExpression.Add(filter);
}
if (parameter.Name == "FullAddressElevator" && textboxAddressElevatorFind.Text != string.Empty)
{
FieldLikePredicate filterElevatorAddressStreet = new FieldLikePredicate(AddressElevatorFields.Street, null, "%" + textboxAddressElevatorFind.Text + "%");
bucket.PredicateExpression.Add(filterElevatorAddressStreet);
FieldLikePredicate filterElevatorAddressCity = new FieldLikePredicate(AddressElevatorFields.City, null, "%" + textboxAddressElevatorFind.Text + "%");
bucket.PredicateExpression.AddWithOr(filterElevatorAddressCity);
FieldLikePredicate filterElevatorAddressContactPerson = new FieldLikePredicate(AddressElevatorFields.ContactPerson, null, "%" + textboxAddressElevatorFind.Text + "%");
bucket.PredicateExpression.AddWithOr(filterElevatorAddressContactPerson);
FieldLikePredicate filterCustomerAddressStreet = new FieldLikePredicate(AddressCustomerFields.Street, null, "%" + textboxAddressElevatorFind.Text + "%");
bucket.PredicateExpression.AddWithOr(filterCustomerAddressStreet);
FieldLikePredicate filterCustomerAddressCity = new FieldLikePredicate(AddressCustomerFields.City, null, "%" + textboxAddressElevatorFind.Text + "%");
bucket.PredicateExpression.AddWithOr(filterCustomerAddressCity);
bucket.Relations.Add(ElevatorEntity.Relations.CustomerEntityUsingCustomerId);
bucket.Relations.Add(CustomerEntity.Relations.AddressCustomerEntityUsingCustomerId);
}
if (parameter.Name == "ContractType" && int.Parse(dropdownListContractType.SelectedValue) != 0)
{
FieldLikePredicate filter = new FieldLikePredicate(ElevatorFields.ContractType, null, dropdownListContractType.SelectedValue);
bucket.PredicateExpression.Add(filter);
}
if (parameter.Name == "ShedualType" && int.Parse(dropdownListShedualType.SelectedValue) != 0)
{
FieldLikePredicate filter = new FieldLikePredicate(ElevatorFields.ShedualType, null, dropdownListShedualType.SelectedValue);
bucket.PredicateExpression.Add(filter);
}
if (parameter.Name == "PriorityType" && new Guid(dropdownListPriorityType.SelectedValue) != Guid.Empty)
{
FieldLikePredicate filter = new FieldLikePredicate(ElevatorFields.PriorityType, null, dropdownListPriorityType.SelectedValue);
bucket.PredicateExpression.Add(filter);
}
}
}
<%@ Import Namespace="Gizmo.Orms.Ui.Web.Code"%>
<%@ Control Language="C#" AutoEventWireup="true" EnableViewState="false" CodeBehind="ElevatorOverviewControl.ascx.cs" Inherits="Gizmo.Orms.Ui.Web.Controls.Elevator.ElevatorOverview" %>
<%@ Register TagPrefix="llbl" Assembly="SD.LLBLGen.Pro.ORMSupportClasses.NET20" Namespace="SD.LLBLGen.Pro.ORMSupportClasses"%>
<%@ Register TagPrefix="mb" Assembly="MattBerseth.WebControls.AJAX" Namespace="MattBerseth.WebControls.AJAX.GridViewControl" %>
<%@ Register TagPrefix="ajtk" Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" %>
<llbl:llblgenprodatasource2 id="llblGenProDataSourceElevators" runat="server"
LivePersistence="False"
CacheLocation="ASPNetCache"
Datacontainertype="EntityCollection"
AdapterTypeName="Gizmo.Orms.Llbl.Dal.DataAccessAdapter, Gizmo.Orms.Llbl.Dal"
OnPerformGetDbCount="llblObjectDataSourceElevators_PerformGetDbCount"
OnPerformSelect="llblObjectDataSourceElevators_PerformSelect"
OnPerformWork="llblObjectDataSourceElevators_PerformWork"
EntityFactoryTypeName="Gizmo.Orms.Core.FactoryClasses.ElevatorFactory, Gizmo.Orms.Core"
EnablePaging="True"
AllowDuplicates="False" >
<SelectParameters>
<asp:ControlParameter ControlID="textboxContractNumberFind" Name="ContractNumber" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="textboxAddressElevatorFind" Name="FullAddressElevator" PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="dropdownListContractType" Name="ContractType" PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="dropdownListShedualType" Name="ShedualType" PropertyName="SelectedValue" Type="Int32" />
<asp:ControlParameter ControlID="dropdownListPriorityType" Name="PriorityType" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</llbl:llblgenprodatasource2>
<table style="width: 100%" cellpadding="0" cellspacing="1" class="igoogle-night">
<tr>
<td colspan="7" >
<asp:GridView ID="gridViewElevators" runat="server" AutoGenerateColumns="False" AllowPaging="True"
AllowSorting="True" DataSourceID="llblGenProDataSourceElevators" PageSize="15" AllowCustomPaging="True"
CssClass="igoogle-night" Width="100%" EnableViewState="False" GridLines="None" ShowHeader="True"
OnRowDataBound="gridViewElevators_DataBound"
OnRowCreated="gridViewElevators_RowCreated"
OnRowCommand="gridViewElevators_RowCommand"
OnPreRender="gridViewElevators_PreRender"
OnSorting="gridViewElevators_Sorting">
<RowStyle CssClass="data-row" />
<AlternatingRowStyle CssClass="alt-data-row" />
<HeaderStyle CssClass="header-row" />
<PagerTemplate>
<table border="1" width="100%">
<tr>
<td align="left">
<asp:ImageButton ID="imageButtonFirst" runat="server" CommandArgument="First" CommandName="Page" ImageUrl="~/Image/Icons/icoPagerFirst.png" />
<asp:ImageButton ID="imageButtonPrevious" runat="server" CommandArgument="Prev" CommandName="Page" ImageUrl="~/Image/Icons/icoPagerPrevious.png" />
<asp:ImageButton ID="imageButtonNext" runat="server" CommandArgument="Next" CommandName="Page" ImageUrl="~/Image/Icons/icoPagerNext.png" />
<asp:ImageButton ID="imageButtonLast" runat="server" CommandArgument="Last" CommandName="Page" ImageUrl="~/Image/Icons/icoPagerLast.png" />
</td>
<td align="right">
<asp:Label ID="labelTotalNumberOfRecords" runat="server" />
Pag
<asp:TextBox ID="textBoxGoToPage" runat="server" style='font-size: 0.9em;' Width="25px" AutoPostBack="true"
OnTextChanged="textBoxGoToPage_TextChanged"></asp:TextBox>
/
<asp:Label ID="labelTotalNumberOfPages" runat="server" />
</td>
</tr>
</table>
</PagerTemplate>
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%# Convert.ToInt32(DataBinder.Eval(Container, "DataItemIndex")) + 1 + (gridViewElevators.PageSize * gridViewElevators.PageIndex)%>.
</ItemTemplate>
<ItemStyle HorizontalAlign="Center" Width="20px" />
</asp:TemplateField>
<asp:BoundField DataField="Id" HeaderText="Id" SortExpression="Id"
ItemStyle-Width="50px" ItemStyle-HorizontalAlign="Center" Visible="False" >
<ItemStyle HorizontalAlign="Center" Width="50px" />
</asp:BoundField>
<asp:TemplateField HeaderText="Number" SortExpression="ContractNumber">
<ItemTemplate>
<asp:LinkButton runat="server" id="linkButtonContractDetail"
PostBackUrl='<%# ((BasePage)Page).GetCurrentPageRelativePath() + "ElevatorDetail.aspx?Id=" + Eval("Id") %>'
Text='<%# Bind("ContractNumber") %>'></asp:LinkButton>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" Width="70px" BackColor="#d4d4d4" />
</asp:TemplateField>
<asp:TemplateField HeaderText="FullAddressElevator" SortExpression="FullAddressElevator">
<ItemTemplate>
<asp:Label ID="labelFullAddressElevator" runat="server" Text='<%# Bind("FullAddressElevator") %>'></asp:Label>
</ItemTemplate>
<ItemStyle Width="280px" />
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="Left" />
</asp:TemplateField>
<asp:TemplateField HeaderText="ContractType" SortExpression="ContractType" ItemStyle-Width="150px">
<ItemTemplate>
<asp:Label ID="LabelContractType" runat="server" Text='<%# Bind("ContractType") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:BoundField DataField="PriorityType" HeaderText="PriorityType" SortExpression="PriorityType" ItemStyle-Width="100px" >
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
</asp:BoundField>
<asp:BoundField DataField="ShedualType" HeaderText="ShedualType" SortExpression="ShedualType" ItemStyle-Width="150px" >
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
</asp:BoundField>
<asp:TemplateField HeaderText="Delete" >
<ItemTemplate>
<asp:Image runat="server" id="imageElevatorDelete" ImageUrl="~/Image/Icons/icoElevatorDelete.png" height="12" width="12"></asp:Image>
</ItemTemplate>
<ItemStyle Width="50px" />
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
</Columns>
</asp:GridView>
<mb:GridViewControlExtender runat="server" TargetControlID="gridViewElevators" RowHoverCssClass="row-over" RowSelectCssClass="row-select" >
</mb:GridViewControlExtender>
</td>
</tr>
<tr>
<td style="width: 16px;">
</td>
<td style="width: 70px;">
<table>
<tr>
<td>
<asp:TextBox runat="server" ID="textboxContractNumberFind" Text="" Width="50px" ></asp:TextBox>
<ajtk:FilteredTextBoxExtender ID="filteredTextBoxExtenderContractNumberFind" runat="server" FilterType="Numbers" ValidChars="1234567890" TargetControlID="textboxContractNumberFind" />
</td>
<td>
<asp:ImageButton ID="imageButtonContractNumberFind" runat="server" ImageUrl="~/Image/Icons/icoSearch.png" Height="13px" Width="13px" />
</td>
</tr>
</table>
</td>
<td style="width: 260px;">
<table>
<tr>
<td>
<asp:TextBox runat="server" ID="textboxAddressElevatorFind" Text="" Width="260px"></asp:TextBox>
</td>
<td>
<asp:ImageButton ID="imageButtonAddressFind" runat="server" ImageUrl="~/Image/Icons/icoSearch.png" Height="13px" Width="13px" />
</td>
</tr>
</table>
</td>
<td style="width: 150px; text-align: center;">
<asp:DropDownList ID="dropdownListContractType" AutoPostBack="true" runat="server" Width="150px"></asp:DropDownList>
</td>
<td style="width: 100px; text-align: center;">
<asp:DropDownList ID="dropdownListPriorityType" AutoPostBack="true" runat="server" Width="100px"></asp:DropDownList>
</td>
<td style="width: 150px; text-align: center;">
<asp:DropDownList ID="dropdownListShedualType" AutoPostBack="true" runat="server" Width="150px"></asp:DropDownList>
</td>
<td style="width: 50px;"></td>
</tr>
</table>
