c# SortedList的妙用 （GroupBy）

         公司做一个药品监控系统(web)，目前负责对监控问题添加屏蔽规则功能。由于药品问题要分成 问题级别 - 问题类型 - 问题具体结果类型 三层级别，以这三个级别，分成大组，再分成小组。由于本人烦于写存储过程，在sql 中使用语句频繁查询，性能又不好。于是想到利用
SortedList的自动排序和分组的功能，做了一个模板。觉得挺好用的，分享一下！

       数据结构如下：

namespace CRMS.Web.Query.ProblemPrescription
{
    public partial class ProblemDetail : CRMS.Web.Base.BasePage
    {
        // 由于datarow的构造函数是protected，这里巧妙地构造自定义table, 可以获取自定义datarow
        private DataTable simpleTable = null;
        public DataTable SimpleTable
        {
            set { this.simpleTable = value; }
            get
            {
                if (simpleTable == null)
                {
                    simpleTable = new DataTable();
                    System.Type stringType = System.Type.GetType("System.String");
                    simpleTable.Columns.Add(new DataColumn("cli_lvl_code", stringType));
                    simpleTable.Columns.Add(new DataColumn("cli_lvl_name", stringType));
                    simpleTable.Columns.Add(new DataColumn("cli_title_code", stringType));
                    simpleTable.Columns.Add(new DataColumn("cli_title_name", stringType));
                    simpleTable.Columns.Add(new DataColumn("id", stringType)); // 以 "id" 代替 "cli_analyze_title"
                }
                return this.simpleTable;
            }
        }


        private result_data result_data;
        public result_data Result_data
        {
            set { this.result_data = value; }
            get { return this.result_data; }
        }


        public partial class result_data
        {
            private result_level[] result_levelField;
            public result_level[] result_level
            {
                get { return this.result_levelField; }
                set { this.result_levelField = value; }
            }
            private string nameField;
            [System.Xml.Serialization.XmlAttributeAttribute("name")]
            public string name
            {
                get { return this.nameField; }
                set { this.nameField = value; }
            }
        }


        public partial class result_level
        {
            private string nameField;
            [System.Xml.Serialization.XmlAttributeAttribute()]
            public string name
            {
                get { return this.nameField; }
                set { this.nameField = value; }
            }
            private result_title[] result_titleField;
            [System.Xml.Serialization.XmlElementAttribute("result_title", Form = System.Xml.Schema.XmlSchemaForm.Unqualified)]
            public result_title[] result_title
            {
                get { return this.result_titleField; }
                set { this.result_titleField = value; }
            }
        }


        public partial class result_title
        {
            private string nameField;
            public string name
            {
                get { return this.nameField; }
                set { this.nameField = value; }
            }
            private result[] resultField;
            public result[] result
            {
                get { return this.resultField; }
                set { this.resultField = value; }
            }
        }


        [System.CodeDom.Compiler.GeneratedCodeAttribute("xsd", "4.0.30319.1")]
        [System.SerializableAttribute()]
        [System.Diagnostics.DebuggerStepThroughAttribute()]
        [System.ComponentModel.DesignerCategoryAttribute("code")]
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
        public partial class result
        {
            private title titleFiled;
            [System.Xml.Serialization.XmlElementAttribute("title", Form = System.Xml.Schema.XmlSchemaForm.Unqualified)]
            public title Title
            {
                get { return this.titleFiled; }
                set { this.titleFiled = value; }
            }

            private detail detailFiled;
            [System.Xml.Serialization.XmlElementAttribute("detail", Form = System.Xml.Schema.XmlSchemaForm.Unqualified)]
            public detail Detail
            {
                get { return this.detailFiled; }
                set { this.detailFiled = value; }
            }

            private reference referenceFiled;
            [System.Xml.Serialization.XmlElementAttribute("reference", Form = System.Xml.Schema.XmlSchemaForm.Unqualified)]
            public reference Reference
            {
                get { return this.referenceFiled; }
                set { this.referenceFiled = value; }
            }

