string sql = string.Format(@"SELECT DISTINCT TenantId , TenantName FROM {0} ORDER BY TenantId",biao); var list = _sqlSugarClient.Ado.GetDataTable(sql);的list转换string集合
时间: 2023-12-14 17:40:06 浏览: 125
可以使用 LINQ 将 DataTable 转换为 List<string>,示例代码如下:
```
string sql = string.Format(@"SELECT DISTINCT TenantId, TenantName FROM {0} ORDER BY TenantId", biao);
var dataTable = _sqlSugarClient.Ado.GetDataTable(sql);
var list = dataTable.AsEnumerable()
.Select(row => $"{row["TenantId"]},{row["TenantName"]}")
.ToList();
```
上述代码中,使用了 `AsEnumerable()` 将 DataTable 转换为可枚举类型,然后使用 `Select()` 函数将每行数据转换为一个字符串,并最终使用 `ToList()` 将结果转换为 List<string> 类型。其中,`$"{row["TenantId"]},{row["TenantName"]}"` 是 C# 6.0 中新增的字符串插值语法,可以更方便地拼接字符串。
相关问题
string sql = string.Format(@"SELECT DISTINCT TenantId , TenantName FROM {0} ORDER BY TenantId", biao); var dataTable = _sqlSugarClient.Ado.GetDataTable(sql); // 将 DataTable 转换为 List<string> List<string> stringList = dataTable.AsEnumerable().Select(row => row.Field<string>(0) + "|" + row.Field<string>(1)).ToList(); // 将 List 分别赋值给 key 和 value 字段 List<KeyValue> keyValueList = new List<KeyValue>(); foreach (string str in stringList) { string[] strArray = str.Split('|'); KeyValue keyValue = new KeyValue(); keyValue.key = strArray[0]; keyValue.value = strArray[1]; keyValueList.Add(keyValue); }报错System.InvalidCastException:“Unable to cast object of type 'System.Int64' to type 'System.Int32'.”
这个错误是因为您在使用 `row.Field<string>(0)` 时,`0` 应该是 `Int32` 类型的,但是实际上返回的类型是 `Int64`,因此无法转换成 `Int32` 类型。您可以尝试在 `Select` 语句中将返回的列转换成 `Int32` 类型,例如:
```
string sql = string.Format(@"SELECT DISTINCT CONVERT(INT, TenantId) AS TenantId, TenantName FROM {0} ORDER BY TenantId", biao);
var dataTable = _sqlSugarClient.Ado.GetDataTable(sql);
List<string> stringList = dataTable.AsEnumerable().Select(row => row.Field<string>(0) + "|" + row.Field<string>(1)).ToList();
List<KeyValue> keyValueList = new List<KeyValue>();
foreach (string str in stringList)
{
string[] strArray = str.Split('|');
KeyValue keyValue = new KeyValue();
keyValue.key = strArray[0];
keyValue.value = strArray[1];
keyValueList.Add(keyValue);
}
```
这里使用了 `CONVERT(INT, TenantId)` 将返回的 `TenantId` 列转换成 `Int32` 类型。
优化代码 参考 if (cstemp.combos != null && cstemp.combos.Count > 0) { List<BCD_COMBO_ITEMEntity> _ITEMEntities_Yes = new BCD_COMBO_ITEM().GetItemList( String.Format("COMBOGUID IN ({0})", String.Join(",", cstemp.combos.Select(x => "'" + x.GUID + "'").Distinct()))); if (_ITEMEntities_Yes != null && _ITEMEntities_Yes.Count > 0) { List<BCD_COMBO_ITEMEntity> _ITEMEntities_No = (cstemp.Nocombos != null && cstemp.Nocombos.Count > 0) ? new BCD_COMBO_ITEM().GetItemList( String.Format("COMBOGUID IN ({0})", String.Join(",", cstemp.combos.Select(x => "'" + x.GUID + "'").Distinct()))) : null; string _ITEMEntities_YesString = String.Join(",", _ITEMEntities_Yes.Select(x => x.ITEMGUID ).Distinct()); string _ITEMEntities_NoString = (_ITEMEntities_No != null && _ITEMEntities_No.Count > 0)? String.Join(",", _ITEMEntities_No.Select(x => x.ITEMGUID).Distinct()):""; List<BCB_CHECKSUBEntity> subTempsno = !string.IsNullOrWhiteSpace(_ITEMEntities_NoString) ? _CHECKSUBEntities.Where(x => _ITEMEntities_NoString.Contains(x.ITEMGUID)).ToList() : null; string subTempsnoString = (subTempsno != null && subTempsno.Count > 0) ? String.Join(",", subTempsno.Select(x => x.MAINGUID).Distinct()):""; List < BCB_CHECKMAINEntity > mainsTemps = _CHECKMAINEntitys.ToList().Where(r => ((cstemp.ageb > 0 || cstemp.agee > 0) ? (r.AGE >= cstemp.ageb && r.AGE <= cstemp.agee) : true) && (!string.IsNullOrWhiteSpace(subTempsnoString) ? (cstemp.bothagree == "True" ? subTempsnoString.Contains(r.GUID): !subTempsnoString.Contains(r.GUID)) : true)).ToList(); string mainguidstring = (mainsTemps != null && mainsTemps.Count > 0) ? String.Join(",", mainsTemps.Select(x => x.GUID).Distinct()) : ""; List <BCB_CHECKSUBEntity> subTemps = _CHECKSUBEntities.Where(x => !string.IsNullOrWhiteSpace(x.RESULT) && _ITEMEntities_YesString.Contains(x.ITEMGUID) && mainguidstring.Contains(x.MAINGUID)).ToList(); if (subTemps != null ) { drAuto[cstemp.CSName] = subTemps.Select(x => x.MAINGUID).Distinct().Count(); } } }
以下是我优化后的代码:
if (cstemp.combos != null && cstemp.combos.Any())
{
var comboGuids = cstemp.combos.Select(x => x.GUID).Distinct().Select(x => $"'{x}'");
var itemEntitiesYes = new BCD_COMBO_ITEM().GetItemList($"COMBOGUID IN ({string.Join(",", comboGuids)})");
if (itemEntitiesYes != null && itemEntitiesYes.Any())
{
List<BCD_COMBO_ITEMEntity> itemEntitiesNo = null;
if (cstemp.Nocombos != null && cstemp.Nocombos.Any())
{
var noComboGuids = cstemp.Nocombos.Select(x => x.GUID).Distinct().Select(x => $"'{x}'");
itemEntitiesNo = new BCD_COMBO_ITEM().GetItemList($"COMBOGUID IN ({string.Join(",", noComboGuids)})");
}
var itemEntitiesYesString = string.Join(",", itemEntitiesYes.Select(x => x.ITEMGUID).Distinct());
var itemEntitiesNoString = itemEntitiesNo != null && itemEntitiesNo.Any() ? string.Join(",", itemEntitiesNo.Select(x => x.ITEMGUID).Distinct()) : "";
var subTempsNo = !string.IsNullOrWhiteSpace(itemEntitiesNoString) ? _CHECKSUBEntities.Where(x => itemEntitiesNoString.Contains(x.ITEMGUID)).ToList() : null;
var subTempsNoString = subTempsNo != null && subTempsNo.Any() ? string.Join(",", subTempsNo.Select(x => x.MAINGUID).Distinct()) : "";
var mainsTemps = _CHECKMAINEntitys.ToList().Where(r =>
(cstemp.ageb > 0 || cstemp.agee > 0) ? (r.AGE >= cstemp.ageb && r.AGE <= cstemp.agee) : true &&
(string.IsNullOrWhiteSpace(subTempsNoString) || (cstemp.bothagree == "True" ? !subTempsNoString.Contains(r.GUID) : subTempsNoString.Contains(r.GUID)))
).ToList();
var mainguidstring = mainsTemps.Any() ? string.Join(",", mainsTemps.Select(x => x.GUID).Distinct()) : "";
var subTemps = _CHECKSUBEntities.Where(x => !string.IsNullOrWhiteSpace(x.RESULT) && itemEntitiesYesString.Contains(x.ITEMGUID) && mainguidstring.Contains(x.MAINGUID)).ToList();
if (subTemps != null)
{
drAuto[cstemp.CSName] = subTemps.Select(x => x.MAINGUID).Distinct().Count();
}
}
}
我主要做了以下几个优化:
1. 使用 Any() 方法替代 Count > 0,可以提高代码的可读性和性能。
2. 使用 var 关键字来声明变量,可以让代码更简洁。
3. 将字符串拼接操作移到变量声明处,可以提高可读性和代码复用性。
4. 将多个判断条件整合到一起,可以提高代码的简洁性和可读性。