Custom sort-order with SQL-Server and .NET Entity-Framework

Question

Hello!

I have a table I store contacts and their phones.
Contact: ContactId (int, PK), FirstName (varchar), LastName (varchar)
Phone: PhoneId (int, PK), ContactId(int FK), Number (varchar), SortOrder (tinyint)

I want that under each contact, the user should be able to maintain the priority of the phones, meaning that the SortOrder column of phone should be under each ContactId by consecutive numbers.

For exmple, when adding the first phone to a user, its SortOrder should become 1, for the second 2 and so on.

Say the user added 5 phone to a user, now he wants to move phone (with order) 3 to place 2, it should push current 2 to place 3 and vice versa.

If he want to make phone 1 to last it should 'pull' all the phones' SortOrder by -1, then assign previous 1 to 5.

I want this system to be consistent and not to have open edges where I have:

• 2 phones with identical sort-order values in one group
• First phone in group with value higher than 1
• Last phone in group with value higher or smaller than amount of phones in group
• etc. etc. etc.

I think I am pretty clear so far (am I).

Now my question is, considering I retrieve the data with .NET EF in a desktop app, and I will be accessing the phones using the Contact.Phones navigation property.

Should implement this system in the DAL or in server AFTER INSERT, UPDATE trigger?
I personally think this has to be implemented on the server, cuz imagine different users play around with the same contact-phones at the same time, this might break the consistency, huh?

Any tips, links, code, advice jokes regarding this custom-sorting issue will be welcommed.

I also thought about ROWNUMBER() as a good idea of retrieving the data, but I need to maintain the sorting, not only selecting it.

Notes:
In general I asked the question in continuation of this answer, I decided to use, the contact-phone example was just to make things simple. I've found this post discussing my issue but no technical advise was provided there.

Thanks in advance.

Answer 1

Your square pegging a round hole here.

Your answer CAN be as easy as Contact.Phones.OrderBy ( @p => @p.SortOrder ) then taking care of all of your phones in a Contact.SetPhone( phone, order ) method which can encompass whatever custom logic you wish.

Also since this is business and view logic your persistence store shouldn't be aware of whats going on.