            public string mediA_hiscode;
            public string mediB_hiscode;
            public string result_type;
            public string result_id;
        }


        public partial class title
        {
            private string nameField;
            private string valueField;
            public string Name
            {
                get { return this.nameField; }
                set { this.nameField = value; }
            }
            public string Value
            {
                get { return this.valueField; }
                set { this.valueField = value; }
            }
        }


        public partial class detail
        {
            private string valueField;
            public string Value
            {
                get { return this.valueField; }
                set { this.valueField = value; }
            }

        }


        [System.CodeDom.Compiler.GeneratedCodeAttribute("xsd", "4.0.30319.1")]
        [System.SerializableAttribute()]
        [System.Diagnostics.DebuggerStepThroughAttribute()]
        [System.ComponentModel.DesignerCategoryAttribute("code")]
        [System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]
        public partial class reference
        {
            private string referenceField;
            [System.Xml.Serialization.XmlTextAttribute()]
            public string Value
            {
                get { return this.referenceField; }
                set { this.referenceField = value; }
            }
        }

    }// class ProblemDetail


    // 各个比较器和等价器
    public class lvl_title_id_comparer : IComparer<DataRow>
    {
        public int Compare(DataRow x, DataRow y)
        {
            int res = string.Compare(x["cli_lvl_code"].ToString(), y["cli_lvl_code"].ToString());
            if (res != 0)
            {
                return res;
            }
            else
            {
                res = string.Compare(x["cli_title_code"].ToString(), y["cli_title_code"].ToString());
                if (res != 0)
                {
                    return res;
                }
                else
                {
                    return string.Compare(x["id"].ToString(), y["id"].ToString());
                }
            }
        }
    }


    public class lvl_title_comparer : IComparer<DataRow>
    {
        public int Compare(DataRow x, DataRow y)
        {
            int res = string.Compare(x["cli_lvl_code"].ToString(), y["cli_lvl_code"].ToString());
            if (res != 0)
            {
                return res;
            }
            else
            {
                return string.Compare(x["cli_title_code"].ToString(), y["cli_title_code"].ToString());
            }
        }
    }


    public class lvl_comparer : IComparer<DataRow>
    {
        public int Compare(DataRow x, DataRow y)
        {
            return string.Compare(x["cli_lvl_code"].ToString(), y["cli_lvl_code"].ToString());
        }
    }


    public class lvl_title_equalitycomparer : IEqualityComparer<DataRow>
    {
        public bool Equals(DataRow x, DataRow y)
        {
            return x["cli_lvl_code"].ToString() == y["cli_lvl_code"].ToString() &&
                   x["cli_title_code"].ToString() == y["cli_title_code"].ToString();
        }
        public int GetHashCode(DataRow obj)
        {
            return obj.ToString().GetHashCode();
        }
    }


    public class lvl_equalitycomparer : IEqualityComparer<DataRow>
    {
        public bool Equals(DataRow x, DataRow y)
        {
            return x["cli_lvl_code"].ToString() == y["cli_lvl_code"].ToString();
        }
        public int GetHashCode(DataRow obj)
        {
            return obj.ToString().GetHashCode();
        }
    }
}

