使用LINQ填补数据中的空白的最佳方法[英] Best way to use LINQ to fill in gaps in my data

本文是小编为大家收集整理的关于使用LINQ填补数据中的空白的最佳方法的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

昨天,我在下面发布了有关如何使用Linq使用"组"将对象转换为平坦结构的问题:

linq groupby在对象上有多个级别

这是CédricBignon非常友善的回答.我想要这种转换的原因是,我可以用UserData变量填充ComponentArt的Silverlight Xychart组件.但是,我刚刚发现,以堆叠式栏格式显示时,其组件有一个已知的错误.如果数据中存在差距,则其显示不正确,因此我需要确保所有用户对类别的所有不同值都有一个值.因此,在我最初的问题中,我将希望用UserData填充的东西在哪里,我需要确保[user =" bob",类别=" international",支出= 0.00]结果.

您通过使用以下代码在您给我的LINQ语句之后实现了这一目标:

// 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多个用户和几个类别时,我的代码非常慢.可以以某种方式将其合并到提供的LINQ语句中,或者我最好使用上面的代码填写空白?

推荐答案

您可以准备所有用户/类别的点列表,0值将其与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();

本文地址:https://www.itbaoku.cn/post/1557195.html

问题描述

Yesterday I posted the question below on how to use LINQ to transform an object with several levels using 'group' into a flat structure:

LINQ GroupBy on object with several levels

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.

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 }));
}

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?

推荐答案

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();