I've searched the forums for this and it seems like a common topic, so I apologize in advance if I missed the answer.  Is there *any* way to sort on virtual columns in the current 2007 release?  I've read that DevExpress and Infragistics Grids might handle it, but I am using the (free) KryptonDataGridView.

I've followed the steps outlined in the extending properties post and my virtual columns show up in grid view fine, but I can't figure out a way to sort on them.  I've tried going down the "views in SQL Server" path, but that is proving to be too much of a headache, especially with master/detail grids.

So far I love ES and everything else so far has worked beautifully.  This is the only really thorn in my side right now.
 

Hmm...tried this in the 2008 Alpha.  No luck there either.

 
Any help/guidance would be appreciated. 

I'm not familiar with that control, but if the column is in the grid, can't you sort the grid rather than the collection? With an MS DataGridView:

DataGridViewColumn col =
    dataGridView1.Columns["NameOfColumn"];
dataGridView1.Sort(col, ListSortDirection.Descending);

Have you tried posting in their forums re sorting a grid programmatically?

Thanks for the reply David.  I saw that in another reply of yours and gave it a whirl.  It doesn't seem to work either but I'm still plugging away at it.

I'm not 100% certain, but I think I'd run into issue with paging/sorting in tandem with that approach anyway though. I don't think I'd be able to use ES' excellent paging seeing that I'd need to bring back the entire collection to sort it properly in the grid view alone. I guess I'd also prefer to find a solution that isn't coupled to a particular control.

The sorting is one issue, filtering is another. It would be incredibly handy to be able to sort and/or filter a particular collection programatically using extended properties / virtual columns.   The very fact that those fields can be bound causes me to think that somehow I must be able to achieve this, I just can't for the life of me figure out how to do it.

Anyhoo, I've seen a few hints in Mike's posts that sorting and filtering on virtual columns might be a feature ES2008 so I thought I'd ask.

Cheers,
-Sean

Look at my 2nd response on this THREAD, same concept really, Sorting / Filtering work the same, you could trick it to work?

Have you tried using proxy stubs?

I am in the middle of building an n-tier application using ES and the client side always gets a proxy stub/ stub collection versus the ES collection. I have tested serializing the query criteria, sending it to the service, having the service fetch the query and then it returns the appropriate proxy stub collection based on the client-side criteria. I also tested extending the criteria on the service-side (I.e. filter the collection further based on security). I have not tried virtual columns or paging - yet.

In my case, I use WCF (Windows Communication Foundation) and have the clients connect via sessions, so the service can remember requests in session-mode. Essentially, every time a client opens a form, a unique Guid is created. When they request something from the service another request ID (Guid) is generated. Thus, when a user opens a form, the form requests a collection from the service and the service processes the requests and uses a callback to return the request. The service receives the request, processes it, and forwards the callback to the client, and then the client forawrds the request to the form (guid) that made the request. When the form receives the request, it deserializes the collection and displays the result. This should work with paging. If a collection is pageable, the request parameters should be able to include paging information and the service should be able to keep-alive the query results until the client logs out, times out, or closes the form. I have - assumed - and have not tested yet that I can create a proxy stub collection that is the equivelant of the current page. If the client clicks "next" or selects a particular page, the request for the requested page should be sent to the service and the service should build a proxy stub collection based on the equivelant of the current page and send it to the client. So, I am now curious if anyone has tested paging with proxy stubs and if proxy stubs can be built based upon the current page?

I do know that the proxy stubs do filter and sort with the DevExpress XtraGrid, but now my curiosity - as I noted - is building. I wouldn't expect general ES collections to be the best choice for disconnected, multi-tier scenarios, but I would expect that it would be very common to use proxy stubs and populate them based on the current page of the collection maintained service-side.

Well, I read further and tested further...

First, this link:

http://community.entityspaces.net/forums/thread/6805.aspx

It appears that sorting on calculated columns is not currently supported (directly); although, some work-around may eventually be shared.

Second, I tested the paging with proxy stubs and... SWEET... the paging works and the results are serialized as I expected (hoped). More than that, the DevExpress grid WILL sort on calculated columns (sorry). What I would like now is to get the proxy stub collections to implement the DevExpress IListServer interface for automatic support of paging with ES proxy stub collections... or some equivelant class (something) to supporting paging. Ahh.... but that is another topic.

Hopefully, you will get a response indicating whether support for sorting calculating columns will be added.

I'll have to try the proxy stuff.  Sounds a tad over my head though. :)

I tried a trial of the DevExpress and Infragistics grids and they do indeed work.  The fact that they can do it though keeps my hopes alive that I can somehow do this with a basic MS grid, or for a collection itself.

I took the simplest example I could think of though, and I can't seem to get it to work so I must be missing something.  Here's my query...

ContactCollection contacts = new ContactCollection();
contacts.Query.Select("<*, NULL AS FullName>");
contacts.Query.OrderBy("FullName", EntitySpaces.Interfaces.esOrderByDirection.Ascending);
contacts.Query.Load();
this.ContactCollectionBindingSource.DataSource = contacts;

I also tried "< *, ' ' As FullName>" Unfortunately, while it no longer throws an error on the OrderBy, it doesn't sort either.  The grid itself (a basic MS one now) doesn't either.

Mike, can you expand on this from that other post?

Mike.Griffin:

Your local property could then use GetColumn() internally and still be strongly typed externally.

I am not sure what you mean so maybe I am doing something wrong in the custom class itself...

        protected override List GetLocalBindingProperties()
        {
            List props = new List();
            props.Add(new esPropertyDescriptor(this, "FullName", typeof(string)));
            return props;
        }

        public string FullName
        {
            get { return string.Format("{0} {1}", this.FirstName, this.LastName); }
        }

 
