左右值无限级分类 MVC + EntityFramework 的简单实现

　　在度娘上查了大半个月的资料，最后发现每个网友分享的文章都有一定的错误（PS：大家是故意的么？）。最后是在看了一个ASP版本后知道了大概流程；看了一个存储过程实现的文章后知道了大概需要的功能；看了一个SQL语句看到了比较直观的实现；看了一个php示例后才知道最复杂的一个功能的实现。每篇文章都是网友们自己正在使用或已经测试通过的，但都很巧，恰好关键地方就有点出错，不过还是感谢这些资料了，不然我也搞不出来。

　　因为查了很久的资料，基本上没有可用的，所以一直不敢直接写到项目的DAL里面，就直接在控制器里面写方法进行了调试，暂时没考虑什么意外情况和效率问题，毕竟出现上述问题的前提条件是你这个功能要实现吧，呵呵，下面直接上代码（PS：有点丑，见谅）。

  1 　　　　　　//数据初始化，反正失败了几十次，所以就在前面写了段初始化的代码，经常用到
  2             cManager.DelByCondition(c => c.Id != 0);
  3             Models.CmsMiee_Article_Category cate1 = new Models.CmsMiee_Article_Category() { Name = "商品", ClassLeftNum = 1, ClassRightNum = 18, ListSkin = "test", ContentSkin = "text" };
  4             Models.CmsMiee_Article_Category cate2 = new Models.CmsMiee_Article_Category() { Name = "食品", ClassLeftNum = 2, ClassRightNum = 11, ListSkin = "test", ContentSkin = "text" };
  5             Models.CmsMiee_Article_Category cate3 = new Models.CmsMiee_Article_Category() { Name = "肉类", ClassLeftNum = 3, ClassRightNum = 6, ListSkin = "test", ContentSkin = "text" };
  6             Models.CmsMiee_Article_Category cate4 = new Models.CmsMiee_Article_Category() { Name = "猪肉", ClassLeftNum = 4, ClassRightNum = 5, ListSkin = "test", ContentSkin = "text" };
  7             Models.CmsMiee_Article_Category cate5 = new Models.CmsMiee_Article_Category() { Name = "蔬菜类", ClassLeftNum = 7, ClassRightNum = 10, ListSkin = "test", ContentSkin = "text" };
  8             Models.CmsMiee_Article_Category cate6 = new Models.CmsMiee_Article_Category() { Name = "白菜", ClassLeftNum = 8, ClassRightNum = 9, ListSkin = "test", ContentSkin = "text" };
  9             Models.CmsMiee_Article_Category cate7 = new Models.CmsMiee_Article_Category() { Name = "电器", ClassLeftNum = 12, ClassRightNum = 17, ListSkin = "test", ContentSkin = "text" };
 10             Models.CmsMiee_Article_Category cate8 = new Models.CmsMiee_Article_Category() { Name = "电视机", ClassLeftNum = 13, ClassRightNum = 14, ListSkin = "test", ContentSkin = "text" };
 11             Models.CmsMiee_Article_Category cate9 = new Models.CmsMiee_Article_Category() { Name = "电冰箱", ClassLeftNum = 15, ClassRightNum = 16, ListSkin = "test", ContentSkin = "text" };
 12             cManager.Add(cate1);
 13             cManager.Add(cate2);
 14             cManager.Add(cate3);
 15             cManager.Add(cate4);
 16             cManager.Add(cate5);
 17             cManager.Add(cate6);
 18             cManager.Add(cate7);
 19             cManager.Add(cate8);
 20             cManager.Add(cate9);
 21             return new MvcHtmlString("OK");
 22 
 23 　　　　　　　//整体数据，后面涉及一些基本操作所调用的实体有些就是用的这个
 24             long nodeId = 315;
 25             Models.CmsMiee_Article_Category cate = cManager.GetById(nodeId);
 26             //一、计算A节点的子节点数。 $num = ($AR - $AL -1)/2;
 27             return Json((cate.ClassRightNum - cate.ClassLeftNum - 1) / 2, JsonRequestBehavior.AllowGet);
 28 
 29 　　　　　　　//二、查找A节点的所有子节点。
 30             select * from tree where L > $AL and R < $AR order by L asc;
 31             List<Models.CmsMiee_Article_Category> childCate = cManager.GetListByCondition(c => c.ClassLeftNum > cate.ClassLeftNum && c.ClassRightNum < cate.ClassRightNum, c => c.ClassLeftNum);
 32             return Json(childCate, JsonRequestBehavior.AllowGet);
 33 
 34 　　　　　　　//三、查找A节点的所有父节点。
 35             select * from tree where L < $AL and R > $AR order by L desc;
 36             List<Models.CmsMiee_Article_Category> parentCate = cManager.GetListByCondition(c => c.ClassLeftNum < cate.ClassLeftNum && c.ClassRightNum > cate.ClassRightNum, c => c.Id);
 37             string r1 = "";
 38             foreach (Models.CmsMiee_Article_Category c in parentCate)
 39             {
 40                 r1 += "," + c.Name;
 41             }
 42             return new MvcHtmlString(r1);
 43 
 44 　　　　　　　//四、在A节点下增加子节点B，B作为最后一个子节点。
 45             update tree set L = L + 2 where L >= $AR; 
 46             update tree set R = R + 2 where R >= $AR; 
 47             insert into tree (name, L, R) values('B', $AR-1, $AR);
 48             List<Models.CmsMiee_Article_Category> update1 = cManager.GetListByCondition(c => c.ClassLeftNum >= cate.ClassRightNum, c => c.ClassLeftNum);
 49             List<Models.CmsMiee_Article_Category> update2 = cManager.GetListByCondition(c => c.ClassRightNum >= cate.ClassRightNum, c => c.ClassLeftNum);
 50             foreach (Models.CmsMiee_Article_Category c in update1)
 51             {
 52                 c.ClassLeftNum += 2;
 53                 cManager.Modify(c, "ClassLeftNum");
 54             }
 55             foreach (Models.CmsMiee_Article_Category c in update2)
 56             {
 57                 c.ClassRightNum += 2;
 58                 cManager.Modify(c, "ClassRightNum");
 59             }
 60             Models.CmsMiee_Article_Category newClass = new Models.CmsMiee_Article_Category() { Name = "北极企鹅", ClassLeftNum = cate.ClassRightNum - 2, ClassRightNum = cate.ClassRightNum - 1, ListSkin = "test", ContentSkin = "text" };
 61             cManager.Add(newClass);
 62             return Json("OK", JsonRequestBehavior.AllowGet);
 63 
 64 　　　　　　　//五、输出节点下的所有子节点，并提供层级等信息以便生成节点树
 65             List<Models.CmsMiee_Article_Category> childCate = cManager.GetListByCondition(c => c.ClassLeftNum >= cate.ClassLeftNum && c.ClassRightNum <= cate.ClassRightNum, c => c.ClassRightNum);
 66             List<Models.CmsMiee_Article_Category_Layer> cateList = new List<Models.CmsMiee_Article_Category_Layer>();
 67             foreach (Models.CmsMiee_Article_Category c in childCate)
 68             {
 69                 Models.CmsMiee_Article_Category_Layer newCate = new Models.CmsMiee_Article_Category_Layer();
 70                 newCate.Id = c.Id;
 71                 newCate.Name = c.Name;
 72                 newCate.ClassLeftNum = c.ClassLeftNum;
 73                 newCate.ClassRightNum = c.ClassRightNum;
 74                 newCate.ListSkin = c.ListSkin;
 75                 newCate.ContentSkin = c.ContentSkin;
 76                 newCate.AddTime = c.AddTime;
 77                 newCate.DelTime = c.DelTime;
 78                 newCate.IsDeleted = c.IsDeleted;
 79                 newCate.Layer = cManager.GetListByCondition(c1 => c1.ClassLeftNum <= newCate.ClassLeftNum && c1.ClassRightNum >= newCate.ClassRightNum, c1 => c.ClassLeftNum).Count;
 80                 cateList.Add(newCate);
 81             }
 82             //拼接节点树
 83             string tree = "";
 84             foreach (Models.CmsMiee_Article_Category_Layer cl in cateList)
 85             {
 86                 string tab = "";
 87                 //根据层级生成制表符
 88                 for (int index = 0; index < cl.Layer; index++) { tab += "  "; }
 89                 tree += tab + cl.Name + " - 层级：" + cl.Layer + "\n";
 90             }
 91             MvcHtmlString result = new MvcHtmlString("<pre>" + tree + "</pre>");
 92             return result;
 93 
 94 　　　　　　　//六、删除A节点。先要计出该节点及其所有子节点所占的左右值空间，将这些节点删掉，然后更新其它节点的左右值。
 95             $num = $AR - $AL + 1; 
 96             delete from tree where L >= $AL and R <= $AR;
 97             update tree set R = R - $num where R > $AR; 
 98             update tree set  = L - $num where L > $AR;
 99             long cateLAR = cate.ClassRightNum - cate.ClassLeftNum + 1;
