LLBLGen 2.5 Runtime: v2.0.50727 ASP.NET 2.0

Can anyone tell me how to sort on a custom property? I have created a public property in the // __LLBLGENPRO_USER_CODE_REGION_START CustomEntityCode section of the entity like the following.

public int Attendance
{
    get
    {
        return this.VisitRequest.Count;
    }
}

VisitRequest is a collection and I want to be able to sort on the count of this collection. So in my GridView BoundField I have SortExpression="Attendance". When I try and sort on this column header I receive the following error.

An exception was caught during the execution of a retrieval query: Invalid column name 'Attendance'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Obviously the sorting is based on the physical database column name. Can anyone tell me how to sort on a custom property?

Please check the following threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=9163 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8072

I have another question about this now.

How do I do this when the sorting is happening in the PerformSelect event. Normal I pass e.Sorter to my data layer which fetches the data a second time just to do the sorting. I would rather do this all client side and remove the second fetch for the sorting.

Should I be storing the fetched collection in memory and then doing any additional sorts to this in memory collection?

Only pass the e.Sorter normally as you have specified that the sort-mode is ClientSide:

<llblgenpro:LLBLGenProDataSource2 ID="LLBLGenProDataSource2_1" runat="server" 
     ...    
     SortingMode="ClientSide">
</llblgenpro:LLBLGenProDataSource2>

public partial class YourEntity
{
     public int Attendance
     {
         get
         {
             return this.VisitRequest.Count;
         }
     }
}

if (!IsPostBack)
{
    SortExpression sorter = new SortExpression();
    sorter.Add(new SortClause(new EntityProperty("Attendance"),null, SortOperator.Ascending));

    this.LLBLGenProDataSource2_1.SorterToUse = sorter;
}

protected void LLBLGenProDataSource2_1_PerformSelect(object sender, PerformSelectEventArgs2 e)
{

    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.FetchEntityCollection(e.ContainedCollection, e.Filter, 0, e.Sorter, e.PrefetchPath);
    }
}

I'm now getting an error. I have the following code which is causing the error at the end.

UI Code Layer.

void eventsDS_PerformSelect(object sender, PerformSelectEventArgs2 e)
{
    SortExpression sortExpression = new SortExpression();

    if (this.eventsDS.SortingMode == DataSourceSortingMode.ServerSide && e.Sorter.Count == 0)
    {
        // Use when SortingMode is ServerSide.
        e.Sorter.Add(ScheduledVisitFields.StartTime | SortOperator.Descending);
        sortExpression = (SortExpression)e.Sorter;
    }
    else if (this.eventsDS.SortingMode == DataSourceSortingMode.ClientSide && !this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is initial fetch.
    }
    else if (this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is post back (re-sorting).
        string sortedField = this.eventsGrid.SortExpression;
        string sortDirection = this.eventsGrid.SortDirection.ToString();

        // TODO: Convert the sortedField and sortDirection into a LLBLGen SortExpression.
        SortOperator sortOperator = (SortOperator)Enum.Parse(typeof(SortOperator), sortDirection);
        sortExpression.Add(new SortClause(new EntityProperty(sortedField), null, sortOperator));
    }

    ServiceManager.GetVisitProgramManager().GetScheduledVisits(e.ContainedCollection, null, sortExpression, null);
}

Data Service Layer.

public IEntityCollection2 GetScheduledVisits(IEntityCollection2 results, IRelationPredicateBucket predicateBucket, ISortExpression sortExpression, ExcludeIncludeFieldsList excludeIncludeFieldsList)
        {
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.ScheduledVisitEntity);
            prefetchPath.Add(ScheduledVisitEntity.PrefetchPathVisitRequest);

            using (DataAccessAdapter adapter = new DataAccessAdapter(this.connectionString))
            {
                adapter.FetchEntityCollection(results, predicateBucket, 0, sortExpression, prefetchPath, excludeIncludeFieldsList);
            }

            return results;
        }

Error.

Unable to cast object of type 'SD.LLBLGen.Pro.ORMSupportClasses.EntityProperty' to type 'SD.LLBLGen.Pro.ORMSupportClasses.IEntityField2'.

I just code the code by adding one line in. Why can't I pass the sortExpression into by data access layer?

DataSource.SorterToUse = sortExpression

This fixed my problem but I now wonder why the original code doesn't work.

Original Code:

