Entity Framework In MVC - Part Seven

In the previous article, I explained how to perform CRUD and how to perform searching functionality using the code-first approach, and Repository using a single table. In this article, I will explain how to create relationships among more than one table. Let’s see step by step.

See the previous articles for basic details.

• Entity Framework in MVC - Part One
• Entity Framework in MVC - Part Two
• Entity Framework in MVC - Part Three
• Entity Framework in MVC - Part Four
• Entity Framework in MVC - Part Five
• Entity Framework in MVC - Part Six

Entity framework supports three types of relationships,

1. One-to-Many,
2. One-to-One
3. Many-to-Many.

Now, first, we will start with one-to-many relationships. In this article, we will create two classes, first is Employee and second is Education.

 

In the above figure, Employee and Education entities have a one-to-many relationship denoted by multiplicity where 1 is for One and * is for Many. This means that Employee can have many Education details whereas Eductation can associate with only one Employee.

Generally, when we create relationships then we have to create first class and we have to add properties, and after that we have to decorate with Attributes as needed.

So now we will see a practical example.

Open Visual Studio and go to File >> Project >> Web application and click OK. Then, it will open a window. In that window, select MVC or Empty.

Now we will add two classes in the Model folder, Employee, and Education

Employee.cs

[Table("TblEmployee")]
 public class Employee
 {
     [Key]
     public int EmpId { get; set; }
     [Required()]
     [StringLength(100, MinimumLength = 4)]
     public string Name { get; set; }
     [Required()]
     [StringLength(200, MinimumLength = 10)]

     public string Address { get; set; }
     [Required()]
     [StringLength(200, MinimumLength = 5)]
     public string Email { get; set; }
     [Required()]
     [StringLength(10, MinimumLength = 10)]

     public string MobileNo { get; set; }
     public List<Education> educations { get; set; }
 }

Educations.cs

[Table("TblEducation")]
lic class Education
 {
     public int EducationId { get; set; }
     [Required()]
     [StringLength(100, MinimumLength = 4)]
     public string EduName { get; set; }
     [Required()]
     [StringLength(100, MinimumLength = 4)]
     public string UniversityName { get; set; }
     [Required()]
     [DataType(DataType.Date)]
     public DateTime PassOut { get; set; }
     public Employee employee { get; set; }
 }

We can see the relationship.

 

After that, we will update the database using data migration.

So, Go to tool >>NuGet Package Manager >> Package Manager Console and type the below command

Update-Database

 

After that, we will check in SQL server.

 

And we can see the relationship. Right click on tblEducation table and go design and right click employee_EmpId and see the relation.

 

Now, we will go to project and open EmpDataContextInitializer class and set some default records.

public class EmpDataContextInitializer : DropCreateDatabaseAlways<EmpDataContext>
  {
      protected override void Seed(EmpDataContext context)
      {

          Employee empObj = new Employee {EmpId=1, Name = "Mithilesh", Address = "Hyderabad Telangana", Email = "[email protected]", MobileNo = "9823423432" };

          context.employees.Add(empObj);

          context.educations.Add(new Education()
          {
              EmpId = 1,
              EduName = "BCA",
              UniversityName = "Patna University",
              PassOut = Convert.ToDateTime("05-07-2010")


          });
          context.educations.Add(new Education()
          {
              EmpId = 1,
              EduName = "MCA",
              UniversityName = "BPUT University",
              PassOut = Convert.ToDateTime("05-07-2010")
          });
          Employee empObj1 = new Employee {EmpId=2, Name = "Rohan", Address = "Banglore Karnatka", Email = "[email protected]", MobileNo = "9823423432" };

          context.employees.Add(empObj1);

          context.educations.Add(new Education()
          {
              EmpId = 2,
              EduName = "MCA",
              UniversityName = "Kuvempu University",
              PassOut = Convert.ToDateTime("05-07-2010")
          });
          context.SaveChanges();
      }
  }

After that we will go to the Global.asax file and add the below line inside the Application_Start().

Database.SetInitializer<EmpDataContext>(new EmpDataContextInitializer());

Now, go to the model folder and add EmpDataContext class and write the below code.

public class EmpDataContext : DbContext
    {
        public EmpDataContext()
            : base("name=MySqlConnection")
        {

        }
        public DbSet<Employee> employees { get; set; }
        public DbSet<Education> educations { get; set; }
    }

