c# - 将一个 List 复杂合并到另一个

Question

我需要返回按时间顺序排列的警报列表。下面的方法 1 执行此操作。

我还需要获取某种警报类型的警报，如果 AlertTypeID = {1,2,3,6,8,9,x}，则按 CreateDate 返回这些警报并按月排序。下面的方法 2 执行此操作。

现在，我需要将方法 2 的结果插入到方法 1 的结果中。尽管方法 2 返回的项目是按月组织的，但我们只关心最近的。只有方法 2 中的最新子组项目需要遵循方法 1 返回的项目的时间顺序。该子组item 将确定子组 2 中其余每月项目的放置位置。

最后一个要求是必须删除重复项。如果返回从子组警报中，它不能同时存在于主要组警报中。

下面我提供了我要达到的效果的说明:

december alert 1 (12/23/2012)
december alert 2 (12/21/2012)
december alert 3 (12/20/2012)
december subalert 1 (12/19/2012)
december subalert 2 (12/18/2012)
december subalert 3 (12/04/2012)
december subalert 4 (12/01/2012)
december alert 4 (12/18/2012)
december alert 5 (12/12/2012)
november alert 1 (11/22/2012)
november alert 2 (11/16/2012)
november subalert 1 (11/14/2012)
november subalert 2 (11/08/2012)
november alert 3 (11/12/2012)

代码:

1. 按日期时间的所有警报

   List<Alert> result = new List<Alert>();
   using(NeuroLabLinqDataContext dc = conn.GetContext())
   {
       IEnumerable<Alert> alerts = (from a in dc.Alerts
                                    where a.AccountID == AccountID
                                    orderby a.CreateDate descending
                                    select a).Take(40);
       result = alerts.ToList();
   }
   return result;
   

2. 按月划分的警报类型

   List<Alert> result = new List<Alert>();
   int[] alertTypes = {1,2,3,4,5,6,7,8,9};
   
   using (NeuroLabLinqDataContext dc = conn.GetContext())
   {
       IEnumerable<Alert> alerts = (from a in dc.Alerts
                                    where a.AccountID == AccountID &&
                                    alertTypes.Contains(a.AlertTypeID)
                                    orderby ((DateTime)a.CreateDate).Month ascending
                                    select a).ToList();
   }
   
   return result;
   

最终 select 语句的分组应如下所示:

 select new { Date = alerts.CreateDate, 
              Message = alerts.Message, 
              Type = alert.AlertTypeID, 
              RecentActivity = [if from method 2, then true] };

更新:更新方法

 public List<Alert> GetAlertsByAccountID(Int32 AccountID, params int[] alertTypes)
        {

            List<Alert> result = new List<Alert>();

            using (NeuroLabLinqDataContext dc = conn.GetContext())
            {
                var all = (from a in dc.Alerts
                           where a.AccountID == AccountID
                           orderby a.CreateDate descending
                           select a);

                int abc = all.Count();

                var first = all
                    .Where(a => a.AccountID == AccountID) && !alertTypes.Contains(a.AlertTypeID))
                    .OrderByDescending(a => a.CreateDate)
                    .GroupBy(a => a.CreateDate.Date)
                    .ToDictionary(g => g.Key);

                var firstKeys = first.Keys.Cast<DateTime>()
                    .ToList().OrderBy(k => k);

                var second = all
                    .Where(a => a.AccountID == AccountID) && alertTypes.Contains(a.AlertTypeID))
                    .OrderBy(a => a.CreateDate.Month)
                    .GroupBy(a => a.CreateDate.Month)
                    .ToDictionary(g => firstKeys
                        .First(k => k > g.OrderByDescending(a => a.CreateDate)
                        .FirstOrDefault().CreateDate));

                var combined = first
                    .GroupJoin(
                        second,
                        fk => fk.Key,
                        sk => sk.Key,
                        (d, l) => d.Value
                            .Union(l.SelectMany(i => i.Value).ToArray()))
                    .SelectMany(i => i);

                    result = combined.ToList(); 
            }

            return result;

        }

感谢 John，我走得更远了。目前，我收到以下错误:

Sequence contains no matching element

在这一行(我很确定):