void eventsDS_PerformSelect(object sender, PerformSelectEventArgs2 e)
{
    SortExpression sortExpression = new SortExpression();

    if (this.eventsDS.SortingMode == DataSourceSortingMode.ServerSide && e.Sorter.Count == 0)
    {
        // Use when SortingMode is ServerSide.
        e.Sorter.Add(ScheduledVisitFields.StartTime | SortOperator.Descending);
        sortExpression = (SortExpression)e.Sorter;
    }
    else if (this.eventsDS.SortingMode == DataSourceSortingMode.ClientSide && !this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is initial fetch.
    }
    else if (this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is post back (re-sorting).
        string sortedField = this.eventsGrid.SortExpression;
        string sortDirection = this.eventsGrid.SortDirection.ToString();

        // TODO: Convert the sortedField and sortDirection into a LLBLGen SortExpression.
        SortOperator sortOperator = (SortOperator)Enum.Parse(typeof(SortOperator), sortDirection);
        sortExpression.Add(new SortClause(new EntityProperty(sortedField), null, sortOperator));
    }

    ServiceManager.GetVisitProgramManager().GetScheduledVisits(e.ContainedCollection, null, null, null);
}

New Code (now working):

void eventsDS_PerformSelect(object sender, PerformSelectEventArgs2 e)
{
    SortExpression sortExpression = new SortExpression();

    if (this.eventsDS.SortingMode == DataSourceSortingMode.ServerSide && e.Sorter.Count == 0)
    {
        // Use when SortingMode is ServerSide.
        e.Sorter.Add(ScheduledVisitFields.StartTime | SortOperator.Descending);
        sortExpression = (SortExpression)e.Sorter;
    }
    else if (this.eventsDS.SortingMode == DataSourceSortingMode.ClientSide && !this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is initial fetch.
    }
    else if (this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is post back (re-sorting).
        string sortedField = this.eventsGrid.SortExpression;
        string sortDirection = this.eventsGrid.SortDirection.ToString();

        // TODO: Convert the sortedField and sortDirection into a LLBLGen SortExpression.
        SortOperator sortOperator = (SortOperator)Enum.Parse(typeof(SortOperator), sortDirection);
        sortExpression.Add(new SortClause(new EntityProperty(sortedField), null, sortOperator));
    }
    
    this.eventsDS.SorterToUse = sortExpression;

    ServiceManager.GetVisitProgramManager().GetScheduledVisits(e.ContainedCollection, null, null, null);
}

Aaron, when using EntityProperty at sorting, you must pass it to LLBLGenProDataSource (via SorterToUse property) as the sorting is executed at client-side by the LLBLGenProDataSource. That's why you got error, because EntityProperty is used in In-Memory filters.

I now have a new problem related to this. The code that I last posted did fix my problem of sorting when clicking on a column header for the custom property. Now my default sorting for when the page is first loaded is not working. What am I doing wrong now?

Here is the current code.

void eventsDS_PerformSelect(object sender, PerformSelectEventArgs2 e)
{
    SortExpression sortExpression = new SortExpression();
    
    if (this.eventsDS.SortingMode == DataSourceSortingMode.ClientSide && !this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is first loading page.
        sortExpression.Add(new SortClause(new EntityProperty(ScheduledVisitFields.StartTime.Name), null, SortOperator.Ascending));
    }
    else if (this.eventsDS.SortingMode == DataSourceSortingMode.ClientSide && this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is post back (re-sorting).
        string sortedField = this.eventsGrid.SortExpression;
        string sortDirection = this.eventsGrid.SortDirection.ToString();

        // Convert the sortedField and sortDirection into a LLBLGen SortExpression.
        SortOperator sortOperator = (SortOperator)Enum.Parse(typeof(SortOperator), sortDirection);
        sortExpression.Add(new SortClause(new EntityProperty(sortedField), null, sortOperator));
    }

    this.eventsDS.SorterToUse = sortExpression;

    ServiceManager.GetVisitProgramManager().GetScheduledVisits(e.ContainedCollection, null, null, null);
}

On page load the data is, I think, not being sorted as all.

Here is the results.

StartTime 2008-05-20 08:00:00.000 2008-05-20 09:00:00.000 2008-05-13 09:15:00.000 2008-06-17 08:00:00.000 2008-06-24 09:00:00.000 2008-07-22 09:00:00.000 2008-08-21 09:00:00.000 2008-09-23 09:00:00.000 2008-10-21 09:00:00.000 2008-05-08 10:00:00.000

Don't use the EntityProperty if you are not sorting on a custom property.

Please try the following:

    if (this.eventsDS.SortingMode == DataSourceSortingMode.ClientSide && !this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is first loading page.
        sortExpression.Add(ScheduledVisitFields.StartTime.Name | SortOperator.Ascending);
    }

Thanks for the reply. I just tried your suggestion using the following code and it still does not work. The order of the rows is still the same. It seems the sort is not applied.

