score:3
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:
Source: stackoverflow.com
Related Articles
- Complex merge of one List<CustomType> into another
- How to break complex query into different methods to achieve less code complexity
- Edit: Merge list into another list that is null, it is possible?
- How to use one Column of Sql query into another sql query inside C# Code
- Linq Aggregate complex types into a string
- Linq Select Certain Properties Into Another Object?
- Merge two List<object> into one List in Linq
- Merge multiple Lists into one List with LINQ
- System.ArgumentException: Complex DataBinding accepts as a data source either an IList or an IListSource
- Convert List<T> into another List<T> that contains another List<T>
- LINQ Query - How to map a resultset into another object using Select
- LINQ merge List<IEnumerable<T>> into one IEnumerable<T> by some rule
- Merge two Lists in C# and merge objects with the same id into one list item
- Suggestions for designing complex LINQ code
- Merge multiple word documents into one using OpenXML and XElement
- Execute expression on another IQueryable source
- LINQ Source Code Available
- LINQ: How to Append List of Elements Into Another List
- Translating expression tree from a type to another type with complex mappings
- How can I combine this code into one or two LINQ queries?
- Find duplicate and merge record into single datatable c#
- C# - Merge list items into one item based on some matching values
- How merge two sequences into one?
- .NET 4 Code Contracts: "requires unproven: source != null"
- Converting one XML document into another XML document
- linq - how do you do a query for items in one query source that are not in another one?
- Cleaner way to convert collection into another type?
- Merge duplicate data without affecting others in LINQ code
- Changing a piece of code into expressions
- How to merge two classes into single unit?
- Convert IEnumerable<string> to ICollection<string>
- How to specify the type of an anonymous type while using a generic type
- Get List of Records between two times C# Linq To Entities
- Update ListView using Linq to Sql Classes in WPF
- Get Values from single column based on condition
- Check if parameter value is null or not inside query
- Conversion of Sql query to linq
- LINQPAD - How do I set values to parameters when using Select after reading in lines from a file
- Subjects vs Publish
- Choosing Database and ORM for a .NET project
- Syntax using LINQ ToList to cast GENERIC list to list of its base type
- Enumerable.Any<T>(this IEnumerable<T> source) should handle null enumerable too, surely?
- How to return a list of stores which are a specified distance from the entered Postcode
- Linq - Take element with max datetime
- Linq to sql left join additional null check needed
- How to Return Thread result
- Querying Datatable with where condition
- Variation of LINQ query for multiple splits
- Can not use Count() on property of Collection linQ
- Convert SQL query to LINQ to get column value as xml string