EntitySet Confusion

Okay, from the outset I have been keen on using the LINQ to SQL technology and in my enthusiasm I pressed forward and created LINQ to SQL classes from my SQL database using the handy little designer in Visual Studio.

I mucked around enough to fiqure out what it was doing and use these classes in a form, except one concept has proven a little frustrating: HOW TO USE THE DARNED ENTITYSET COLLECTIONS!  It's not that I couldn't use them, it's just that I didn't find them very useful.  Why?  Because they did not follow the rules of the other collections and I have found it extremely difficult to extract any information from Google on how to deal with them.  Today I made a few discoveries and thought I would post them so you could benefit, if only from a good laugh at my flailing around.

The Problem

When I first attempted to use these EntitySet collections I tried to follow the paradigm for sorting that works for List collections.  That is, in my XAML file:

<Grid>

  <Grid.Resources>

    <CollectionViewSource x:Key="SortedBios" Source="{Binding SelectedItem.CGBiographies, ElementName=cbxManagers, Mode=OneWay}">

      <CollectionViewSource.SortDescriptions>

        <scm:SortDescription PropertyName="Name"/>

      </CollectionViewSource.SortDescriptions>

    </CollectionViewSource>

  </Grid.Resources>

  <ListBox Name="lbxBios" DisplayMemberPath="Name" ItemsSource="{Binding Source={StaticResource SortedBios}}"/>

</Grid>

The ListBox populated, but was not sorted.   After searching on the web I found some who suggested to use the EntitSet.ToList() function to return a List object.  So I added a property to my CGManager object:

  public partial class CGManager: InvestBase

  {

    public List BiographyList

    {

      get { return _CGBiographies.ToList(); }

    }

    ...

Then I changed the XAML so that the binding expression in the CollectionViewSource element referenced this property instead of the CGBiographies EntitySet and suddenly my sorting worked!  Only problem is that the List collection does not provide change notification so the ListBox will not automatically update when items are added/removed from the EntitySet.  Defeat was snatched from the jaws of victory. This is a major problem and one that needed to be solved.  Was there a way to sort these frustrating EntitSets?  

The Answer: No.

Okay, "no" for the EntitySet, but that is fortunately not the end of the story.  The EntitySet has a method GetNewBindingList() that returns an IBindingList collection.  As it turns out, this interface provides you with everything you need to not only sort, but to notify your UIElements of changes to the underlying collection.  Here is my new BiographyList property:

  public partial class CGManager: InvestBase

  {

    private BindingList<CGBiography> _BiographyList;

    public BindingList<CGBiography> BiographyList

    {

      get {

        if (_BiographyList == null)

        {

          PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(new CGBiography());

          PropertyDescriptor myProp = properties.Find("Name", true);

          IBindingList bl = (IBindingList)_CGBiographies.GetNewBindingList();

          bl.ApplySort(myProp, ListSortDirection.Ascending);

          _BiographyList = (BindingList<CGBiography>)bl;

        }

        return _BiographyList; }

    }

    ...

After adding this I completely removed the CollectionViewSource element from my XAML and bound the ListBox.ItemSource property directly to the BiographyList property.  Now the sorting happens and if you remove an item from the BiographyList, the ListBox is updated automatically.  This may have some other bumps that I haven't discovered yet, but I have confirmed that the underlying EntitySet is indeed updated through the BindingList.  I haven't spent any time trying to streamline the whole PropertyDescriptor thing, so there is probably a way to get that in a more direct manner, but I'll save that joy for another day.

I have to say, this seems like a lot of code for what should have been a much simpler task, and I am still looking for a better way to do this.  If you have one, please let me know!

PS: ObservableCollection

How about ObservableCollections?  You can create one from an EntitySet using a statement like:

_BiographyList = new ObservableCollection<CGBiography>(CGBiographies);

This results in a collection that can not only be sorted using the above XAML, but will also notify the ListBox of additions and deletions.  Unfortunately, the link between it and the EntitySet is non-existant and so additions and deletions will not be reflected in the EntitySet as they are when using the BindingList.