CODEWITHSUNDEEP
c#linqlinq-to-entities
Vitor Reis
Vitor Reis

Reputation: 989

Getting only one record from parent with multiple child in Linq (Distinct)

I'm trying to fill in a Typed GridView with a list of products and it's last status.

This first query works OK and returns one record for each Product because of the Distinct() clause. But now I need to show the LAST Situation and get some data from other tables to display on a TYPED GridView.

(The ProductSituationHistory basically contains ProductId and SituationId, to keep a track of all status the product have gained...

public IQueryable<Product> SelectProducts()
            {
            var query = (from ph in db.ProductSituationHistory
                            join p in db.Products on ph.ProductId equals p.Id
                            join s in db.Situation on ph.SituationId equals s.Id
                            select  p).Distinct();
            return query;
            }

So, I created another type class called ProductGridView (I don't like working with anonymous types and the GridView would work better here if it was Strongly Typed) the query is like this:

public IQueryable<ProductGridView> SelectProducts()
    {
            var query = (from ph in db.ProductSituationHistory
                            join p in db.Products on ph.ProductId equals p.Id
                            join s in db.Situation on ph.SituationId equals s.Id
                            select new ProductGridView
                            {
                                Id = p.Id,
                                Name = p.Name,
                                Situation = s.Description,
                                Manufacturer = p.Manufacturer.Name

                            }).Distinct();


            return query;
    }

The problem is now the Distinct() won't work, because, obviously, none of the ProductGridView items will be equal (the SituationId changes). How can I get a list of all products while getting only the last record from ProductSituationHistory for each of them?

Upvotes: 1

Views: 1608

Answers (1)

Servy
Servy

Reputation: 203827

How can I get a list of all products while getting only the last record from ProductSituationHistory for each of them?

This means we want to start with Product, rather than ProductSituationHistory, and do a group join. When you do that you'll have all matching history items as a collection, to which you can, for example, get the last item from.

var query = from p in db.Products 
                join ph in db.ProductSituationHistory 
                on p.Id equals ph.ProductId into history
                let lastHistory = history
                    .OrderByDescending(h=>h.Id)
                    .FirstOrDefault()
                join s in db.Situation 
                on lastHistory.SituationId equals s.Id
                select new ProductGridView
                {
                    Id = p.Id,
                    Name = p.Name,
                    Situation = s.Description,
                    Manufacturer = p.Manufacturer.Name,
                };

Upvotes: 2

Related Questions