ASP.NET MVC Linq query to SQL DB return Sum by Name-CodePudding

I have DB with table that contains Scores of students. Every record contains StudentName (nvarchar) and Score (int from 1 to 5) and other data related to record (date, discipline name etc)

------------------------------------------------

StudentName   Score   Discipline  Date

Bob             5        Asp.net       05/23/21
Bob             5        Html          05/23/21
Bob             5        C#            05/23/21
John            4        Asp.net       05/23/21
John            4        C#            05/23/21
Michael         3        Asp.net       05/23/21
Michael         3        Html          05/23/21
Michael         4        C#            05/23/21

I am trying to return result view where all students will be visible with Sum of their Scores by Name.

---------------------------

StudentName   ScoreSum

Bob             15
John            8
Michael         10

I've tried following solution, but getting error with &&:

Operator 'operator' cannot be applied to operands of type 'type' and 'type' Code is part of Controller and public ActionResult ViewStudentsBySumOfScore() is a method of it.

public ActionResult ViewStudentsBySumOfScore()
{
var db = new AllDbContext();
var ScoreSum = db.Scores
.Select(x =\> x.StudentName && x.Score)
.Sum();

            return View();
        }

how to change code to get result as mentioned above?

CodePudding user response：

You should use .GroupBy() to group all score records by StudentName before creating one object per student and using .Sum() to generate the score sum for each student.

If you have a class for storing the score sum for a student, e.g. StudentScoreSum:

public class StudentScoreSum
{
    public string StudentName { get; set; }
    public int ScoreSum { get; set; }
}

, the implementation could be as follows:

List<StudentScoreSum> scoreSum = db.Scores
    .GroupBy(s => s.StudentName)
    .Select(scoresByStudent => new StudentScoreSum {
        StudentName = scoresByStudent.Key,
        ScoreSum = scoresByStudent.Sum(s => s.Score)})
    .ToList();

Example fiddle here.

The `.GroupBy()` return value

Assuming the data type of the entries in the Scores database table is in fact Score, the .GroupBy() operation returns an IEnumerable<Grouping<string, Score>>.

In the .Select() operation, scoresByStudent is hereby a Grouping<string, Score>;

string being the data type of the group key
Score being the data type of the elements in the group's object collection

Based on your example score data, the result from the .GroupBy() operation will be three groupings. They can be visualized as:

Key	Object collection
"Bob"	`{ StudentName = "Bob", Score = 5 }` `{ StudentName = "Bob", Score = 5 }` `{ StudentName = "Bob", Score = 5 }`
"John"	`{ StudentName = "John", Score = 4 }` `{ StudentName = "John", Score = 4 }`
"Michael"	`{ StudentName = "Michael", Score = 3 }` `{ StudentName = "Michael", Score = 3 }` `{ StudentName = "Michael", Score = 4 }`

For each of these three groups, a StudentScoreSum object is created, using the group's key as the StudentName and summing the Score values of all elements in the group's object collection to produce the ScoreSum.

The .GroupBy() return value

The `.GroupBy()` return value