CODEWITHSUNDEEP
c#linq-to-sql
Svish
Svish

Reputation: 157981

C#, Linq2SQL: Getting the highest of each group

Lets say I have this table:

PetID    Name    Age    Weight

How can I in a Linq2SQL get the name, age and weight of each pet that is the heaviest in its age group?

So if I have one pet of age 5 and four pets of age 2, I would like the Name, Age and Weight of the one that is age 5, and the one of the four with age 2 that is the heaveiest of those four.

This should probably not be too difficult, but I am not too into query thinking yet. Working on it though. And this is one kind of problem that I will need many times, but doesn't quite manage to wrap my head around how to do...

I think it would include using the Max or a > operator, and some sort of grouping or joining, but thats about as far as I've gotten....

Thanks for the answers. All of them put me on the right track when it comes to the GroupBy stuff. The one I marked as an answer, was the one that was closest to what I ended up with.

Upvotes: 1

Views: 416

Answers (3)

bruno conde
bruno conde

Reputation: 48265

It could be something like this:

var pets =  from pet in petSource
            group pet by pet.Age into g
            let max = g.OrderByDescending(p => p.Weight).FirstOrDefault<Pet>()
            select new { Name = max.Name, Age = max.Age, Weight = max.Weight };

Upvotes: 0

Hath
Hath

Reputation: 12769

how is this for you.

public class Pet
{
     public int PetID { get; set; }
     public string Name { get; set; }
     public int Age { get; set; }
     public decimal Weight { get; set; }
}

List<Pet> pets = new List<Pet>() 
{ 
    new Pet {PetID = 1, Age = 5, Name = "Bob", Weight = 4M },
    new Pet {PetID = 2, Age = 5, Name = "Brad", Weight = 3M },
    new Pet {PetID = 3, Age = 2, Name = "Troy", Weight = 1M },
    new Pet {PetID = 4, Age = 2, Name = "Dave", Weight = 2M },
    new Pet {PetID = 5, Age = 2, Name = "Simon", Weight = 3M }
};

var fatPetsByAge = from pet in pets
                   group pet by pet.Age into petsByAge
                   select new
                   {
                       Age = petsByAge.Key,
                       FatPet = petsByAge.FirstOrDefault(
                                f => f.Weight == petsByAge.Max(m => m.Weight))
                   };

foreach (var fatty in fatPetsByAge)
{
    Console.WriteLine("PetID: {0}, Age: {1} Name: {2} Weight: {3}",
        fatty.FatPet.PetID,
        fatty.FatPet.Age,
        fatty.FatPet.Name,
        fatty.FatPet.Weight);
}

Upvotes: 1

Christian C. Salvad&#243;
Christian C. Salvad&#243;

Reputation: 827208

Try this query:

var petsGroup = ctx.Pets.GroupBy(p => p.Age)
                        .Select(g => g.First(x => x.Weight == g.Max(y => y.Weight)));

It groups the Pets by Age, and then selects the first element on the group, that matchs with the maximum weight of the group.

Upvotes: 1

Related Questions