score:3

Accepted answer

the key is breaking the two groups down into dictionaries, using the dates from the first list as the keys to the dictionary, and picking the closest key after the second list item date as the key for the second dictionary.

once you have the two dictionaries, each using a common key value for the types and the subtypes you can just do a groupjoin and selectmany to get the results in a sorted list.

(* note that the answer is based on a slightly different, earlier version of the question, i'm not going to take the time to update the answer because i think the fundamental problem is illustrated and solved in this answer already)

update 2 i realize the problem you're seeing with the first() call is that some of your subalert items might be newer than any other alert item which would cause your exception. i addressed that by adding a 'surrogate' key to the first dictionary using datetime::maxvalue, and then i no longer filter out the subalerts from the first list, i just use .distinct() on the final result to remove duplicates

using linqpad i mocked up this question and solved it using dictionaries and 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();

which yields:

updated results


Related Query

More Query from same tag