Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Part 29 - Using data transfer object as the model in mvc

Suggested Videos 
Part 26 - Customizing the auto-generated index view
Part 27 - Customizing the auto-generated create view
Part 28 - Customizing the auto-generated edit view



In this video we will discuss, using data transfer object as the model in mvc. Please watch Part 28, before proceeding. 

Let's say the business requirement is such that, we want to display total number of employees by department as shown below. At the moment, either the Employee or Department class does not have Total property. This is one example, where a Data Transfer Object can be used as a model.
Employees By Department



Right click on the "Models" folder and add a class with name="DepartmentTotals.cs". Copy and paste the following code.
public class DepartmentTotals
{
    public string Name { get; set; }
    public int Total { get; set; }
}

Now add the following "EmployeesByDepartment" controller action method to EmployeeController class.
public ActionResult EmployeesByDepartment()
{
    var departmentTotals = db.Employees.Include("Department")
                                .GroupBy(x => x.Department.Name)
                                .Select(y => new DepartmentTotals 
                                { 
                                    Name = y.Key, Total = y.Count() 
                                }).ToList();
    return View(departmentTotals);
}

At this point, build the solution, so that the newly added DepartmentTotals class is compiled.

Now right click on "EmployeesByDepartment" action method in "EmployeeController" and select "Add View" from the context menu.
View name = EmployeesByDepartment
View engine = Razor
Select "Create a strongly-typed view" checkbox
Model class = DepartmentTotals
Model class = DepartmentTotals

To list the employees in ascending order of total employee, use OrderBy() LINQ method as shown below.
var departmentTotals = db.Employees.Include("Department")
                            .GroupBy(x => x.Department.Name)
                            .Select(y => new DepartmentTotals 
                            { 
                                Name = y.Key, Total = y.Count() 
                            }).ToList().OrderBy(y => y.Total);

To sort the list in descending order use, OrderByDescending() LINQ method.
var departmentTotals = db.Employees.Include("Department")
                            .GroupBy(x => x.Department.Name)
                            .Select(y => new DepartmentTotals 
                            { 
                                Name = y.Key, Total = y.Count() 
                            }).ToList().OrderByDescending(y => y.Total);
            return View(departmentTotals);

14 comments:

  1. Hello Sir G
    As i have both the version of VS, i.e VS 2010 & VS 2012 in latest version mvc 3&4 are included by default so i think there is no need to install separately mvc 4.
    please tell me mian feature of vs 2012 over vs 2010/
    Dear Sir G you are doing a very good and outstanding job.
    May GOD bless you more and more and.........
    Thank you so much Sir G!!!

    ReplyDelete
  2. Hi Venkat,

    I am getting an error
    like this


    Server Error in '/' Application.

    The model item passed into the dictionary is of type 'System.Collections.Generic.List`1[MvcDemo3.Models.Employee]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[MvcDemo3.Models.DepartmentTotals]'.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.InvalidOperationException: The model item passed into the dictionary is of type 'System.Collections.Generic.List`1[MvcDemo3.Models.Employee]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[MvcDemo3.Models.DepartmentTotals]'.

    Source Error:

    An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


    please help me

    ReplyDelete
    Replies
    1. Try without setting up any data context class

      Delete
  3. Hi Prahim,

    How could I pass to the same View ID of department to be available for making a link:
    @Html.ActionLink(item.Name, "index", new { @id = item.DepId })

    Kind regards

    ReplyDelete
  4. Hello Sir ,i m getting an error while tring to create a DTO usin g code first EF ,its asking for Key ... plzz tell me how can i solve d issues ..plzz sir, upload a video by describing the DTO in details .

    ReplyDelete
  5. If you're getting an error trying to create your view, just add the [Key] attribute to the Name property of your DepartmentTotals class:

    using System.ComponentModel.DataAnnotations;

    namespace MVCDemo.Models
    {
    public class DepartmentTotals
    {
    [Key]
    public string Name { get; set; }
    public int Total { get; set; }
    }
    }

    ReplyDelete
    Replies
    1. Though I added [Key] attribute I'm getting this error:

      "One or more validation errors were detected during model generation" along with "Entity type has no key defined".

      Error is like this:

      There was an error running the selected code generator:
      'Unable to retrieve metadata for 'EntityFrameworkDemo.Models.DepartmentTotals', One or more validation errors were detected during model generation:

      Department:: EntityType 'Department' has no key Defined
      Define the key for this EntityType.
      DepartmentTotals:: EntityType 'DepartmentTotals' has no key defined. Define the key for this EntityType.
      Departments: EntityType: EntitySet 'Departments' is based on type 'Department' that has no keys defined.
      DepartmentsTotals: EntityType: EntitySet 'DepartmentTotals' is based on type 'DepartmentTotals' that has no keys defined.


      Please Help me to solve this.

      Thanks in advance.

      Delete
    2. Did you try building your project after adding the Key attribute? This error went away for me after building the project.
      Also, the Name property will not be added to the view when you generate the view, so you will have to add it manually.

      Delete
  6. Respected Venkat
    I am too much confused by this code I have made this and it is also working but I am confused that where is the query to select the data and join the two table. Please guide me that what should I do first to understand these kind of codes

    Regards

    ReplyDelete
    Replies
    1. Hi Anonymous,
      The SQL query is being executed by Entity Framework generated from LINQ you wrote.

      Delete
  7. everything is right in server when I run query ,but query resulting in total as "1" for every department in visual studio

    ReplyDelete
  8. Hi There,

    While I was trying to create an View for "EmployeesByDepartment" It was showing this error:

    There was an error running the selected code generator:
    'Unable to retrieve metadata for 'EntityFrameworkDemo.Models.DepartmentTotals', One or more validation errors were detected during model generation:

    Department:: EntityType 'Department' has no key Defined
    Define the key for this EntityType.
    DepartmentTotals:: EntityType 'DepartmentTotals' has no key defined. Define the key for this EntityType.
    Departments: EntityType: EntitySet 'Departments' is based on type 'Department' that has no keys defined.
    DepartmentsTotals: EntityType: EntitySet 'DepartmentTotals' is based on type 'DepartmentTotals' that has no keys defined.


    Please Help me to resolve this.

    Thanks in advance,
    Divya

    ReplyDelete
  9. There was an error running the selected code generator:
    'Unable to retrieve metadata for 'EntityFrameworkDemo.Models.DepartmentTotals', One or more validation errors were detected during model generation:

    Department:: EntityType 'Department' has no key Defined
    Define the key for this EntityType.
    DepartmentTotals:: EntityType 'DepartmentTotals' has no key defined. Define the key for this EntityType.
    Departments: EntityType: EntitySet 'Departments' is based on type 'Department' that has no keys defined.
    DepartmentsTotals: EntityType: EntitySet 'DepartmentTotals' is based on type 'DepartmentTotals' that has no keys defined.

    ReplyDelete
    Replies
    1. add [Key] above the name property in DepartmentTotal.cs and rebuild then add a view

      Delete

It would be great if you can help share these free resources