100             cManager.DelByCondition(c => c.ClassLeftNum >= cate.ClassLeftNum && c.ClassRightNum <= cate.ClassRightNum);
101             List<Models.CmsMiee_Article_Category> update1 = cManager.GetListByCondition(c => c.ClassRightNum > cate.ClassRightNum, c => c.ClassRightNum);
102             List<Models.CmsMiee_Article_Category> update2 = cManager.GetListByCondition(c => c.ClassLeftNum > cate.ClassRightNum, c => c.ClassRightNum);
103             foreach (Models.CmsMiee_Article_Category c in update1)
104             {
105                 c.ClassRightNum -= cateLAR;
106                 cManager.Modify(c, "ClassRightNum");
107             }
108             foreach (Models.CmsMiee_Article_Category c in update2)
109             {
110                 c.ClassLeftNum -= cateLAR;
111                 cManager.Modify(c, "ClassLeftNum");
112             }
113             MvcHtmlString result = new MvcHtmlString("OK");
114             return result;
115 
116 　　　　　　　//七、分类的父级修改
117             //1.如果目标节点的ClassRightNum小于当前节点的ClassRightNum；并且目标节点的ClassLeftNum大于当前节点的ClassLeftNum，则返回第一个错误：
118             long selfCategoryId = 316;   //要移动的节点
119             long newCategoryId = 321;    //目标节点
120             Models.CmsMiee_Article_Category selfCategory = cManager.GetById(selfCategoryId);
121             Models.CmsMiee_Article_Category newCategory = cManager.GetById(newCategoryId);
122             if (selfCategory == null) return new MvcHtmlString("当前所操作的分类不存在！");
123             if (newCategory == null) return new MvcHtmlString("您选择的新分类不存在！");
124             long selfLeft = selfCategory.ClassLeftNum;
125             long selfRight = selfCategory.ClassRightNum;
126             long value = selfRight - selfLeft;
127             //取得该分类下面的所有分类，包括自身
128             List<Models.CmsMiee_Article_Category> selfCategories = cManager.GetListByCondition(c => c.ClassLeftNum >= selfLeft && c.ClassRightNum <= selfRight, c => c.Id);
129             //将所有分类的ID写入数组以便更新左右值
130             long[] selfCategoryIDS = new long[selfCategories.Count];
131             for (int index = 0; index < selfCategoryIDS.Length; index++)
132             {
133                 selfCategoryIDS[index] = Convert.ToInt32(selfCategories[index].Id);
134             }
135             //将所有子类及自身的id组成字符串，逗号分隔
136             string inIDS = string.Join(",", selfCategoryIDS);
137             long parentLeft = newCategory.ClassLeftNum;
138             long parentRight = newCategory.ClassRightNum;
139             //读取新父级分类的所有父级分类及其层级
140             List<Models.CmsMiee_Article_Category> newParentCate1 = cManager.GetListByCondition(c => c.ClassLeftNum < newCategory.ClassLeftNum && c.ClassRightNum > newCategory.ClassRightNum, c => c.Id);
141             List<Models.CmsMiee_Article_Category_Layer> newParentCate = new List<Models.CmsMiee_Article_Category_Layer>();
142             foreach (Models.CmsMiee_Article_Category c in newParentCate1)
143             {
144                 Models.CmsMiee_Article_Category_Layer newCate = new Models.CmsMiee_Article_Category_Layer();
145                 newCate.Id = c.Id;
146                 newCate.Name = c.Name;
147                 newCate.ClassLeftNum = c.ClassLeftNum;
148                 newCate.ClassRightNum = c.ClassRightNum;
149                 newCate.ListSkin = c.ListSkin;
150                 newCate.ContentSkin = c.ContentSkin;
151                 newCate.AddTime = c.AddTime;
152                 newCate.DelTime = c.DelTime;
153                 newCate.IsDeleted = c.IsDeleted;
154                 newCate.Layer = cManager.GetListByCondition(c1 => c1.ClassLeftNum <= newCate.ClassLeftNum && c1.ClassRightNum >= newCate.ClassRightNum, c1 => c.ClassLeftNum).Count;
155                 newParentCate.Add(newCate);
156             }
157             //判断是前移还是后移，并更新数据库
158             if (parentRight > selfRight)
159             {
160                 //后移
161                 //更新左边值
162                 //update CmsMiee_Article_Category set ClassLeftNum=ClassLeftNum-value-1 where ClassLeftNum>selfRight and ClassRightNum<=parentRight
163                 List<Models.CmsMiee_Article_Category> tempCategories = cManager.GetListByCondition(c => c.ClassLeftNum > selfRight && c.ClassRightNum <= parentRight, c => c.Id);
164                 foreach (Models.CmsMiee_Article_Category c in tempCategories)
165                 {
166                     c.ClassLeftNum = c.ClassLeftNum - value - 1;
167                     cManager.Modify(c, "ClassLeftNum");
168                 }
169                 //更新新父类的父类的左边值
170                 foreach (Models.CmsMiee_Article_Category_Layer c in newParentCate)
171                 {
172                     if (c.Layer == 1) continue;
173                     c.ClassLeftNum = c.ClassLeftNum - value - 1;
174                     //转换为不带层级的分类后更新到数据库中
175                     Models.CmsMiee_Article_Category result = cManager.GetById(c.Id);
176                     result.ClassLeftNum = c.ClassLeftNum;
177                     cManager.Modify(result, "ClassLeftNum");
178                 }
179                 //update CmsMiee_Article_Category set ClassRightNum=ClassRightNum-value-1 where ClassRightNum>selfRight and ClassRightNum<parentRight
180                 //更新右边值
181                 tempCategories = cManager.GetListByCondition(c => c.ClassRightNum > selfRight && c.ClassRightNum < parentRight, c => c.Id);
182                 foreach (Models.CmsMiee_Article_Category c in tempCategories)
183                 {
184                     c.ClassRightNum = c.ClassRightNum - value - 1;
185                     cManager.Modify(c, "ClassRightNum");
186                 }
187                 long tempValue = parentRight - selfRight - 1;
188                 tempCategories = cManager.GetListByCondition(c => selfCategoryIDS.Contains(c.Id), c => c.Id);
189                 //update CmsMiee_Article_Category set ClassLeftNum=ClassLeftNum+tempValue,ClassRightNum=ClassRightNum+tempValue where Id in(selfCategoryIDS)
190                 foreach (Models.CmsMiee_Article_Category c in tempCategories)
191                 {
192                     c.ClassLeftNum = c.ClassLeftNum + tempValue;
193                     c.ClassRightNum = c.ClassRightNum + tempValue;
194                     cManager.Modify(c, "ClassLeftNum", "ClassRightNum");
195                 }
196             }
197             else
198             {
199                 //前移
200                 List<Models.CmsMiee_Article_Category> tempCategories = cManager.GetListByCondition(c => c.ClassLeftNum > parentRight && c.ClassLeftNum < selfLeft, c => c.Id);
201                 //更新左边值
202                 foreach (Models.CmsMiee_Article_Category c in tempCategories)
203                 {
204                     c.ClassLeftNum = c.ClassLeftNum + value + 1;
205                     cManager.Modify(c, "ClassLeftNum");
206                 }
207                 //更新右边值
208                 tempCategories = cManager.GetListByCondition(c => c.ClassRightNum >= parentRight && c.ClassRightNum < selfLeft, c => c.Id);
209                 foreach (Models.CmsMiee_Article_Category c in tempCategories)
210                 {
211                     c.ClassRightNum = c.ClassRightNum + value + 1;
212                     cManager.Modify(c, "ClassRightNum");
213                 }
214                 long tempValue = selfLeft - parentRight;
215                 tempCategories = cManager.GetListByCondition(c => selfCategoryIDS.Contains(c.Id), c => c.Id);
216                 foreach (Models.CmsMiee_Article_Category c in tempCategories)
217                 {
218                     c.ClassLeftNum = c.ClassLeftNum - tempValue;
219                     c.ClassRightNum = c.ClassRightNum - tempValue;
220                     cManager.Modify(c, "ClassLeftNum", "ClassRightNum");
221                 }
222             }
223             return new MvcHtmlString("操作完成！");

　　估计是特别乱了，昨天整理了好久，总算是放到DAL里面去了，最后一个修改的功能，步骤稍微有点繁琐。为以防万一，我用的是事务，反正很简单，SaveChange你懂的》。。。

　　变量里面用到的cManager对象是BLL中管理分类用的类，没有什么特别，只是继承了父类的增删改查功能。

　　这段代码只是我测试、学习的时候用到的，后来改动了很多地方，不过关于数据库操作和无限级分类这一块，基本上没什么改动了。没这么无私，所以懒得整理了（PS：我要是像大神们那样整理，估计代码中也会“恰巧”出现一点点关键地方的错误了，呵呵）。所以需要用的朋友可以自行整理一下。

相关阅读:
自制凉皮
 牛人
 史记
 阅读detection
最近的购书清单
 不要轻易挑战用户的习惯，否则会被用户打脸！
INVEST原则的应用
 谈谈Backlog梳理活动
 要写封闭式的用户故事
 敏捷教练的八种失败角色
原文地址：https://www.cnblogs.com/jiangkun/p/Infinite_Class_MVC_EntityFramework.html