c# - 在复杂类型列表中填充复杂类型
问题描述
假设我有一个复杂类型的列表(称为“ClientMessage”)。ClientMessage 有一个名为“MsgText”的字典类型和一个名为“ClientMessageDeeplinkResponse”的类列表,在一堆其他简单类型之上。
当前,存储过程返回三个数据集。tmpMsgResult 是主要结果,tmpDeeplinkResult 和 tmpMsgTxt 是次要结果,将用于丰富最终结果。
存储的过程执行速度很快(亚秒)。tmpMsgResult 返回 26000 行,tmpDeeplinkResult 返回 21000 行,tmpMsgTxt 返回 63300 行。
如何在 ClientMessage 中快速填充“MsgText”和“ClientMessageDeeplinkResponse”?我尝试了几种方法,从 20 秒到 45 秒不等。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ItchyScratchy13
{
class CMCSimple
{
public static List<ClientMessage> ReadClientMessagesSP(string connString, string GCN, bool? messageDeleted, bool? messageArchived)
{
List<ClientMessage> results = new List<ClientMessage>();
using (SqlConnection conn = new SqlConnection(connString))
{
using (SqlCommand cmd = new SqlCommand("cmc.GetCmcMessages", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@GCN", SqlDbType.VarChar).Value = GCN;
cmd.Parameters.Add("@DeletedMsgs", SqlDbType.Bit).Value = messageDeleted;
cmd.Parameters.Add("@ArchivedMsgs", SqlDbType.Bit).Value = messageArchived;
conn.Open();
DataSet ds = new DataSet();
SqlDataAdapter adapter;
adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
var tmpMsgResult = ds.Tables[0].AsEnumerable().Select(s => new ClientMessage
{
MsgID = s.Field<int>("MessageId"),
MsgAuditID = s.Field<int>("MessageAuditId"),
MsgGUID = s.Field<Guid>("MsgGuid"),
}).ToList();
var tmpDeeplinkResult = ds.Tables[1].AsEnumerable().Select(s => new AdminClientMessageDeeplink
{
MessageId = s.Field<int>("MessageId"),
DeeplinkMask = s.Field<string>("DeeplinkMask"),
DeeplinkName = s.Field<string>("DeeplinkName"),
DeepLinkValue = s.Field<string>("DeeplinkValue"),
}).ToList();
var tmpMsgTxt = ds.Tables[2].AsEnumerable().Select(s => new AdminClietnMsgTxt
{
MessageId = s.Field<int>("MessageId"),
MessageTxt = s.Field<string>("MessageText"),
RowNumber = int.Parse(s.Field<Int64>("ROW_NUMBER").ToString()),
}).ToList();
//ATTEMPT 1
Parallel.ForEach(tmpMsgResult, item =>
{
item.DeepLink = tmpDeeplinkResult.Where(wh => wh.MessageId == item.MsgID).Select(s => new ClientMessageDeeplinkResponse
{
DeeplinkMask = s.DeeplinkMask,
DeeplinkName = s.DeeplinkName,
DeepLinkValue = s.DeepLinkValue,
}).ToList();
item.MsgText = tmpMsgTxt.Where(wh => wh.MessageId == item.MsgID).Select(s => new KeyValuePair<int, string>(s.RowNumber, s.MessageTxt)).ToDictionary(x => x.Key, x => x.Value);
});
//ATTEMPT 2
results = tmpMsgResult.Select((x, i) => new ClientMessage
{
DeepLink = tmpDeeplinkResult.Where(wh => wh.MessageId == x.MsgID).AsParallel().Select(s => new ClientMessageDeeplinkResponse() { DeeplinkMask = s.DeeplinkMask, DeeplinkName = s.DeeplinkName, DeepLinkValue = s.DeepLinkValue }).ToList(),
MsgAuditID = x.MsgAuditID,
MsgGUID = x.MsgGUID,
MsgID = x.MsgID,
MsgText = tmpMsgTxt.Where(wh => wh.MessageId == x.MsgID).AsParallel().Select(s => new { s.RowNumber, s.MessageTxt }).ToDictionary(td => td.RowNumber, td => td.MessageTxt)
}).ToList();
//ATTEMPT 3
foreach (var item in tmpMsgResult)
{
item.DeepLink = tmpDeeplinkResult.Where(wh => wh.MessageId == item.MsgID).Select(s => new ClientMessageDeeplinkResponse
{
DeeplinkMask = s.DeeplinkMask,
DeeplinkName = s.DeeplinkName,
DeepLinkValue = s.DeepLinkValue
}).ToList();
item.MsgText = tmpMsgTxt.Where(wh => wh.MessageId == item.MsgID).Select(s => new KeyValuePair<int, string>(s.RowNumber, s.MessageTxt)).ToDictionary(x => x.Key, x => x.Value);
}
//ATTEMPT 4, super fast but not correct result set
results = (from zxc in tmpMsgResult
join vbn in tmpDeeplinkResult
on zxc.MsgID equals vbn.MessageId
join asd in tmpMsgTxt
on zxc.MsgID equals asd.MessageId
select new ClientMessage
{
DeepLink = new List<ClientMessageDeeplinkResponse>() { vbn },
MsgAuditID = zxc.MsgAuditID,
MsgGUID = zxc.MsgGUID,
MsgID = zxc.MsgID,
MsgText = new Dictionary<int, string> { { asd.RowNumber, asd.MessageTxt } }
}).ToList();
}
}
return results;
}
}
public class ClientMessage
{
public int MsgID { get; set; }
public int MsgAuditID { get; set; }
public Guid? MsgGUID { get; set; }
public Dictionary<int, string> MsgText { get; set; }
public List<ClientMessageDeeplinkResponse> DeepLink { get; set; }
}
public class AdminClientMessageDeeplink : ClientMessageDeeplinkResponse
{
public int MessageId { get; set; }
public int DeeplinkId { get; set; }
}
public class AdminClietnMsgTxt
{
public int MessageId { get; set; }
public string MessageTxt { get; set; }
public int RowNumber { get; set; }
}
public class ClientMessageDeeplinkResponse
{
public string DeeplinkName { get; set; }
public string DeepLinkValue { get; set; }
public string DeeplinkMask { get; set; }
}
}
解决方案
使用GroupBy
并且Dictionaries
应该可以按照以下方式做一些事情:
....
// It's better to use Dictionary for tmpDeeplinkResult and tmpMsgTxt
var grouppedTmpDeeplinkResult = tmpDeeplinkResult.GroupBy(x => x.MessageId).ToDictionary(g => g.Key, g => g.ToList());
var grouppedTmpMsgTxt = tmpDeeplinkResult.GroupBy(x => x.MessageId).ToDictionary(g => g.Key, g => g.ToDictionary(x => x.RowNumber, x => x.MessageTxt));
foreach (var item in tmpMsgResult)
{
item.DeepLink = grouppedTmpDeeplinkResult[item.MsgID].Select(s => new ClientMessageDeeplinkResponse
{
DeeplinkMask = s.DeeplinkMask,
DeeplinkName = s.DeeplinkName,
DeepLinkValue = s.DeepLinkValue
}).ToList();
item.MsgText = grouppedTmpMsgTxt[item.MsgID];
}
....
字典的查找,例如 dict[x] 可能看起来与 List 的相同,但它使用 hashset 并且具有O(1)
复杂性,而list.Where(...)
复杂性为O(N)
.
推荐阅读
- sorting - 如何通过匹配来自其他单元格的值来排列数据
- php - 尝试使用 php 和 laravel 从 s3 下载时出错
- autodesk-forge - 如何根据Activity中的输入rvtFile定义输出文件名?
- python - Django test.py 模型对象实例仅限于一个测试
- powershell - 如何通过 powers shell 命令在任务调度程序中运行计划任务
- c - 为什么有时对一个字符串进行两次检查?
- javascript - 如何将 html、css、script 转换为 javascript
- java - Exoplayer 音频不在后台服务中播放
- sqlite - 将输出作为“未来的实例”
’”扑腾扑腾 - vue.js - VueJs延迟初始化选项卡组件一次