score:0

After searching and reading stackoverflow, make desired result with this code.

    var groupedList = (from t in list
                       group t by new { t.Account, t.StartDate } into g
                       select new
                       {                              
                          g.Key.Account,
                          g.Key.StartDate
                        });

    var filteredList = groupedList.GroupBy(x => x.Account)
            .SelectMany(g => (from t in g orderby t.StartDate descending select t)
                     .Take(2).ToList() );

    var Result = (from c in list
                  join k in filteredList on
                  new { c.StartDate, c.Account } equals
                  new { k.StartDate, k.Account } //into j
                  select c).ToList();
        /*  or lambda method chain
        var groupedList = list.GroupBy(t => new {t.StartDate, t.Account})
            .Select(g => new { g.Key.StartDate,g.Key.Account})
            .GroupBy(x => x.Account)
            .SelectMany(g => (from t in g orderby t.StartDate descending select t)
                        .Take(2).ToList() );

          var result = (list.Join(inner: groupedList, 
            outerKeySelector: c => new {c.StartDate, c.Account}, 
            innerKeySelector: k => new {k.StartDate, k.Account},
            resultSelector: (c, k) => c))
            .OrderByDescending(e =>e.StartDate).OrderBy(e =>e.Account).ToList(); 

        */

      Console.WriteLine(Result);  

Thanks a lot LINQPAD(the best tool for linq) and all friends in stackoverflow (the best and professional developers in the world)

but i guess my code is very complex (3 level filtering) and have not best performance. :)

who have a better offer, please let me know.

I'd love to get some improvements!

score:0

In order to get the top two from the group the query would be like this: Update But in this case, the combination of the Account ID and the Start Date must be unique.

.ToList().GroupBy(x=>new{x.Account,x.StartDate}).SelectMany(y=>y.OrderByDescending(z=>z.StartDate).Take(2));

I am using the similar one in my code and know this works fine.

score:0

At last i find one statement which produce desired result.

var result = (from cheque in list.OrderBy(a => a.Account).ThenByDescending(a => a.StartDate)
                            group cheque by new { cheque.Account, cheque.StartDate } into gr
                            //from ids in gr
                            group gr by gr.Key.Account into secondGrouping
                            from second in secondGrouping.Distinct().Take(2)
                                  from Cheque f in second
                                  select f 
                            ).ToList<Cheque>();

score:2

The trick is to group by Account and Serial. Take the top two dates and then flatten the list again by SelectMany:

list.GroupBy(x => new {x.Account, x.Serial})
.Select(g => new { FirstTwo = g
                   .GroupBy(x => x.StartDate).Select(x => x.FirstOrDefault())
                   .OrderByDescending(x => x.StartDate).Take(2)
                 })
.SelectMany(g => g.FirstTwo)
.OrderBy(x => x.Account)
    .ThenByDescending(x => x.StartDate)
        .ThenBy(x => x.Serial)

Result:

1   1   20120120
1   2   20120120
1   3   20120120
1   1   20110120
1   2   20110120
1   3   20110120
2   1   20120314
2   2   20120314
2   1   20100417
2   2   20100417

Related Query

More Query from same tag