Saturday, 14 September 2013

Entity Framework - Linq : how query to take 2 first ordered and grouped list

Entity Framework - Linq : how query to take 2 first ordered and grouped list

I have a large table with 6000000 record like this
format(Acc,sDate,Serial,Amount,...)
Acc,date,serial is PKey
To show my problem, created small code
public class Cheque
{
public string Account{ get; set; }
public string Serial{ get; set; }
public string StartDate { get; set; }
// ... public string Amount { get; set; } ...
}
var list = new List<Cheque>();
list.Add(new Cheque() { Account = "1", Serial = "1", StartDate =
"20080120" });
list.Add(new Cheque() { Account= "1", Serial= "2", StartDate =
"20080120" });
list.Add(new Cheque() { Account= "1", Serial= "3", StartDate =
"20080120" });
// each acc have 100 to 300 record per date ,for simplicity 3 obj added
list.Add(new Cheque() { Account= "1", Serial= "1", StartDate =
"20110120" });
list.Add(new Cheque() { Account= "1", Serial= "2", StartDate =
"20110120" });
list.Add(new Cheque() { Account= "1", Serial= "3", StartDate =
"20110120" });
list.Add(new Cheque() { Account= "1", Serial= "1", StartDate =
"20120120" });
list.Add(new Cheque() { Account= "1", Serial= "2", StartDate =
"20120120" });
list.Add(new Cheque() { Account= "1", Serial= "3", StartDate =
"20120120" });
list.Add(new Cheque() { Account= "2", Serial= "1", StartDate =
"20090320" });
list.Add(new Cheque() { Account= "2", Serial= "2", StartDate =
"20090320" });
list.Add(new Cheque() { Account= "2", Serial= "1", StartDate =
"20100417" });
list.Add(new Cheque() { Account= "2", Serial= "2", StartDate =
"20100417" });
list.Add(new Cheque() { Account= "2", Serial= "1", StartDate =
"20120314" });
list.Add(new Cheque() { Account= "2", Serial= "2", StartDate =
"20120314" });
expected list only tow set with nearest date from each AccountNumber
Acc Serial Date
"1", "1", "20120120" //first resultSet with AccountNumber= 1
"1", "2", "20120120"
"1", "3", "20120120"
"1", "1", "20110120" //second resultset with AccountNumber= 1
"1", "2", "20110120"
"1", "3", "20110120"
"2", "1", "20120314" //first resultSet with AccountNumber= 2
"2", "2", "20120314"
"2", "1", "20100417" //second resultset with AccountNumber= 2
"2", "2", "20100417"
plz help me how can query this with linq how group by (or distinct ) and
take tow first set, like this

No comments:

Post a Comment