How do you align a Linq query after using double grouping?

Question

Suppose I have a class representing order strings, for example

public class Line { public string Code ; public string No ; // Invoice Number public DateTime Date ; public string Product ; public decimal Quantity ; } 

and a list of strings, for example

 List<Line> myList = new List<Line>(); myList.Add(new Line() { Code = "ABC001", No = "1001" ,Date = new DateTime(2012,4,1) , Product = "X", Quantity= 1m}); myList.Add(new Line() { Code = "ABC001", No = "1001" ,Date = new DateTime(2012,4,1) , Product = "Y", Quantity= 1m}); myList.Add(new Line() { Code = "ABC002", No = "1002" ,Date = new DateTime(2012,4,2) , Product = "X", Quantity= 1m}); myList.Add(new Line() { Code = "ABC002", No = "1002" ,Date = new DateTime(2012,4,2) , Product = "Y", Quantity= 1m}); myList.Add(new Line() { Code = "ABC002", No = "1003" ,Date = new DateTime(2012,4,3) , Product = "Z", Quantity= 1m}); myList.Add(new Line() { Code = "ABC002", No = "1004" ,Date = new DateTime(2012,4,4) , Product = "X", Quantity= 1m}); myList.Add(new Line() { Code = "ABC003", No = "1005" ,Date = new DateTime(2012,4,4) , Product = "X", Quantity= 1m}); myList.Add(new Line() { Code = "ABC003", No = "1006" ,Date = new DateTime(2012,4,4) , Product = "X", Quantity= 1m}); myList.Add(new Line() { Code = "ABC003", No = "1006" ,Date = new DateTime(2012,4,4) , Product = "Y", Quantity= 1m}); 

I am looking to get all the lines in which the client code contains multiple accounts. To do this, I first group the code, "No" and "Date", and then grouping it by the client code, and for any clients that have two or more records, I select everything except the first record.

Same:

 var query1 = (from r in myList group r by new { r.Code , r.No , r.Date } into results group results by new { results.Key.Code } into results2 where results2.Count() > 1 select new { results2.Key.Code , Count = results2.Count(), Results = results2.OrderBy(i=>i.Key.Date).Skip(1).ToList() // Skip the first invoice } ).ToList(); 

query1 now contains the correct records, but wrapped inside IGrouping, and I'm having trouble getting results like List<Line>

I tried query1.SelectMany (r => r.Results) .ToList ();

but it still leaves me with IGrouping and where I am stuck.

I could resort to nested loops, as in

 List<Line> output = new List<Line>(); foreach (var r1 in query1) { foreach(var r2 in r1.Results) foreach(var r3 in r2) output.Add(r3); } 

but is there a better way / linq?

The actual output should be four lines, as in

 // Code No Date Product Quantity // ABC002 1003 03/04/2012 00:00:00 Z 1 // ABC002 1004 04/04/2012 00:00:00 X 1 // ABC003 1006 04/04/2012 00:00:00 X 1 // ABC003 1006 04/04/2012 00:00:00 Y 1 

Answer 1

You gonna kick yourself:

 query1.SelectMany(q => q); ABC002 1003 3/04/2012 12:00:00 AM Z 1 ABC002 1004 4/04/2012 12:00:00 AM X 1 ABC003 1006 4/04/2012 12:00:00 AM X 1 ABC003 1006 4/04/2012 12:00:00 AM Y 1 

The return from query1 is enumerated (I deleted your lists) IGrouping and IGrouping are enumerated, so we can just smooth it directly.

See here: http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/09/28/9836.aspx

Edit: Remember, I also simplified your code:

 var query1 = (from r in myList group r by new { r.Code , r.No , r.Date } into results group results by new { results.Key.Code } into results2 where results2.Count() > 1 from result in results2.OrderBy(i=>i.Key.Date).Skip(1) select result ); 

Answer 2

This code:

 List<Line> output = new List<Line>(); foreach (var r1 in query1) foreach(var r2 in r1.Results) foreach(var r3 in r2) output.Add(r3); 

The same as:

 var q2 = from r1 in query1 from r2 in r1.Results from r3 in r2 select r3; var output = q2.ToList(); 

Answer 3

Try the following:

 var flattenedLines = from outerGroup in query1 from innerGroup in outerGroup.Results from line in innerGroup select line; 

or

 var flattenedLines = query1 .SelectMany(outerGroup => outerGroup.Results, (outerGroup, innerGroup) => innerGroup) .SelectMany(x => x);