CODEWITHSUNDEEP
c#linq
Matthew Sawrey
Matthew Sawrey

Reputation: 105

Use LINQ to retrieve largest number from Properties of a collection, within a collection

Here's my model:

public class SeasonScore
{
    [Key]
    public int SeasonScoreID { get; set; }

    [DisplayName("Season")]
    public string season { get; set; }

    [DisplayName("Year")]
    public int year { get; set; }

    [DisplayName("Value")]
    public decimal value { get; set; }

    [DisplayName("Subject")]
    public virtual Subject Subject { get; set; }
}

public class Subject
{
    [Key]
    public int SubjectID { get; set; }

    [DisplayName("Subject")]
    public string subject { get; set; }

    [DisplayName("Scores")]
    public virtual List<SeasonScore> scores { get; set; }

    public Subject()
    {
        scores = new List<SeasonScore>();
    }
}

As you can see each instance of "Subject" contains a list of "SeasonScore". You can also see that SeasonScore has a decimal property called "value".

I have a database that stores a list of Subject objects. I want to find the largest "value" property from any of the SeasonScore instances contained within any of the Subject instances in my database.

I could do it the long way round, but I'm convinced I should be able to do it quickly using LINQ although I can't quite figure it out.

Upvotes: 2

Views: 550

Answers (3)

Fᴀʀʜᴀɴ Aɴᴀᴍ
Fᴀʀʜᴀɴ Aɴᴀᴍ

Reputation: 6251

You can use the Enumerable.Max function. It has an overload which takes a selector. After you get a list of maximum values for each subject, call the Max function again to get the largest of those values.

subjects.Select(x => x.scores.Max(y => y.value)).Max();

See it in action here.

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726889

Assuming that your classes are properly mapped to database with LINQ2SQL or Entity Framework, you can retrieve max value using Max, like this:

var maxVal = dbContext
    .Subjects
    .Max(s => s.scores.Max(v => v.value));

or

var maxVal = dbContext
    .Subjects
    .SelectMany(s => s.scores)
    .Max(v => v.value);

In both cases the query would be translated to SQL, and produce a single scalar entirely within RDBMS.

Upvotes: 2

Bewar Salah
Bewar Salah

Reputation: 567

You can get a list of max value of each Subject's Score as:

IEnumerable<decimal> max = subjects.Select(a => a.scores.Max(b => b.value));

Upvotes: 1

Related Questions