How to group rows based on condition using LINQ in MVC

Question

0.00/5 (No votes)

See more:

Hi I have a web application in mvc in which i have teacher and courses , as well as teacherVsCourses Table , in teachervscourses table i am saving assigned courses to each teacher , right now I have a teacher for which i have assigned five courses to him and in db i have five rows , I want it to be one row with teacher name and five courses with comma separated list.

<pre> public ActionResult Index(string sortOrder, string currentFilter, string SearchString, int? page)
        {
       

            ViewBag.Course_Id = _ITeacherCoruses.PopulateCourses();
            ViewBag.Teacher_Id = _ITeacherCoruses.PopulateTeachers();
            ViewBag.Semester_Id = _ITeacherCoruses.PopulateSemsters();
            
            ViewBag.CurrentSort = sortOrder;
            ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
            ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";
            if (SearchString != null)
            {
                page = 1;
            }
            else
            {
                SearchString = currentFilter;
            }

            ViewBag.CurrentFilter = SearchString;


            var dept = from s in db.Teacher_courses
                       select s;


       
           
         
            dept =dept.Include(t => t.course).Include(t => t.regs).Include(t => t.semesterName).OrderBy(t => t.Teacher_Id);
            if (!String.IsNullOrEmpty(SearchString))
            {
                dept = dept.Where(s => s.course.Course_Name.Contains(SearchString));
                //   || s.FirstMidName.Contains(searchString));
            }
            switch (sortOrder)
            {
                case "name_desc":
                    dept = dept.OrderByDescending(s => s.course.Course_Name);
                    break;
                case "Date":
                    dept = dept.OrderBy(s => s.Course_Assignment_date);
                    break;
                case "date_desc":
                    dept = dept.OrderByDescending(s => s.Course_Assignment_date);
                    break;
                default:
                    dept = dept.OrderBy(s => s.course.Course_Name);
                    break;
            }
            int pageSize = 5;
            int pageNumber = (page ?? 1);


            //  var teacher_courses = db.Teacher_courses.Include(t => t.course).Include(t => t.regs).Include(t => t.semesterName).OrderBy(t=>t.Teacher_Id);
            dept = dept.Include(t => t.course).Include(t => t.regs).Include(t => t.semesterName).OrderBy(t => t.Teacher_Id);
            //return View(groupedUsers);
             return View(productsArray.ToPagedList(pageNumber, pageSize));
           // return View(teacher_courses.ToList());
        }

What I have tried:

I have grouped the record but do not know how to use it on view , from below user mean the teacher and hobby means the courses

<pre> Teacher_courses[] productsArray = dept.ToArray();
            var groupedUsers = from u in productsArray
                               group u by u.regs.FirstName into g
                               select new
                               {
                                   Name = g.First<Teacher_courses>().Teacher_Id,
                                   Hobby = g.Select(u => u.Course_Id)
                               };

           
            foreach (var user in groupedUsers)
            {
                Console.WriteLine("Teacher_ID: {0}", user.Name);
                foreach (var hobby in user.Hobby)
                {
                    Console.WriteLine("Course_ID: {0}", hobby);
                }
                
            }

Posted 5-Jan-20 23:18pm

Malikdanish

Updated 6-Jan-20 11:36am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2020-01-06T11:36:00

Solution 1

This will return grouped data. How you use it is up to you. There's no rule you HAVE to use LINQ, you can write views or stored procs and call those if you prefer

Posted 6-Jan-20 11:36am

Christian Graus

Comments

Malikdanish 6-Jan-20 22:23pm

below is my view can you update it as per my return data
@*@model IEnumerable<cms_monitoring.models.teacher_courses>*@
@using PagedList;
@using PagedList.Mvc;
@using CMS_Monitoring.Helpers;
@model PagedList.IPagedList<cms_monitoring.models.teacher_courses>
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_Layout2.cshtml";
}

Index

setTimeout(function () {
$('#successMessage').fadeOut('slow');
}, 12000); // <-- time i

@Html.ActionLink("Create New", "Create", "Departments", new { @class = "btn btn-info" })

@if (TempData["UpdatedMessage"] != null)
{
@TempData["UpdatedMessage"]
}

@Html.ActionLink("Create New", "Create")

@using (Html.BeginForm())
{

Find by Department name: @Html.TextBox("SearchString")

if (TempData["DeleteDepartment"] != null)
{
@TempData["DeleteDepartment"]
}

@foreach (var item in Model)
{

}

@Html.DisplayNameFor(model => model[0].course.Course_Name)
@Html.DisplayNameFor(model => model[0].regs.FirstName)
@Html.DisplayNameFor(model => model[0].semesterName.Semester_Title)
@Html.DisplayNameFor(model => model[0].is_on_Substitute)
@Html.DisplayNameFor(model => model[0].Active)
@Html.DisplayNameFor(model => model[0].Course_Assignment_date)
@Html.DisplayNameFor(model => model[0].Date_Modified)
@Html.DisplayNameFor(model => model[0].Added_By)

@Html.DisplayFor(modelItem => item.course.Course_Name)
@Html.DisplayFor(modelItem => item.regs.FirstName)

@Html.DisplayFor(modelItem => item.semesterName.Semester_Title)
@Html.DisplayFor(modelItem => item.is_on_Substitute)
@Html.DisplayFor(modelItem => item.Active)
@Html.DisplayFor(modelItem => item.Course_Assignment_date)
@Html.DisplayFor(modelItem => item.Date_Modified)
@Html.DisplayFor(modelItem => item.Added_By)
@Html.ActionLink("Edit", "Edit", new { id = item.Id }) |
@Html.ActionLink("Details", "Details", new { id = item.Id }) |
@Html.ActionLink("Delete", "Delete", new { id = item.Id })

}
Page @(Model.PageCount < Model.PageNumber ? 0 : Model.PageNumber) of @Model.PageCount

