CODEWITHSUNDEEP
c#.netlinq.net-4.0lambda
huMpty duMpty
huMpty duMpty

Reputation: 14460

Select one record using multiple group by and count

I have a list of entities where the entity likes

Offer

Int OfferId
DateTime FromDate
DateTime ToDate
Bool Status

In the List<Offer>, it has duplicate OfferId as well Eg. 

Offer1 { Id=1, From=01/01/2011, To=31/01/2011, Status=true }
Offer2 { Id=1, From=01/02/2011, To=28/02/2011, Status=false}
Offer2 { Id=2, From=01/02/2011, To=28/02/2011, Status=false}
Offer3 { Id=3, From=01/01/2011, To=31/01/2011, Status=true }
Offer4 { Id=4, From=01/01/2011, To=31/01/2011, Status=true }
Offer5 { Id=1, From=01/03/2011, To=31/03/2011, Status=false}

What I am trying to do is select a list of offers related to one offerId which has the oldest and most frequent startdate.
In this case it is the once with offerId=1. Because it has the startdate 01/01/2011 which is oldest and the most frequent.
I am not sure how to put the all conditions in a one command.

I grouped the records based on offerId, but I'm not sure how to continue

var list = OfferList.GroupBy(a => a.offerId).Select(g => g.Select(s => s));

Update

Seems like bit confusion. I am trying to select list or records which satisfy the conditions. In this case answer should be

Offer1 { Id=1, From=01/01/2011, To=31/01/2011, Status=true }
Offer2 { Id=1, From=01/02/2011, To=28/02/2011, Status=false}
Offer5 { Id=1, From=01/03/2011, To=31/03/2011, Status=false}

Because, this offerid which is 1 has the oldest fromdate which is 01/01/2011 and it also has the most frequent fromdate which is 01/01/2011. (01/01/2011 is appear 3 times as the fromdate in main list )

Upvotes: 1

Views: 159

Answers (2)

Hogan
Hogan

Reputation: 70523

Here you go and here is the LinqPad test: https://gist.github.com/3207484

var r = (OfferList.GroupBy(offer => offer.id)
             .Select(group =>
                 new { offerid = group.Key,
                       offers = group.OrderBy(o => o.fromDt),
                       count = group.Count() })
             .OrderBy(g => g.offers.First().fromDt) // list with the oldest
             .ThenByDescending(g => g.count)).Dump()  // then by most
        .First().offers.First();

Upvotes: 1

Pravin Pawar
Pravin Pawar

Reputation: 2569

var q = from t in offerList
        group t by new { t.Id } into grp
        select new
        {
           grp.Key.Id,
           MinDate = grp.Min(t => t.From)
        };
var re = q.ToList().OrderBy(p=>p.MinDate).FirstOrDefault();

Not tested though

Upvotes: 1

Related Questions