Linq not grouping

Question

Given the data below, I am trying to write a LINQ statement that will group by ParentProductId, and select the maximum EndDate if there is more than one item in the group.

Since the data listed below has two items with the same ParentProductId, I would expect three records to be returned (and for the "2020" date to be used for the ParentProductId = 1 group, not "2019"). However, the LINQ statement that I have is still returning all four records. What am I doing wrong?

Data:

Subscription.Add(new Subscription() { CustomerId = 555, ParentProductId = 37 , EndDate= null});
Subscription.Add(new Subscription() { CustomerId = 555, ParentProductId = 38 , EndDate = null });
Subscription.Add(new Subscription() { CustomerId = 555, ParentProductId = 1  , EndDate = new DateTime(2019, 11, 28) });
Subscription.Add(new Subscription() { CustomerId = 555, ParentProductId = 1, EndDate = new DateTime(2020, 1, 28) });

LINQ Statement:

var IndividualSubscription = (from s in db.Subscriptions
                              join ptp in db.ProductToProducts on s.ProductToProductId equals ptp.ProductToProductId
                              join p in db.Products on ptp.ParentProductId equals p.ProductId
                              where SubscriptionIds.Contains(s.OriginalSubscriptionId)
                              && s.CustomerId == CustomerId
                              group new  {ptp.ParentProductId, s.EndDate }
                              by new 
                              {
                              s.CustomerId,
                              ptp.ParentProductId,
                              p.Name,
                              s.EndDate,
                              } into grp
                              select new NCCN.Model.IndividualGroupSubscription
                                {
                                    CustomerId = grp.Key.CustomerId,
                                    ParentProductId = grp.Key.ParentProductId,
                                    ParentProductName = grp.Key.Name,
                                    EndDate = grp.Max(p => p.EndDate),
                                }).ToList();

user_2748412 · Answer

Note sure why your linq is that overkill but following your written requirement and not your code what you need is a million times shorter than what you have wrote

// get grouped by product id
var result = Subscription.GroupBy(s => s.ParentProductId) 

// select to output something for each group. What you want is
// order descending all elements and pick the first which is the highest

                         .Select(grp => grp.OrderByDescending(o => o.EndDate).FirstOrDefault())

// returned as a list

                         .ToList();

user_1255023 · Answer

You group for {ptp.ParentProductId, s.EndDate }.

Group only for ParentProductId, if you need 3 rows result.

var subscription = new List(){new Subscription() { CustomerId = 555, ParentProductId = 37 , EndDate= null}
    ,new Subscription() { CustomerId = 555, ParentProductId = 38 , EndDate = null }
    ,new Subscription() { CustomerId = 555, ParentProductId = 1  , EndDate = new DateTime(2019, 11, 28) }
    ,new Subscription() { CustomerId = 555, ParentProductId = 1, EndDate = new DateTime(2020, 1, 28) }
};

var query = subscription
    .GroupBy(x=> x.ParentProductId)
    .Select(x=> new {x.Key, EndDate = x.Max(s=>s.EndDate)});

Linq not grouping

2 Answers