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 Query
- Complex merge of one List<CustomType> into another
- How to use one Column of Sql query into another sql query inside C# Code
- Merge two List<object> into one List in Linq
- Merge multiple Lists into one List with LINQ
- 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
- Merge multiple word documents into one using OpenXML and XElement
- How can I combine this code into one or two LINQ queries?
- C# - Merge list items into one item based on some matching values
- 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?
- Copy a list of objects to another typed list in one line of code
- How to merge two lists that share 1 attribute into a third one with LINQ?
- How to merge two lists of different types into one list of new type that contains both types?
- Linq & C# - Inserting distinct data from one class into another
- Conditions and merge on Datatables while data replacement from one to another
- How to break complex query into different methods to achieve less code complexity
- 'Inject' one expression into another
- How does one Transform a Collection of Objects into a Collection of new Objects of another Type in Powershell?
- LINQ or XSLT to turn one Element into another in Visual Basic 9
- How can I code numerous MIN functions into one LINQ to DataSet query
- Merge two LINQ expressions into one
- Merge the values of multiple dictionaries into one list
- How to combine / merge two StringCollection into one in C#
- Merge and map from one class to another in C# using linq
- How to join the result of one query into the result of another without selecting new instance?
- Is there an algorithm or pattern to merge several rows of the same record into one row?
- Linq assign values from one table into another
- How can I insert all rows from one table into another table with Linq-to-SQL?
- Merge multiple xml files into one using linq to xml
More Query from same tag
- Is Linq Include broken when used with joins and where clauses?
- Select max value in dropdownlist?
- linq operations on extended list class
- LINQ GroupBy except null values
- LinqtoSQL SelectMany type arguments cannot be inferred from usage
- Returning Concatenated String with LINQ for Dropdown
- Using ViewModels and Linq in ASP.Net MVC 4
- Use a numeric value in a linq dynamic query string
- How can I cast IEnumerable<?> to IEnumerable<string>?
- LINQ update procedures - best practice
- how to get DataGridViewColumns out of IOrderedEnumerable with DataGridviewCell's?
- How to group a time series by interval (OHLC bars) with LINQ
- Use of SelectMany
- Linq Query using where clause in windows phone app
- How can I write an UPDATE in LINQ without fetching the object, changing it, and calling SaveChanges?
- Is it possible to extend Entity Framework to add new database query types?
- Entity Framework lazy load
- How to check a string contains another string
- LINQ Option Built Query + DateTime Comparision
- Can I use Func/Predicate or Linq expression to create generic filtering of a List<T>?
- Linq select only newest of duplicates
- Get XElement from XML using LINQ
- Enumerable.Any<T>(this IEnumerable<T> source) should handle null enumerable too, surely?
- Grouping data based on date entity framework and LINQ
- Entity Framework4, to see generated query?
- Ignore dots in Convert.ToDecimal and return 0
- How can I use Linq to extract this data from a List?
- How can i insert into multiple tables using Linq?
- Create a dynamic Expression<Func<TIn, TOut>> from a string
- LINQ in child AppDomain