具体实现函数：

        /// <summary>
        /// 获取问题类型、问题标题
        /// </summary>
        protected void GetProLvl()
        {
            UserParamsClass user = Session["user"] as UserParamsClass;
            string hosp_code = "";
            if (user != null)
            {
                hosp_code = user.strAreaCode;
            }

            if (hosp_code == "")
                hosp_code = "%";

            string strSQL = string.Empty;
            string strTmp = string.Empty;

            // 查询问题结果详细信息
            strTmp =
@"SELECT a.id 
      ,a.cli_lvl_code
      ,a.cli_title_code
      ,a.cli_analyze_title
      ,a.cli_analyze_reference
      ,a.cli_analyze_addition
      ,a.encode_type
      ,c.sys_name as cli_lvl_name
      ,d.sys_name as cli_title_name
      ,b.sys_name as cli_result_name
      ,{0} as is_op_ip
  FROM {1} as a
  left join comh_syslang as b on a.cli_result_code=b.sys_code 
  left join comh_syslang as c on a.cli_lvl_code=c.sys_code
  left join comh_syslang as d on a.cli_title_code=d.sys_code
  where a.acquisition_time='{2}' and a.hos_hosp_code like '{3}' and a.cli_adm_no='{4}' 
        and a.source_type=1 and a.is_del=0 
        and not exists 
        (
            select distinct b.id from homh_user_analyze_rules_req as b
			  where a.source_type=1 and a.source_type=b.source_type and a.cli_drug_code_a=b.edt_item1 and isnull(a.cli_drug_code_b,'')=isnull(b.edt_item2,'') 
				    and a.cli_result_type=b.edt_analyze_type and b.is_op_ip in({0},3) and b.status=1 and a.hos_hosp_code=b.hos_hosp_code
        ) 
  order by a.cli_lvl_code, a.cli_title_code";

            if (Op_Ip.Value == "门诊" || Op_Ip.Value == "急诊")
            {
                strSQL = string.Format(strTmp, "1", "pctd_analysis_result_detail_op", acquisition_time, hosp_code, hos_adm_no);
            }
            else if (Op_Ip.Value == "住院")
            {
                strSQL = string.Format(strTmp, "2", "pctd_analysis_result_detail_ip", acquisition_time, hosp_code, hos_adm_no);
            }
            DataTable dt = helper.ExecuteQuery(strSQL);

            // 由于不想把多余的数据塞入result_level, 只能把datarow和result_level 弄成 KeyValuePair （数据-值对）形式
            SortedList<DataRow, result_level> levelList = new SortedList<DataRow, result_level>(new lvl_comparer());
            SortedList<DataRow, result_title> titleList = new SortedList<DataRow, result_title>(new lvl_title_comparer());
            SortedList<DataRow, result> resultList = new SortedList<DataRow, result>(new lvl_title_id_comparer());

            // 初始化所有result class
            foreach (DataRow row in dt.Rows)
            {
                DataRow simpleRow = SimpleTable.NewRow();
                simpleRow["cli_lvl_code"] = row["cli_lvl_code"];
                simpleRow["cli_lvl_name"] = row["cli_lvl_name"];
                simpleRow["cli_title_code"] = row["cli_title_code"];
                simpleRow["cli_title_name"] = row["cli_title_name"];
                simpleRow["id"] = row["id"];    // 其它项都有可能相同，cli_analyze_title 会不同，但 cli_analyze_title 太长，换成以 "id" 为主键

                resultList.Add(
                    simpleRow,
                    new result()
                    {
                        Title = new title()
                        {
                            Value = model.deEncry(row["cli_analyze_title"].ToString()/*, Convert.ToInt32(row["encode_type"])*/)
                        },
                        Detail = new detail()
                        {
                            Value = model.deEncry(row["cli_analyze_addition"].ToString()/*, Convert.ToInt32(row["encode_type"])*/)
                        },
                        Reference = new reference()
                        {
                            Value = model.deEncry(row["cli_analyze_reference"].ToString()/*, Convert.ToInt32(row["encode_type"])*/)
                        },
                        result_id = simpleRow["id"].ToString()
                    }
                );
            }

            // 对result object 按 (cli_lvl_code - cli_title_code) 分组, 装箱到 titleList
            IEnumerable<IGrouping<DataRow, KeyValuePair<DataRow, result>>> resultClique =
                resultList.GroupBy<KeyValuePair<DataRow, result>, DataRow>(
                    keyValuePair => keyValuePair.Key,
                    new lvl_title_equalitycomparer()
                );

            foreach (IGrouping<DataRow, KeyValuePair<DataRow, result>> resultGroup in resultClique)
            {
                result_title title = new result_title()
                {
                    name = resultGroup.First().Key["cli_title_name"].ToString(),
                    result = new result[resultGroup.Count()]
                };

                int i = 0;
                foreach (KeyValuePair<DataRow, result> resultMember in resultGroup)
                {
                    title.result[i] = resultMember.Value;
                    i++;
                }

                titleList.Add(resultGroup.First().Key, title);
            }

            // 对result_title object 按 (cli_lvl_code) 分组, 装箱到 levelList
            IEnumerable<IGrouping<DataRow, KeyValuePair<DataRow, result_title>>> titleClique =
                titleList.GroupBy<KeyValuePair<DataRow, result_title>, DataRow>(
                    keyValuePair => keyValuePair.Key,
                    new lvl_equalitycomparer()
                );

            foreach (IGrouping<DataRow, KeyValuePair<DataRow, result_title>> titleGroup in titleClique)
            {
                result_level level = new result_level()
                {
                    name = titleGroup.First().Key["cli_lvl_name"].ToString(),
                    result_title = new result_title[titleGroup.Count()]
                };

                int i = 0;
                foreach (KeyValuePair<DataRow, result_title> titleMember in titleGroup)
                {
                    level.result_title[i] = titleMember.Value;
                    i++;
                }

                levelList.Add(titleGroup.First().Key, level);
            }

            // 生成 result_data class, 它最后赋值给网页供显示
            this.Result_data = new result_data()
            {
                name = "问题结果",
                result_level = new result_level[levelList.Count]
            };

            int j = 0;
            foreach (KeyValuePair<DataRow, result_level> levelMember in levelList)
            {
                this.Result_data.result_level[j] = levelMember.Value;
                j++;
            }
        }