@Html.PagedListPager(Model, page => Url.Action("Index",
new { page, sortOrder = ViewBag.CurrentSort, currentFilter = ViewBag.CurrentFilter }))

Christian Graus 6-Jan-20 22:32pm

This makes no sense at all. It's not helpful. Why on earth are you using ASP.NET?

Your view, if you have one, is SQL. This is an ASPX file.

Malikdanish 6-Jan-20 23:19pm

this is the razor view not aspx , I just want to say as i am grouping the records in the loop so it is not in a form which could be passed as data object to the view.

Christian Graus 6-Jan-20 23:24pm

OK, so group the records in SQL, I said that to start with

Malikdanish 6-Jan-20 23:41pm

I just want to ask how do i send this data to view ?

Malikdanish 7-Jan-20 0:34am

Hi i have changed my view code to group the data there
@foreach (var item in Model)
{
@foreach(var group in Model.GroupBy(model=> model.regs.FirstName))
{
@Html.Encode(group.Key)
@foreach(var tm in group)
{
@Html.Encode(tm.course.Course_Name)}

}
@*@Html.DisplayFor(modelItem => item.course.Course_Name)*@
but check it's out put is wrong i am not sure why , it is showing five courses for ALAM but in db there are 4 rows for this user,secondly the grouping is still repeating the number of rows which is wrong
https://imgur.com/33BJUjA

Christian Graus 7-Jan-20 0:38am

Again, write a SQL view that groups your data the way you want it and call that....

Malikdanish 7-Jan-20 0:41am

By sql i have done it using STuff function I want to do it with entity framework only using Linq , no matter either to make it from controller or from view i do not want to call the stored procedure i am using Entity framework using linq

Christian Graus 7-Jan-20 1:22am

OK, well, if you're not willing to use the tools available to you, then I guess play with your LINQ statement until it returns the data you expect. If you want help, post the data, your code, and what you think is wrong in the result

Malikdanish 7-Jan-20 1:31am

Here is my index controller code :
public ActionResult Index(string sortOrder, string currentFilter, string SearchString, int? page)
{

ViewBag.Course_Id = _ITeacherCoruses.PopulateCourses();
ViewBag.Teacher_Id = _ITeacherCoruses.PopulateTeachers();
ViewBag.Semester_Id = _ITeacherCoruses.PopulateSemsters();
// ViewBag.Teacher_Id = new SelectList(db.Registration, "User_Id", "FirstName");
ViewBag.CurrentSort = sortOrder;
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";
if (SearchString != null)
{
page = 1;
}
else
{
SearchString = currentFilter;
}

ViewBag.CurrentFilter = SearchString;

var dept = from s in db.Teacher_courses
select s;

dept =dept.Include(t => t.course).Include(t => t.regs).Include(t => t.semesterName).OrderBy(t => t.Teacher_Id);
if (!String.IsNullOrEmpty(SearchString))
{
dept = dept.Where(s => s.course.Course_Name.Contains(SearchString));
// || s.FirstMidName.Contains(searchString));
}
switch (sortOrder)
{
case "name_desc":
dept = dept.OrderByDescending(s => s.course.Course_Name);
break;
case "Date":
dept = dept.OrderBy(s => s.Course_Assignment_date);
break;
case "date_desc":
dept = dept.OrderByDescending(s => s.Course_Assignment_date);
break;
default:
dept = dept.OrderBy(s => s.course.Course_Name);
break;
}
int pageSize = 5;
int pageNumber = (page ?? 1);

// var teacher_courses = db.Teacher_courses.Include(t => t.course).Include(t => t.regs).Include(t => t.semesterName).OrderBy(t=>t.Teacher_Id);
dept = dept.Include(t => t.course).Include(t => t.regs).Include(t => t.semesterName).OrderBy(t => t.Teacher_Id);
//return View(groupedUsers);
return View(productsArray.ToPagedList(pageNumber, pageSize));
// return View(teacher_courses.ToList());
}
----
Here is my index view :
@*@model IEnumerable<cms_monitoring.models.teacher_courses>*@
@using PagedList;
@using PagedList.Mvc;
@using CMS_Monitoring.Helpers;
@model PagedList.IPagedList<cms_monitoring.models.teacher_courses>
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_Layout2.cshtml";
}

Index

setTimeout(function () {
$('#successMessage').fadeOut('slow');
}, 12000); // <-- time i

@Html.ActionLink("Create New", "Create", "Departments", new { @class = "btn btn-info" })

@if (TempData["UpdatedMessage"] != null)
{
@TempData["UpdatedMessage"]
}

@Html.ActionLink("Create New", "Create")

@using (Html.BeginForm())
{

Find by Department name: @Html.TextBox("SearchString")

if (TempData["DeleteDepartment"] != null)
{
@TempData["DeleteDepartment"]
}

@foreach (var item in Model)
{
}

@Html.DisplayNameFor(model => model[0].course.Course_Name)
@Html.DisplayNameFor(model => model[0].regs.FirstName)
@Html.DisplayNameFor(model => model[0].semesterName.Semester_Title)
@Html.D

Malikdanish 7-Jan-20 1:34am

here is the result i am getting
https://imgur.com/5O4J7vc
here is the result i want to get I want one teacher's all courses on same row

Christian Graus 7-Jan-20 1:39am

https://docs.microsoft.com/en-us/dotnet/api/system.linq.enumerable.groupby?view=netframework-4.8