After that we will go to model folder and will add an Employee Repository class and add the below code.

public class Repository<T> where T : class
{
    EmpDataContext objDataContext = null;
    protected DbSet<T> DbSet
    {
        get;set;
    }
    public Repository()
    {
        objDataContext = new EmpDataContext();
       DbSet = objDataContext.Set<T>();
    }

    public Repository(EmpDataContext objDataContext)
    {
        this.objDataContext = objDataContext;
    }
    public List<T> GetAll()
    {
        return DbSet.ToList();
    }
  public void Delete(T entity)
    {
        DbSet.Remove(entity);
    }
    public T GetById(int id)
    {
        return DbSet.Find(id);
    }
    public void SaveChanged()
    {
        objDataContext.SaveChanges();
    }
}

Now, we will add a controller and create methods for employee details and according to the employee we have to see education details.

public class DemoController : Controller
  {
      EmployeeRepository objRepository = new EmployeeRepository();
      // GET: Demo
      public ActionResult EmpDetails()
      {
         return View(objRepository.GetAll());

      }
      public ActionResult EduDetails(string id)
      {
          int empId = Convert.ToInt32(id);
          var emp = objRepository.GetById(empId);
          return View(emp);
      }
      }
blic ActionResult Delete(string id)
      {
          int empId = Convert.ToInt32(id);
          var emp = objRepository.GetById(empId);
          return View(emp);
      }
      [HttpPost]
      public ActionResult Delete(Employee objEmp)
      {
          var emp = objRepository.GetById(objEmp.EmpId);
          objRepository.Delete(emp);
          objRepository.SaveChanged();
          return View("EmpDetails");
      }

  }

Now, we will add a view for Employee details and Education details.

So, for that first Right click on EmpDetails and add HTML code for the view.

@model IEnumerable<Example3.Models.Employee>

<p>
    @Html.ActionLink("Create New", "Create")
</p>

<table class="table">
    <tr class="btn-primary">
        <th>
            @Html.DisplayNameFor(model => model.EmpId)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Address)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Email)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.MobileNo)
        </th>
        <th>Action</th>
    </tr>

@foreach (var item in Model) {
    <tr class="btn-info">
        <td>
            @Html.DisplayFor(modelItem => item.EmpId)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Address)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Email)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.MobileNo)
        </td>
        <td>
            @Html.ActionLink("Details", "Details", new { id = item.EmpId })
        </td>
    </tr>
}

</table>

And then we will add Education details view page. Right click and add HTML code.

@model Example3.Models.Employee

<div>
    <h4>Employee</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.Name)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Name)
        </dd>

      <dd>
          <ul>
              <b>Education Details</b>
              @foreach (var edu in Model.educations)
              {
                  <li>@edu.EduName,@edu.UniversityName,@edu.PassOut</li>

              }
          </ul>
      </dd>

    </dl>
</div>

Now, finally run the project and see the output.  We get an error.

 

Here it's giving the error “Object reference not set to an instance of an object”

Why did we get a null reference error?

Entity Framework uses lazy loading by default.

It is the default behavior of an Entity Framework. “Lazy loading” means that the related data is transparently loaded from the database when the navigation property is accessed. It delaying the loading of related data until you specifically request for it. For more information click this link.

Here, Employee entity contains the Education entity. In the lazy loading, the context first loads the Employee entity data from the database, and then it will load the Education entity when we access the Education property.

How do we support lazy loading?

Mark all complex type properties as virtual.

Can we disable lazy loading?

Ans: Yes

Now how do we solve that?

• Add a property for the key of the primary object:- EmpId for Employee
• Entity Framework will automatically pick this up based on convention:- Use the ForignKeyAttribute if we need to change the name

Now, again go back in our project and make it virtual of reference properties in both Employee and Education and then add EmpId inside the Education details.

So, first open the Education class and change it to virtual like the below example.

 

Again open Employee class and change it to virtual.

Now, run the project and see the output.

When we will click details then we will see the output like below.

Now check in the database also.

 

Now we will delete our records so first, we have to make sure Cascade delete is set to true.

So, open sql server and right click on tblEducation table and right click on EmpId and select relationship and go to Insert and Update specific option and set Cascade in Delete Rule.

After that, save the table and go in our application output, click on Delete button, and after that see the output.

Summary

In this article, we saw how to create one-to-many relationships using a Code First approach. In our next article, we will see one-to-one and many-to-many relationships.