LINQ Query for getting count in ASP.Net MVC 5 Application

Question

Need help in writing LINQ Query to get count of employee based on department.

Department Model

public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Employee> Employees { get; set; }
}

Employee Model

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int DepartmentId { get; set; }
    public Department Department { get; set; }
}

MyViewModel

public class MyViewModel
{
    public string Department { get; set; }
    public int count { get; set; }
}

LINQ Query

    public ActionResult shows()
    {
        //one department can have many employees and one employee can only be parrt of one department
        // below LINQ query fetches the count of employees belonging to each department
        var x = _context.Employees.Include("Department").GroupBy(e => e.DepartmentId)
     .Select(y=> new MyViewModel{
             Department= y.Key, // ERROR HERE AS Cannot type cast string to integer
            count = y.Count()               
        }).ToList();
     // code removed for brevity
        return Content("x");
    }

Output Expected

Department                 Count (or employees)

Human Resource               10

Information Tech              5

Question How to write a LINQ Query to get the output as above. Please guide me.

Department= y.First().Department.Name, (assumes Department contains a property named Name) — user3559349
– user3559349, Commented Jul 22, 2017 at 7:42
@StephenMuecke: I am really sorry, but I could not follow you. In this tutorial "youtube.com/…" at 1:57 minute its working fine for the tutor. — Unbreakable
– Unbreakable, Commented Jul 22, 2017 at 7:47
Change Department= y.Key to Department= y.First().Department.Name — user3559349
– user3559349, Commented Jul 22, 2017 at 7:48
And that tutorial is using GroupBy(e => e.Department.Name) in which case you can use Department= y.Key, — user3559349
– user3559349, Commented Jul 22, 2017 at 7:49
I got error as the "first" cannot be used a final query operation" — Unbreakable
– Unbreakable, Commented Jul 22, 2017 at 7:49

user3559349 · Accepted Answer · 2017-07-22 08:30:57Z

1

You have 2 options for modifying your query. I am assuming your Department model contains a property string Name which you want to assign to the Department property of your view model.

Get the first Department in the grouped collection and assign its Name property to your view models Department property. Note that you need to add .ToList() before .GroupBy() so that the first part of the query is executed before grouping

var x = _context.Employees
    .Include("Department")
    .ToList()
    .GroupBy(e => e.DepartmentId)
    .Select(y => new MyViewModel
    {
         Department = y.First().Department.Name,
         count = y.Count()               
    }).ToList();

Change the .GroupBy() expression to group by the Name property of the Department property

var x = _context.Employees
    .Include("Department")
    .GroupBy(e => e.Department.Name)
    .Select(y=> new MyViewModel
    {
        Department = y.Key,
        count = y.Count()               
    }).ToList();

answered Jul 22, 2017 at 8:30

user3559349

Sign up to request clarification or add additional context in comments.

9 Comments

Unbreakable Over a year ago

Kindly do explain why even after grouping by ID (logically wrong) we still get the correct output.

Mihail Shishkov Over a year ago

Caution here grouping only by the name property of the department should work appropriately only if you have an unique constraint on the Name column ensuring you have unique department names. If you have duplicate department names for some reason you'll get the sum of employees in those lets say two departments. The best thing will be to group by Id AND Name, see my answer.

Unbreakable Over a year ago

@MihailShishkov: Makes perfect sense. I am just doing it for a learning purpose, but I will pay heed to what you are telling. Thanks a ton!. I will try your solution too. :)

user3559349 Over a year ago

@MihailShishkov, You answer is grouping by the Employees DepartmentId and Employees Name (not Department Name) which is not what OP wants

Mihail Shishkov Over a year ago

In option 1 you'll have to retrieve every employee from the database which is bad. What if there are thousands of employees? Effectively what you need @Unbreakable is this SQL to be executed on the server SELECT DepId, Name, COUNT(1) AS EmplCount FROM Employees GROUP BY DepId, Name

|

Mihail Shishkov · Accepted Answer · 2017-07-22 08:45:51Z

1

public ActionResult shows()
    {
        //one department can have many employees and one employee can only be parrt of one department
        // below LINQ query fetches the count of employees belonging to each department
        var x = _context.Employees.Include("Department").GroupBy(e => new { e.DepartmentId, e.Department.Name})
     .Select(y=> new MyViewModel{
             Department= y.Key.Name
            count = y.Count()               
        }).ToList();
     // code removed for brevity
        return Content("x");
    }

the key here is to group by DepartmentId AND Name

edited Jul 22, 2017 at 8:45

answered Jul 22, 2017 at 8:36

Mihail Shishkov

16.2k8 gold badges52 silver badges63 bronze badges

Comments

Mihail Stancescu · Accepted Answer · 2017-07-22 07:42:56Z

0

Try it like this:

public ActionResult shows()
{
    //one department can have many employees and one employee can only be parrt of one department
    // below LINQ query fetches the count of employees belonging to each department
    var x = _context.Employees.Include("Department").GroupBy(e => e.DepartmentId)
 .Select(y=> new MyViewModel{
         Department= y.Key.DepartmentName, // or whatever property you have for storing the name
        count = y.Count()               
    }).ToList();
 // code removed for brevity
    return Content("x");
}

answered Jul 22, 2017 at 7:42

Mihail Stancescu

4,1681 gold badge18 silver badges22 bronze badges

Comments

MSH · Accepted Answer · 2017-07-23 11:11:47Z

0

you can use this code :

using System.Linq;
using System.Web.Mvc;
using MvcApplication1.Models;

namespace MvcApplication1.Controllers
{
     
     public class HomeController : Controller
     {
          public ActionResult Index()
          {
               var dataContext = new MovieDataContext();
               int count = (from row in dataContext.Movies
                    where row.IsEnquiry == true
                 select row).Count();
               ViewBag.ItemCount = count;
               return View();
          }
     }
}

edited Jul 23, 2017 at 11:11

answered Jul 23, 2017 at 4:40

MSH

1932 silver badges16 bronze badges

1 Comment

Govind Samrow Over a year ago

Explain your answer

Collectives™ on Stack Overflow

LINQ Query for getting count in ASP.Net MVC 5 Application

4 Answers 4

9 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related