none
Association order RRS feed

  • Question

  • Hi,

    By default an association between two entities uses no order. For instance the association between the Categories table and the Products table (of the Northwind database) is carried out by the Products property of the Category class. The order of the referenced Product objects is not defined. It could be any order the database system uses. The SQL Server for instance does not order the records by their Primary Key.

    What I want to know is whether I can define the order of the N-side of an association. This would be very handy to avoid an explicit ordering every time the collection is iterated.


    Friday, March 7, 2008 11:49 PM

Answers

  • You can set the order for association properties using the LoadOptions property.

     

    MyDataContext dc = ...;

    DataLoadOptions options = new DataLoadOptions();

    options.AssociateWith<Category>(c =>c.Products.OrderBy(p => p.ProductName));

    dc.LoadOptions = options;

     

    In an AssociateWith setting you can specify OrderBy, Where, Take and Skip for 1:N associations.

     

     

     

     

     

    Tuesday, March 18, 2008 4:21 AM
    Moderator

All replies

  •  

    Databases such as SQL server do not order their results unless specifically asked to do so. The same is true of LINQ to SQL - for both requesting results from a table e.g.

    Code Snippet
    from p in db.Products select p

     

     

    There is no ordering done, unless you add a order by clause. Hence the same is true of association results.

     

    The basic logic is performance: sorting has a cost, and if the order isn't important then the database allows you to avoid having to sort it.

    Saturday, March 8, 2008 9:28 AM
    Answerer
  • Thanks for your reply, Howard,

    Clearly is sorting a time consuming process. But it would be handy to have the option to order an association in the case, that this order is used all over the application. I think in most cases the detail entities will be ordered by their primary key (for instance an integer ID). With the optional  ordering of an association one could not forget to sort the entities when used.
    Saturday, March 8, 2008 11:37 AM
  • As you are using an association, which is essentially a property of the table class, why not make your own version that is pre-sorted,.

     

    For example in Northwind, the Category class has a Products property:

    Code Snippet

    [Association(Name="Category_Product", Storage="_Products", OtherKey="CategoryID")]

    public EntitySet<Product> Products

    {

    get

    {

    return this._Products;

    }

    set

    {

    this._Products.Assign(value);

    }

    }

     

     

     

    You could try creating a pre-sorted version - however note that this returns an IOrderedEnumerable and not the EntitySet - and so it might not work in the same way

     

    Code Snippet

    [Association(Name = "Category_Product", Storage = "_Products", OtherKey = "CategoryID")]

    public IOrderedEnumerable<Product> ProductsSorted

    {

    get

    {

      return from p in this._Products orderby p.ProductName select p;

    }

    set

    {

    this._Products.Assign(value); // this might not work!

    }

    }

     

     

     

    Saturday, March 15, 2008 9:20 AM
    Answerer
  • You can set the order for association properties using the LoadOptions property.

     

    MyDataContext dc = ...;

    DataLoadOptions options = new DataLoadOptions();

    options.AssociateWith<Category>(c =>c.Products.OrderBy(p => p.ProductName));

    dc.LoadOptions = options;

     

    In an AssociateWith setting you can specify OrderBy, Where, Take and Skip for 1:N associations.

     

     

     

     

     

    Tuesday, March 18, 2008 4:21 AM
    Moderator