In that example the FullName column shows up in the grid and is populated, but sorting (or filtering) on it doesn't seem to work.
 

I feel like I am being daft here so I apologize if I am.  :)

It appears to me though that the reason FullName is working in the demo is because it is actually pulling data back from the table.  All of the now obsolete extended property code has been commented out of the custom EmployeesCollection class in the demo and all FullName does in the custom Employees class is this...

         public string FullName
		{
			get { return base.GetSystemString("FullName"); }
		}

Just to clarify, using FullName was intended as an example of the kind of thing I am trying to do.  It doesn't truly represent what I want to do though.  Something simple like FullName could accomplished in exactly the way you do in the demo (by concatenating columns in T-SQL).  What I am looking for is a way to sort on columns that are not easily computed using T-SQL.  Columns computed at runtime using a separate financial class library, based on runtime properties of the entity.

So going back to the example from the demo, what I am trying to determine is how I could make FormattedName sortable.  By sortable, I mean that I can sort it programatically (for reporting or what not) and using grids.  As Mike said, sorting and filtering are pretty much the same thing so as a bonus that goal will be met once I meet this first one.

FormattedName shows up in a grid, and other third party grids seem to be able to sort it, so this leads me to believe that it can be sorted on, somehow.


Cheers,
-Sean
 

Here is what I've come up with. I have no idea whether it fits what you are tying to accomplish, but maybe it will lead you in the right direction.

Apparently, the MS DataGridView needs to have a column fully populated before it can sort on it. In other words, you cannot calculate the value of the column in the gettor. All you can do is retrieve a pre-calculated value using GetColumn(). Starting with the code in the Demo:

I added this to the custom EmployeesCollection class:

public void PopulateFormattedName()
{
    Random autoRand = new Random();
    foreach (Employees emp in this)
    {
        emp.SetColumn("FormattedName", 
            autoRand.Next().ToString() +
            emp.LastName);
    }
}

I changed the FormattedName gettor in the custom Employees class:

public string FormattedName
{
    get { return this.GetColumn("FormattedName").ToString(); }
}

In SampleCode.cs the Load now looks like this:

emps.Query.Select
(
    emps.Query.EmployeeID,
    emps.Query.LastName,
    emps.Query.FirstName,
    sqlText,
    "<'' AS FormattedName>"
);
emps.Query.Load();

emps.PopulateFormattedName();

Now both FullName and FormattedName will sort.

Thanks David!  That is exactly the direction I needed.

I wanted to make this a little more flexible and relatively easy to implement across various collections though, so after a little playing here's came up with. 

1) I modified my CollectionBase.cs like so...

       public virtual string GetVirtualColumnSqlString()
        {
            //default implementation returns an empty string
            return string.Empty;
        }

        protected virtual List<string> GetVirtualColumnNames()
        {
            //default implementation returns empty list
            List<string> columnNames = new List<string>();
            return columnNames;
        }

        public virtual void PopulateVirtualColumns()
        {
            //get entity type
            Type entityType = this.GetEntityType();
            //get property list
            PropertyInfo[] properties = entityType.GetProperties();
            //get virtual column names
            List<string> columnNames = this.GetVirtualColumnNames();
            ///loop through entities
            foreach (esEntity entity in this)
            {
                //loop through properties and if it's a virtual column, populate it
                foreach (PropertyInfo property in properties)
                {
                    if (columnNames.Contains(property.Name))
                    {
                        entity.SetColumn(property.Name, property.GetValue(entity, null));
                    }
                }
            }
        }

2) Then in my custom collection class I did this...

        public override string GetVirtualColumnSqlString()
        {
            return "'' AS FullName";
        }

        protected override List<string> GetVirtualColumnNames()
        {
            List<string> columnNames = new List<string>();
            columnNames.Add("FullName");
            return columnNames;
        }

3) Then I changed the code that loads the collection to this...

ContactCollection contacts = new ContactCollection();
string sqlText = string.Format("<*, {0}>", contacts.GetVirtualColumnSqlString);
contacts.Query.Select(sqlText);
contacts.Query.Load();

//populate the extended properties
contacts.PopulateVirtualColumns();

//default sort
contacts.Sort = "FullName ASC";

this.ContactCollectionBindingSource.DataSource = contacts;

The end result is that sorting works like a charm!  Woo-hoo!

Using this for any collection then is simply a matter of overriding "GetVirtualColumnSqlString" and "GetVirtualColumnNames" to include whatever virtual columns you need.  When creating the Select string you need to pay attention to the type you want though, as NULL will default to Int32 and throw an error for string properties.

Also, if you're worried about the reflection overhead, you can also override the method that populates the virtual columns like so...

      public override void PopulateVirtualColumns()
        {
            foreach (Contact contact in this)
            {
                contact.SetColumn("FullName", contact.FullName);
            }
        }

Now, while this works, I am sure there are probably better/faster ways of doing it.  Some ideas...

• Use attributes to identify virtual columns so that we can eliminate the need for "GetVirtualColumnNames"
• Create a new "LoadWithVirtualColumns" or something like that that could automatically modify the Select string and populate the virtual columns
• Allow for specifying which virtual columns you want to populate (rather than all of them)
• Allow for specifying virtual column dependencies (i.e. FullName requires that FirstName and LastName are in the Select) and either automatically add those or throw an error if they are not included
  

I'll probably try all of these once I have some more time, but I thought I'd at least post this first iteration though to show one way it could be done, and then we can improve upon it.

Thanks again!

Cheers,
-Sean