使用LINQ填补我的数据空白的最佳方法 [英] Best way to use LINQ to fill in gaps in my data
问题描述
昨天我在下面发布了有关如何使用LINQ使用组"将具有多个级别的对象转换为平面结构的问题:
Yesterday I posted the question below on how to use LINQ to transform an object with several levels using 'group' into a flat structure:
塞德里克·比格农(CédricBignon)对此表示非常友好的回答.我想要进行此转换的原因是,我可以使用userData变量填充ComponentArt的Silverlight XYChart组件.但是,我刚刚发现,以堆叠的条形格式显示时,它们的组件存在一个已知的错误.如果数据中存在间隙,则不能正确显示,因此我需要确保所有用户对Category的所有不同值都具有一个值.因此,在我最初的问题中,我将要填充的userData放在哪里,我需要确保[User ="Bob",Category ="International",Spend = 0.00]也出现在结果.
This was very kindly answered by Cédric Bignon. The reason i wanted this transformation was so I could populate ComponentArt's Silverlight XYChart component with the userData variable. However, I've just found out that there is a known bug with their component when displaying in a stacked bar format. If there's gaps in the data it does not display properly, so I need to ensure that all users have a value for all distinct values of Category. So in my original question, where I've put what I'd like userData to be populated with, I'd need to ensure [User = "Bob", Category = "International", Spend = 0.00] was also present in the results.
在您给我的LINQ语句之后,我通过使用以下代码实现了这一点:
I've achieved this by using the following code after the LINQ statement you have given me:
// LINQ Statement provided by @Cedric
var userData = from spend in allSpend
from userDataPoint in
(from monthSpend in spend.Spend
from spendDetail in monthSpend.Detail
group spendDetail by spendDetail.Description into g
select new UserDataPoint
{
User = spend.UserName,
Category = g.Key,
Spend = g.Sum(t => t.Amount)
})
select userDataPoint;
// END
List<UserDataPoint> userDataNoGaps = new List<UserDataPoint>();
userDataNoGaps = userData.ToList();
foreach (string strCategory in userData.Select(c => c.Category).Distinct())
{
var existing = userData.Where(c => c.Category == strCategory).Select(c => c.User);
userDataNoGaps.AddRange(userData.Where(c => !existing.Contains(c.User)).Select(c => new UserDataPoint() { User = c.User, Category = strCategory, Spend = 0 }));
}
但是,当我有1000多个用户和几个类别时,我的代码就非常慢.可以以某种方式将其合并到Cédric提供的LINQ语句中吗?还是我以后再使用上面的代码填补空白?
But my code is pretty slow when I have over 1000 users and a few categories. Can this somehow be incorporated into the LINQ statement Cédric provided or am I better off filling in the gaps afterwards using the code above?
推荐答案
您可以为具有0
值的所有用户/类别准备点列表,并通过union
将其与userData合并.
You can prepare list of points for all Users/Categories with 0
values merge it with your userData with union
.
var userDataList = userData.ToList();
var usersList = userDataList.Select(x => x.Uder).Distinct().ToList();
var categoriesList = userDataList.Select(x => x.Category).Distinct().ToList();
// make list of UserDataPoint with 0 sums
var empty = (from user in users
from category in categoriesList
select new UserDataPoint
{
User = user,
Category = category,
Spend = 0
}).ToList();
var merged = userDataList.Union(empty)
.GroupBy(x => new { x.User, x.Category }) // here sum up empty points with real
.Select(new UserDataPoint {
User = group.Key.User,
Category = group.Key.Category,
Spend = group.Sum(y => y.Spend)
}).ToList();
这篇关于使用LINQ填补我的数据空白的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!