CODEWITHSUNDEEP
c#entity-frameworkmany-to-manyself-reference
psy
psy

Reputation: 2809

Many to Many (self related) specific order entity framework

Hello I'm trying to do the impossible apparently.

I need a self referenced table with a many to many relationship to itself that also has a specific order in c# entity framework (4.2) database first.

Think of it like Friends having Friends in which they order their friendship > Best Friend to Worst Friend.

Is there anyway to do this without using the "FriendToFriend" relationship entity? I would like to be able to use Friend.Friends (removing the order column creates it), but I would have a default order based on their friendshipOrder. My work around is looking like extending the generated classes to have a new property for Friends in order.

Any one else have any better ideas?

Upvotes: 2

Views: 619

Answers (2)

Jamie Burns
Jamie Burns

Reputation: 1318

I know I'm late to this, but when designing this as a data model, I would prefer to add a relationship table, and that relationship table should have a property that defines the order (for example, worst friend is 0, best is 100).

Then, in EF, I would explicitly order by that property, if the list I'm retrieving should be of that order.

That means that whatever method you use to query the data, that relationship can be consistently used. So if you were using EF, you could use it (although, yes, it's not as handy as Friend.Friends, but the code would be clearer as to its intention - Friend.FriendRelationships.Select(p => p.Friend).OrderBy(p => p.OrderValue)), and if you were using direct SQL, then you could use it too.

If I came across Friend.Friends in code, I would have no idea what ordering would be applied to it.

If you must have it though, you could always add it as a non-db property -

public class Friend
{
  public virtual List<FriendRelationship> UnorderedFriendList { get; set; }

  [NotMapped]
  public IEnumerable<Friend> Friends
  {
    get
    {
       return UnorderedFriendList.Select(p => p.Friend).OrderByDescending(p => p.OrderValue);
    }
  }
}

Upvotes: 1

Eranga
Eranga

Reputation: 32447

Entity framework does not support ordered collections. This is one of many situations where EF shows its immaturity.

Try nHibernate if it is a viable option. It supports ordered collections.

With EF you will have to map the intermediate table with extra column and manually adjust the ordering according to your logic.

Upvotes: 2

Related Questions