Multi tool use

ASP.net.MVC 5 create a page which will display sql query

I want to learn how can I add to my controller some SQL query but I don't know how to do it properly.

My controller is

public ActionResult Index()
{
return View(db.Student.ToList());
}


By default page is giving me the next data.

ID, NAME, STATUS
1 Bella, 5
2 Bella, 5
3 Bella, 7


I want to add the next SQL query to the controller which will give me data that I want it on page to display.

(select distinct id,name,max(status) from students group by id,name)



ID, NAME, STATUS

3 Bella, 7


2 Answers
2

I suggest you use a linq with a group-by clause as below;

var students = from s in db.Student
group s by s.name into groupedResult
select new
{
Name = groupedResult.Key,
Max_Status = groupedResult.Max(g => g.Status)
} ;

If you want to group data by Name and display Maximum value of Status with linq then you can do as below.

Name

Status

var students = db.Student.GroupBy(i => i.Name)
.Select(g => new Student()
{
Name = g.Key,
Status = g.Max(row => row.Status),
Id = g.Max(row => row.Id)
}).ToList();
return View(students);


Name = g.Key

Name

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.