数据库表结构：

pctd_analysis_result_detail_op 表

homh_user_analyze_rules_req 表

实现效果图：
本次要屏蔽规则:
氨茶碱注射液≡≡维生素C注射液存在配伍禁忌
问题结果对应 result_data,
问题级别（重要警示，一般提示，其它信息）对应 result_level
问题标题(氨茶碱注射液≡≡维生素C注射液存在配伍禁忌)对应cli_title_name(cli_analyze_title， id 也可以)

在“屏蔽规则管理”中“批准”该屏蔽规则

批准屏蔽后，可以看到不会再显示
氨茶碱注射液≡≡维生素C注射液存在配伍禁忌

参考例子：

http://msdn.microsoft.com/zh-cn/library/vstudio/bb534501.aspx

http://msdn.microsoft.com/zh-cn/library/vstudio/bb549393.aspx

            class Pet
            {
                public string Name { get; set; }
                public double Age { get; set; }
            }

            public static void GroupByEx3()
            {
                // Create a list of pets.
                List<Pet> petsList =
                    new List<Pet>{ new Pet { Name="Barley", Age=8.3 },
                                   new Pet { Name="Boots", Age=4.9 },
                                   new Pet { Name="Whiskers", Age=1.5 },
                                   new Pet { Name="Daisy", Age=4.3 } };

                // Group Pet objects by the Math.Floor of their age.
                // Then project an anonymous type from each group
                // that consists of the key, the count of the group's
                // elements, and the minimum and maximum age in the group.
                var query = petsList.GroupBy(
                    pet => Math.Floor(pet.Age),
                    (age, pets) => new
                    {
                        Key = age,
                        Count = pets.Count(),
                        Min = pets.Min(pet => pet.Age),
                        Max = pets.Max(pet => pet.Age)
                    });

                // Iterate over each anonymous type.
                foreach (var result in query)
                {
                    Console.WriteLine("\nAge group: " + result.Key);
                    Console.WriteLine("Number of pets in this age group: " + result.Count);
                    Console.WriteLine("Minimum age: " + result.Min);
                    Console.WriteLine("Maximum age: " + result.Max);
                }

                /*  This code produces the following output:

                    Age group: 8
                    Number of pets in this age group: 1
                    Minimum age: 8.3
                    Maximum age: 8.3

                    Age group: 4
                    Number of pets in this age group: 2
                    Minimum age: 4.3
                    Maximum age: 4.9

                    Age group: 1
                    Number of pets in this age group: 1
                    Minimum age: 1.5
                    Maximum age: 1.5
                */
            }