void eventsDS_PerformSelect(object sender, PerformSelectEventArgs2 e)
{
    SortExpression sortExpression = new SortExpression();
    
    if (this.eventsDS.SortingMode == DataSourceSortingMode.ClientSide && !this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is first loading page.
        sortExpression.Add(ScheduledVisitFields.StartTime | SortOperator.Ascending);
    }
    else if (this.eventsDS.SortingMode == DataSourceSortingMode.ClientSide && this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is post back (re-sorting).
        string sortedField = this.eventsGrid.SortExpression;
        string sortDirection = this.eventsGrid.SortDirection.ToString();

        // Convert the sortedField and sortDirection into a LLBLGen SortExpression.
        SortOperator sortOperator = (SortOperator)Enum.Parse(typeof(SortOperator), sortDirection);
        sortExpression.Add(new SortClause(new EntityProperty(sortedField), null, sortOperator));
    }

    this.eventsDS.SortingMode = DataSourceSortingMode.ClientSide;
    this.eventsDS.SorterToUse = sortExpression;

    ServiceManager.GetVisitProgramManager().GetScheduledVisits(e.ContainedCollection, null, null, null);
}

The code you gave also throws an error because you left in the .Name property the the Field is expected.

I think setting the Datasource.SorterToUse inside the Perform select won't have an effect.

For the first time you load the entities

If you wan to sort on client side, then you should sort the collection after beinf fetched. Either sort the DefaulView of the collection or use the Collection sort method.

Otherwise if you want to sort on the server/database (which I recomment), then you should pass the sort expression to the Fetch method.

sorting on a custom property is always client-side. So if you want to sort SOMETIMES on the server and SOMETIMES on the client, it's a bit problematic.

You could sort on the custom property using the Sort method on the entity collection fetched in the performselect method, otherwise you should set the sorting location to client on the datasource (which sorts only what's in the grid/on hte page)

Otis wrote:

sorting on a custom property is always client-side. So if you want to sort SOMETIMES on the server and SOMETIMES on the client, it's a bit problematic.

You could sort on the custom property using the Sort method on the entity collection fetched in the performselect method, otherwise you should set the sorting location to client on the datasource (which sorts only what's in the grid/on hte page)

Otis, it's nice to hear from you. I hope your doing well. I did finally get some code to work. I ended up with a problem where the grid would sort correctly, when first loading and when clicking the column header, but I had to click the column header twice to make it sort the opposite direction of the current sort. This I didn't like so I continued to mix and match code.

Below is what I finally ended up with that does work properly, and without the double click of the column header. The commented bits of code are different variations I tried to make work which all failed. If anyone can give me a better solution I would sure like to see it. What I'm doing to make this work feels like a hack and I don't really like it. It does work though.

protected void Page_Load(object sender, EventArgs e)
{
    if (this.IsPostBack)
    {
        this.eventsDS.SortingMode = DataSourceSortingMode.ClientSide;
    }
} 

void eventsDS_PerformSelect(object sender, PerformSelectEventArgs2 e)
{
    //ISortExpression sorter = e.Sorter;

    if (!this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is first loading page.
        //e.Sorter.Add(ScheduledVisitFields.StartTime | SortOperator.Ascending);
        this.eventsGrid.Sort(ScheduledVisitFields.StartTime.Name, SortDirection.Ascending);
        //sortExpression.Add(ScheduledVisitFields.StartTime | SortOperator.Ascending);
        //sortExpression.Add(new SortClause(new EntityProperty(ScheduledVisitFields.StartTime.Name), null, SortOperator.Ascending));
    }
    else if (this.eventsDS.SortingMode == DataSourceSortingMode.ClientSide && this.IsPostBack)
    {
        // Use when SortingMode is ClientSide and is post back (re-sorting).

        string sortedField = this.eventsGrid.SortExpression;
        string sortDirection = this.eventsGrid.SortDirection.ToString();

        // Convert the sortedField and sortDirection into a LLBLGen SortExpression.
        SortExpression sortExpression = new SortExpression();
        SortOperator sortOperator = (SortOperator)Enum.Parse(typeof(SortOperator), sortDirection);
        sortExpression.Add(new SortClause(new EntityProperty(sortedField), null, sortOperator));

        this.eventsDS.SorterToUse = sortExpression;
    }

    ServiceManager.GetVisitProgramManager().GetScheduledVisits(e.ContainedCollection, null, e.Sorter, null);

    //if (!this.IsPostBack)
    //  e.ContainedCollection.Sort(ScheduledVisitFields.StartTime.FieldIndex, System.ComponentModel.ListSortDirection.Ascending);
}

I think the solution might be easier than that.

Try to do the folowing:

1- Handle the Sorting Grid's event.

2- Check the passed in GridViewSortEventArgs, and check for the SortExpression and SortDirection.

3- If it's sorting on the custom field, then set the DataSource.SorterToUse as follows:

eventsDS.SorterToUse= new sortExpression.Add(new SortClause(new EntityProperty(sortedField), null, sortOperator));

4- Cancel the sort operation. by setting the ((GridViewSortEventArgs)e).Cancel = true;

This is a custom code, but may try it to sort custom fields.