Using Entity Framework, how do I specify a sort on a navagation property?

Question

I have two tables: [Category], [Item]. They are connected by a join table: [CategoryAndItem]. It has two primary key fields: [CategoryKey], [ItemKey]. Foreign keys exist appropriately and Entity has no problem pulling this in and creating the correct navigation properties that connect the entity objects.

Basically each category can have multiple items, and items can be in multiple categories. The problem is that the order of items is specified per category, so that a particular item might be third in one category but fifth in another.

In the past, I have added a [Sequence] field to the join table and modified the stored procedure to handle it. But since Entity is replacing my stored procedures, I need to figure out how to make Entity handle the sequence.

Any suggestions?

Answer 1

First option: use a stored procedure. You can still use stored procedures with the Entity Framework, and EF4 makes this easier than the original EF3 implementation did.

Second option: handle it manually. Will you know at insert-time what Sequence order the CategoryAndItem record should get? If so, it could be as simple as setting that Entity's Sequence property to 1 + (From item as CategoryAndItem in model.CategoryAndItem select item.Sequence where item.Category = ... ).Max(). (VB code, sorry :)).

Answer 2

EntityCollections are unordered. You can read about this here. The workaround, when you need an ordered list, is to project onto a POCO presentation model or DTO:

var q = from c in Context.Categories
        select new CategoryPresentation
        {
            Key = c.Key,
            Items = from ci in c.CategoryAndItems
                    orderby ci.SequenceNo
                    select new ItemPresentation
                    {
                        Key = ci.Item.Key,
                        // etc.
                    },
             // etc.
        };