Suppose I have a class representing order lines , eg
public class Line
{
public string Code ;
public string No ; // Invoice Number
public DateTime Date ;
public string Product ;
public decimal Quantity ;
}
and a List of lines, eg
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 retrieve all lines where the Customer Code has more than one invoice. To do this, I am first of all grouping by the Code, No and Date and then grouping that by customer Code and for any customers which two or more records, I am selecting all but the first record.
Like so:
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 am having trouble gets the results out as a List<Line>
I tried query1.SelectMany(r=>r.Results).ToList();
but this still leaves me with IGrouping and that's where I am stuck.
I could resort to the nested for 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/Linq way?
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
You're going to 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 an enumerable (I removed your lists) of IGrouping
and IGrouping
is itself an enumerable, so we can just flatten it directly.
See here: http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/09/28/9836.aspx
Edit: Remembered 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
);
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);
Is the same thing as:
var q2 = from r1 in query1
from r2 in r1.Results
from r3 in r2
select r3;
var output = q2.ToList();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With