/// <summary> /// 执行表连接查询,该方法可连接两个不同的表 /// </summary> /// <typeparam name="TModel1">要连接的数据库实体对象</typeparam> /// <typeparam name="TModel2">要连接的数据库实体对象</typeparam> /// <param name="alisName">主表的别名</param> /// <param name="unionAlisName">连接表的别名</param> /// <param name="unionType">连接类型</param> /// <param name="predicate">On 的查询表达式</param> /// <returns></returns> public QueryContext <T> Union <TModel1, TModel2>(string alisName, string unionAlisName, UnionType unionType, Expression <Func <TModel1, TModel2, bool> > predicate) { Type type = typeof(TModel1); var last = UnionList.Where(f => f.Model.Equals(type) && f.UnionAlisName == alisName).FirstOrDefault(); if (alisName != masterAlisName && last == null) { ExpressionUnionModel u2 = new ExpressionUnionModel { Model = typeof(TModel1), MasterType = typeof(T), Body = predicate.Body, UnionType = unionType, AlisName = alisName, UnionAlisName = alisName }; UnionList.Add(u2); } ExpressionUnionModel us = new ExpressionUnionModel { Model = typeof(TModel2), MasterType = typeof(TModel1), Body = predicate.Body, UnionType = unionType, AlisName = alisName, UnionAlisName = unionAlisName }; UnionList.Add(us); return(this); }
/// <summary> /// 设置查询排序条件和方向 /// </summary> /// <param name="keySelector">字段选择器</param> /// <param name="direction">排序方向,默认 ASC</param> /// <returns></returns> private QueryContext <T> OrderTransafer(Expression keySelector, string direction = "ASC") { MemberExpression exp = null; if (keySelector.NodeType == ExpressionType.Lambda) { exp = (MemberExpression)((LambdaExpression)keySelector).Body; } else { exp = (MemberExpression)keySelector; } string alisname = UnionList.FirstOrDefault(f => f.Model.Equals(exp.Member.DeclaringType))?.AlisName; if (!string.IsNullOrEmpty(alisname)) { alisname += "."; } else if (UnionList.Count > 0) { alisname = masterAlisName + "."; } OrderByText = $"ORDER BY {alisname}{exp.Member.Name} {direction}"; return(this); }
public void Should_work_properly_with_multiple_lists() { var first = new HList <uint> { 29905, 28722, 27102, 26286, 25340, 25043, 24896, 24220, 23764, 23530, 22636, 22611, 21644, 20639, 20311, 17854, 17698, 16742, 16500, 15971, 15946, 15890, 15702, 15060, 13737, 13313, 13011, 10628, 9138, 9000, 8289, 8082, 7127, 6791, 6480, 5919, 5014, 4805, 3759, 3025, 2894, 1459, 959, 667, 194 }; var second = new HList <uint> { 30000, 29927, 29886, 29576, 29566, 29469, 29155, 29027, 28774, 28700, 28698, 28528, 28387, 28335, 28301, 28233, 28162, 28100, 28088, 27750, 27701, 27539, 27384, 27342, 27333, 26992, 26843, 26706, 26670, 26639, 26344, 26127, 25827, 25696, 25642, 25318, 25068, 25050, 24983, 24979, 24755, 24521, 24296, 24127, 24044, 23949, 23869, 23838, 23574, 23502, 23442, 23274, 23098, 22854, 22642, 22624, 22203, 22139, 21865, 21573, 21488, 21334, 21315, 21140, 21064, 21045, 21022, 20999, 20989, 20951, 20936, 20917, 20591, 20425, 20397, 20332, 20203, 20200, 20170, 20046, 19935, 19779, 19605, 19322, 19313, 19285, 19258, 19082, 18928, 18778, 18682, 18571, 18500, 18433, 18242, 17970, 17863, 17803, 17604, 17495, 16843, 16723, 16663, 16645, 16611, 16343, 16317, 16159, 15745, 15580, 15485, 15186, 15172, 14915, 14859, 14854, 14642, 14558, 14324, 14235, 14005, 13975, 13877, 13865, 13837, 13782, 13780, 13705, 13618, 13569, 13379, 13339, 13311, 13269, 13189, 13086, 13083, 12704, 12591, 12522, 12521, 12499, 12456, 12323, 12287, 12152, 11899, 11457, 11320, 11194, 11144, 10798, 10731, 10519, 10475, 10408, 10384, 10383, 10319, 10130, 10104, 10066, 9763, 9735, 9330, 8604, 8180, 7892, 7750, 7565, 7537, 7518, 7516, 7356, 7312, 7239, 6884, 6434, 6219, 6144, 5992, 5881, 5764, 5482, 5310, 5135, 5132, 4728, 4683, 4241, 4218, 4136, 3907, 3880, 3798, 3689, 3667, 3550, 3496, 3486, 3131, 3117, 2794, 2630, 2483, 2353, 2331, 2262, 2020, 1348, 1283, 1169, 1022, 858, 735, 694, 444, 61 }; var third = new HList <uint> { 29514, 29374, 29025, 28165, 27911, 27369, 27330, 27264, 27075, 26994, 26940, 26845, 26541, 26075, 25698, 25658, 25224, 25183, 25018, 25014, 24832, 24767, 24760, 24413, 24410, 24382, 24348, 24285, 24278, 24265, 24070, 24045, 23996, 23983, 23605, 23267, 23230, 23212, 23077, 22800, 22714, 22712, 22666, 22549, 22545, 22154, 21982, 21964, 21946, 21679, 21601, 21588, 21532, 21500, 21497, 21353, 21218, 21176, 21170, 21167, 20925, 20670, 20269, 19657, 19240, 18829, 18184, 18076, 18064, 18025, 17876, 17453, 17337, 17169, 16807, 16446, 16211, 16015, 15685, 15549, 15524, 15142, 13543, 13486, 13045, 12770, 12306, 12276, 11998, 11812, 11106, 11038, 10938, 10933, 10883, 10170, 9829, 9658, 9584, 9293, 8482, 8275, 8112, 8023, 7916, 7666, 7207, 5710, 5632, 5473, 5446, 4778, 4761, 4742, 4602, 4372, 3701, 2910, 2749, 2671, 2270, 2222, 1946, 1740, 1568, 1495, 1125, 918, 718, 370, 195, 92, 28 }; var forth = new HList <uint> { 29644, 29560, 29346, 28810, 28791, 28735, 28655, 28627, 28624, 28609, 28375, 27858, 27680, 27522, 27424, 27295, 27128, 27068, 26681, 26534, 26496, 26137, 26069, 26053, 26012, 25843, 25838, 25829, 25800, 25736, 25700, 25670, 25561, 25361, 25228, 24935, 24694, 24597, 24554, 24466, 24105, 24066, 24020, 23835, 23830, 23714, 23383, 23309, 23207, 22984, 22977, 22584, 22496, 22459, 22413, 22303, 21758, 21569, 21562, 21482, 21472, 21351, 21282, 21270, 21259, 21214, 21127, 21032, 20739, 19774, 19458, 19281, 18792, 18364, 18191, 18178, 18096, 18083, 17967, 17685, 17680, 17480, 17445, 17257, 17134, 17085, 16907, 16709, 16654, 16604, 16588, 16557, 16466, 16459, 16362, 16153, 16040, 16037, 15913, 15302, 15208, 15073, 14860, 14539, 14135, 14116, 14064, 14001, 13947, 13907, 13804, 13775, 13723, 13663, 13275, 12891, 12670, 12649, 12008, 11935, 11891, 11869, 11705, 11643, 11494, 11440, 11415, 11371, 11195, 11148, 11049, 11026, 10870, 10846, 10797, 10736, 10658, 10561, 10273, 10138, 10039, 9959, 9898, 9515, 9308, 9158, 9094, 9028, 8904, 8842, 8242, 7695, 6970, 6968, 6867, 6826, 6720, 6703, 6591, 6519, 6422, 6354, 6289, 6241, 5947, 5866, 5549, 5386, 5251, 5079, 5069, 5047, 4810, 4599, 4567, 4450, 4178, 4097, 4080, 3782, 3755, 3431, 3399, 3104, 2976, 2810, 2766, 2571, 2343, 2259, 2125, 2022, 1990, 1980, 1936, 1917, 1308, 1295, 1244, 1089, 878, 620, 522, 392, 90, 89 }; var result = new UnionList <uint>(forth, new UnionList <uint>(third, new UnionList <uint>(first, second))); var values = result.ToArray(); var grouped = values.GroupBy(x => x) .Select(group => new { Metric = group.Key, Count = group.Count() }) .OrderByDescending(x => x.Count); foreach (var group in grouped) { Console.WriteLine($"{group.Metric}: {group.Count}"); } }
public void Should_return_unique_values2() { var first = new HList <uint> { }; var second = new HList <uint> { 25, 18, 15, 11, 4 }; var result = new UnionList <uint>(first, second); var values = result.ToArray(); values.Should().BeEquivalentTo(new uint[] { 25, 18, 15, 11, 4 }); }
public void Should_return_unique_values4() { var first = new HList <uint> { 31, 29 }; var second = new HList <uint> { 25, 18, 15, 11, 4 }; var third = new HList <uint> { 15, 11, 4, 3 }; var result = new UnionList <uint>(third, new UnionList <uint>(first, second)); var values = result.ToArray(); Console.WriteLine(string.Join(',', values)); values.Should().BeEquivalentTo(new uint[] { 31, 29, 25, 18, 15, 11, 4, 3 }); }
public void Load() { DBInterface.CommandText = "SELECT nameCountry, codeISO2, codeISO3, codeCitizen FROM `sellcontroller`.`country` WHERE `idCountry` = @id;"; DBInterface.AddParameter("@id", MySql.Data.MySqlClient.MySqlDbType.Int32, ID); DataTable tab = DBInterface.ExecuteSelection(); if (tab.Rows.Count == 1) { Name = Convert.ToString(tab.Rows[0]["nameCountry"]); ISO = Convert.ToString(tab.Rows[0]["codeISO2"]); ISO3 = Convert.ToString(tab.Rows[0]["codeISO3"]); Nationality = Convert.ToString(tab.Rows[0]["codeCitizen"]); } else if (tab.Rows.Count > 1) { throw new DuplicateNameException("Country table has rows with same id"); } UnionList.Load(this); mirror = UnionList.mirror; Changed = false; }
/// <summary> /// 将查询命令和条件转换为 SQL 语句 /// </summary> /// <returns></returns> public override string ToString() { Type mastertype = typeof(T); string tableName = MyStagingUtils.GetMapping(mastertype); // master table StringBuilder sqlText = new StringBuilder(); sqlText.AppendLine($"SELECT {string.Join(",", Fields)} FROM {tableName} {masterAlisName}"); // union int _index = 2; foreach (var item in UnionList) { DbExpressionVisitor expression = new DbExpressionVisitor { TypeMaster = item.MasterType, AliasMaster = item.AlisName, AliasUnion = item.UnionAlisName }; expression.Visit(item.Body); string unionTableName = MyStagingUtils.GetMapping(item.Model); sqlText.AppendLine(item.UnionType.ToString().Replace("_", " ") + " " + unionTableName + " " + expression.AliasUnion + " ON " + expression.SqlText.Builder.ToString()); ParamList.AddRange(expression.SqlText.Parameters); _index++; } // condition if (WhereExpressionList.Count > 0) { foreach (var item in WhereExpressionList) { DbExpressionVisitor expression = new DbExpressionVisitor(); if (UnionList.Count == 0) { expression.TypeMaster = item.Model; expression.AliasMaster = masterAlisName; } else { ExpressionUnionModel union = null; if (item.UnionAlisName == null) { union = UnionList.FirstOrDefault(f => f.Model == item.Model); } else { union = UnionList.FirstOrDefault(f => f.Model == item.Model && f.UnionAlisName == item.UnionAlisName); } if (union == null && typeof(T) == item.Model) { expression.TypeMaster = item.Model; expression.AliasMaster = masterAlisName; } else if (union != null) { expression.AliasMaster = union.AlisName; expression.AliasUnion = union.UnionAlisName; } else { throw new NotSupportedException($"找不到 where {item.Body.ToString()}条件的表,不支持的表查询条件"); } } expression.Visit(item.Body); WhereList.Add(expression.SqlText.Builder.ToString().ToLower()); ParamList.AddRange(expression.SqlText.Parameters); } } if (WhereList.Count > 0) { sqlText.AppendLine("WHERE " + string.Join("\nAND ", WhereList)); } if (!string.IsNullOrEmpty(GroupByText)) { sqlText.AppendLine(GroupByText); } if (!string.IsNullOrEmpty(GroupByText) && !string.IsNullOrEmpty(HavingText)) { sqlText.AppendLine(HavingText); } if (!string.IsNullOrEmpty(OrderByText)) { sqlText.AppendLine(OrderByText); } if (!string.IsNullOrEmpty(LimitText)) { sqlText.AppendLine(LimitText); } this.commandtext = sqlText.ToString(); return(this.commandtext); }