使用SQL Server和.NET实体框架的自定义排序顺序 [英] Custom sort-order with SQL-Server and .NET Entity-Framework
问题描述
我有一张表，用于存储联系人和他们的电话。
Contact ：ContactId（int，PK），FirstName（varchar），LastName（varchar）
Phone ：PhoneId（int，PK），ContactId（int FK），Number（varchar），SortOrder（tinyint）
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希望在每个联系人下，用户应该能够保持电话的优先级，这意味着电话的SortOrder列应在每个ContactId下，并带有连续的数字。
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.
例如，将第一个电话添加到用户时，其SortOrder应该为1，第二个为2，依此类推。
For exmple, when adding the first phone to a user, its SortOrder should become 1, for the second 2 and so on.
说用户向用户添加了5个电话，现在他想将手机（带订单）3移至位置2，则应将电流2移至位置3，反之亦然。
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.
如果他想制造电话1最后，它应该将-1的所有电话的SortOrder拉，然后将之前的1分配给5。
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:
- 组中第一部价值高于1的电话
- 组中具有值的最后一部电话高于或小于组中电话的数量
- 等。等等。
- 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).
现在我的问题是，考虑到我在桌面应用程序中使用.NET EF检索数据，并且我将使用Contact.Phones导航属性访问电话。
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.
应该在DAL中还是在服务器 AFTER INSERT，UPDATE 触发器中实现此系统？
我个人认为必须在服务器，因为想象不同的用户同时使用相同的联系电话，这可能会破坏一致性，是吗？
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.
我还认为ROWNUMBER（）是检索数据的一个好主意，但我需要保持排序，而不仅仅是选择它。
I also thought about ROWNUMBER() as a good idea of retrieving the data, but I need to maintain the sorting, not only selecting it.
注意：
一般而言，我问答案，这个联系电话示例只是为了使事情变得简单。
我发现此帖子
预先感谢。
推荐答案
您的正方形在此处固定一个圆孔。
Your square pegging a round hole here.
您的答案可以和Contact.Phones.OrderBy一样简单（@p => @ p.SortOrder）然后使用Contact.SetPhone（phone，order）方法处理所有电话，该方法可以包含您希望的任何自定义逻辑。
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.
这篇关于使用SQL Server和.NET实体框架的自定义排序顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