.First(k => k > g.OrderByDescending(a => a.CreateDate)

值得一提的是，以下是我的 Alerts 表中的数据。

AlertID AccountID   CreateDate  Timestamp   AlertTypeID    Message
122 5   2008-03-11 20:48:07.983 0x00000000000128FB  9        sdfs
123 1   2008-03-11 20:48:39.957 0x00000000000128FE  8        sdfsd
124 5   2008-03-11 20:48:39.977 0x00000000000128FF  8        sdfs
125 5   2008-03-11 20:48:40.017 0x0000000000012901  8        asdfa
126 1   2008-03-12 22:57:42.160 0x00000000000130B3  4        sfsf
127 5   2008-03-12 22:57:42.337 0x00000000000130B4  4        sdfsd
128 5   2008-03-13 09:42:14.237 0x0000000000013889  4        sdfsd
129 5   2008-03-13 09:42:31.957 0x000000000001388B  4        sdfsd
130 5   2008-03-13 09:42:45.397 0x000000000001388D  5        asdfsdf
131 1   2008-03-16 14:52:17.197 0x0000000000014822  9        asdfsdf
132 1   2008-04-12 15:25:17.330 0x000000000001B582  3        sfasdf
133 5   2008-04-12 15:25:17.700 0x000000000001B583  3        dfsfds
134 6   2008-04-14 08:37:03.273 0x000000000001BD87  3        aasfsd
135 6   2008-04-14 08:37:15.270 0x000000000001BD89  3        fhfsdf
136 6   2008-04-14 08:38:45.120 0x000000000001BD8B  2        ghsdgd
137 6   2008-04-14 08:41:30.407 0x000000000001BD9A  4        fghsdfg
138 6   2008-04-14 08:42:30.800 0x000000000001BD9C  4        gfsdf
139 6   2008-04-14 08:42:43.763 0x000000000001BD9E  5        sdfsdf
140 6   2008-04-14 08:49:25.450 0x000000000001BDAA  9        sdfasdfa
141 6   2008-04-14 08:49:34.237 0x000000000001BDAC  9        sdfasdf
142 1   2008-04-14 08:50:23.380 0x000000000001BDAF  8        sdfhdfhsg
143 6   2008-04-14 08:50:23.567 0x000000000001BDB0  8        dgasdf
144 5   2008-04-14 08:50:23.690 0x000000000001BDB1  8        dgasdf
145 6   2008-04-14 08:50:23.747 0x000000000001BDB2  8         dgasdf
147 1   2008-06-24 14:22:41.183 0x00000000000222E6  14       dgasdf
148 5   2008-06-24 14:22:41.617 0x00000000000222E7  14       dgasdf
149 6   2008-06-24 14:22:41.623 0x00000000000222E8  14       dgasdf
150 1   2008-06-24 20:11:57.757 0x0000000000022AB3  13     dgasdf
151 5   2008-06-24 20:11:57.947 0x0000000000022AB4  13       dgasdf
152 6   2008-06-24 20:11:57.953 0x0000000000022AB5  13       dgasdf
153 1   2008-07-03 18:41:51.067 0x0000000000028888  14       dgasdf
154 5   2008-07-03 18:41:51.230 0x0000000000028889  14        dgasdf
155 6   2008-07-03 18:41:51.237 0x000000000002888A  14        dgasdf
156 1   2008-07-03 18:46:17.873 0x000000000002888D  14       dgasdf
157 5   2008-07-03 18:46:17.937 0x000000000002888E  14       dgasdf
158 6   2008-07-03 18:46:17.940 0x000000000002888F  14        dgasdf

Answer 1

关键是将两组分解成字典，使用第一个列表中的日期作为字典的键，并选择第二个列表项日期之后最接近的键作为第二个字典的键。

一旦你有了两个字典，每个字典都使用类型和子类型的公共(public)键值，你就可以执行 GroupJoin 和 SelectMany 来获得结果排序列表。

(*请注意，答案基于问题的早期版本略有不同，我不会花时间更新答案，因为我认为基本问题已经在这个答案中得到说明和解决)

更新 2我意识到您在 First() 调用中看到的问题是您的某些子警报项目可能比任何其他警报项目更新，这会导致您的异常。我通过使用 DateTime::MaxValue 向第一个字典添加一个“代理”键来解决这个问题，然后我不再从第一个列表中过滤掉子警报，我只使用 .Distinct( ) 对最终结果进行去重

我使用 linqpad 模拟了这个问题并使用字典和 GroupJoin 解决了它

var all = new []{
    new {date = DateTime.Parse("2012-12-23"), type = "alert", value = 1, accountId = 333 },
    new {date = DateTime.Parse("2012-12-21"), type = "alert", value = 2, accountId = 333 },
    new {date = DateTime.Parse("2012-12-20"), type = "alert", value = 3, accountId = 333 },
    new {date = DateTime.Parse("2012-12-18"), type = "alert", value = 4, accountId = 333 },
    new {date = DateTime.Parse("2012-12-12"), type = "alert", value = 5, accountId = 333 },
    new {date = DateTime.Parse("2012-11-22"), type = "alert", value = 1, accountId = 333 },
    new {date = DateTime.Parse("2012-11-16"), type = "alert", value = 2, accountId = 333 },
    new {date = DateTime.Parse("2012-11-12"), type = "alert", value = 3, accountId = 333 },
    new {date = DateTime.Parse("2012-12-19"), type = "subalert", value = 1, accountId = 333 },
    new {date = DateTime.Parse("2012-12-18"), type = "subalert", value = 2, accountId = 333 },
    new {date = DateTime.Parse("2012-12-04"), type = "subalert", value = 3, accountId = 333 },
    new {date = DateTime.Parse("2012-12-01"), type = "subalert", value = 4, accountId = 333 },
    new {date = DateTime.Parse("2012-11-14"), type = "subalert", value = 1, accountId = 333 },
    new {date = DateTime.Parse("2012-11-08"), type = "subalert", value = 2, accountId = 333 },  
/*add*/ new {date = DateTime.Parse("2012-12-25"), type = "subalert", value = 9, accountId = 333 },  
};

var first = all
    .Where(a=>a.accountId == 333 /* removed && type != "alert" */)
    .OrderByDescending(a=>a.date)
    .GroupBy(a=>a.date.Date)
    .ToDictionary(g=>g.Key);

var firstKeys = first.Keys
    .Cast<DateTime>()
    .Union(new []{DateTime.MaxValue}) /* added this 'surrogate' key */
    .OrderBy(k=>k)
    .ToArray();

var second = all
    .Where(a=>a.accountId == 333 && a.type == "subalert")
    .OrderBy(a=>a.date.Month)
    .GroupBy(a=>a.date.Month)
    .ToDictionary(g=>firstKeys.First(k=>k > g.OrderByDescending(a=>a.date).FirstOrDefault().date));


var combined = first
    .GroupJoin(
        second,
        fk=>fk.Key,
        sk=>sk.Key,
        (d,l)=>d.Value
            .Union(l.SelectMany(i=>i.Value).ToArray()))
    .SelectMany(i=>i)
    .Distinct(); /* Added this to remove duplicates */

combined.Dump();

产生: