Queries: Entity

Showing posts with label Entity. Show all posts

Friday, October 16, 2015

Repository Pattern with MVC and Entity Framework

In this tutorial We will learn How to create generic Repository Pattern with Asp.Net MVC and Entity Framework.

Repository Pattern is used to create an abstraction layer between data access layer and business logic layer of an application. This layer communicate with data access layer and provide data to business logic layer as per requirement.

In general Our controller action methods directly access the data context and get data from database.

The main purpose of this Repository Pattern to Isolate the data access layer and business logic layer, so that Changes in any layer can not effect directly on other layer.

By using Repository Pattern, Our Controller Action Method won’t talk to Context Class Directly.

Now all the database action are done In our Repository.

Now wee will see how to create Generic Repository Pattern In Asp.Net Mvc and Entity Framework.

Create a MVC application.

Add Two Model class to your Model folder.

namespace RepositoryDemo.Models

{

    public class Employee

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public string Email { get; set; }

        public string Age { get; set; }

    }

}

using System.Data.Entity;

namespace RepositoryDemo.Models

{

    public class DemoContext:DbContext

    {

        public DbSet<Employee> employee { get; set; }

    }

}

Create GenericRepository folder in your project.

First Add an Interface to this folder

using System;

using System.Collections.Generic;

namespace RepositoryDemo.GenericRepository

{

    interface IRepository<T> where T:class

    {

        IEnumerable<T> getAll();

        T getById(object Id);

        void Insert(T obj);

        void Update(T obj);

        void Delete(Object Id);

        void Save();

    }

}

I have created generic IRepository interface, which contain method for all the CRUD Operations.
Similarly We will Create Generic Class which will implement IRepository Interface.

using System.Collections.Generic;

using System.Linq;

using RepositoryDemo.Models;

using System.Data.Entity;

using System.Data;

using System;

namespace RepositoryDemo.GenericRepository  

{

    public class Repository<T>:IRepository<T> where T:class

    {

        private DemoContext demoContext;

        private DbSet<T> dbSet;

        public Repository()

        {

            this.demoContext = new DemoContext();

            dbSet = demoContext.Set<T>();

        }

        public IEnumerable<T> getAll()

        {

            return dbSet.ToList();

        }

        public T getById(object Id)

        {

            return dbSet.Find(Id);

        }

        public void Insert(T obj)

        {

            dbSet.Add(obj);

        }

        public void Update(T obj)

        {

            demoContext.Entry(obj).State = EntityState.Modified;

        }

        public void Delete(object Id)

        {

            T getObjById = dbSet.Find(Id);

            dbSet.Remove(getObjById);

        }

        public void Save()

        {

            demoContext.SaveChanges();

        }

        public void Dispose()

        {

            Dispose(true);

            GC.SuppressFinalize(this);

        }

        protected virtual void Dispose(bool disposing)

        {

            if (disposing)

            {

                if (this.demoContext != null)

                {

                    this.demoContext.Dispose();

                    this.demoContext = null;

                }

            }

        }

    }

}

Our Generic Repository Pattern is Created.

Now We can use it in our Controller.
So create a Empty Home Controller to perform CRUD Operation using Repository Pattern over Employee class.

using System.Web.Mvc;

using RepositoryDemo.GenericRepository;

using RepositoryDemo.Models;

namespace RepositoryDemo.Controllers

{

    public class HomeController : Controller

    {

        private IRepository<Employee> _repository = null;

        public HomeController()

        {

            this._repository = new Repository<Employee>();

        }

        public ActionResult Index()

        {

            var employees = _repository.getAll();

            return View(employees);

        }

        [HttpGet]

        public ActionResult Create()

        {

            return View();

        }

        [HttpPost]

        public ActionResult Create(Employee employee)

        {

            if (ModelState.IsValid)

            {

                _repository.Insert(employee);

                _repository.Save();

                return RedirectToAction("Index");

            }

            else

            {

                return View(employee);

            }

        }

        public ActionResult Edit(int Id)

        {

            var employee = _repository.getById(Id);

            return View(employee);

        }

        [HttpPost]

        public ActionResult Edit(Employee employee)

        {

            if (ModelState.IsValid)

            {

                _repository.Update(employee);

                _repository.Save();

                return RedirectToAction("Index");

            }

            else

            {

                return View(employee);

            }

        }

        public ActionResult Details(int Id)

        {

            var employee = _repository.getById(Id);

            return View(employee);

        }

        public ActionResult Delete(int Id)

        {

            var employee = _repository.getById(Id);

            return View(employee);

        }

        [HttpPost, ActionName("Delete")]

        public ActionResult DeleteConfirmed(int Id)

        {

            var employee = _repository.getById(Id);

            _repository.Delete(Id);

            _repository.Save();

            return RedirectToAction("Index");

        }

    }

}

I have declare IRepository as type of Employee.
Index View

Details View

Edit View

Create New record

Delete Record

DOWNLOAD Code to get the View Code

Tuesday, October 13, 2015

Entity Splitting in Entity Framework

I have done a lot of post on Entity Framework Code First which includes some simple tips and tricks to more complex scenarios of mapping relationship and here are some of those post if you want to read them.

Entity Framework Tutorial

However recently I was helping a friend of mine to understand Entity Framework modelling for a legacy system. Since the schema is already defined it becomes a bit difficult to create a rich domain model keeping the model in your design. The other thing I have seen is that people simply follow the schema and let schema dictating what your domain is, which in my opinion is not correct.

Anyways, after some discussion I realized that they are not taking advantage of Entity Splitting in Entity Framework, so I thought about blogging it.

Entity Splitting:- In this scenario there is a single entity or domain object but the data is stored in multiple tables with a one to one relationship. For example lets say the domain is a fitness/health industry and you have customer table which stores basic information like first name, last name, DOB in the customer table and other vital stats like resting heat beat,blood type,cholesterol level, blood pressure, Sugar level etc. in their health information table. However in your domain model the customer object is composed of both information.

Lets see this in action.

Customer.cs

public class Customer
{
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public DateTime DOB { get; set; }

    public int RestingHeartBeat { get; set; }

    public string BloodType { get; set; }

    public decimal Cholesterol { get; set; }

    public string BloodPressure { get; set; }
}

So this is my domain object which represents the whole customer object however in the legacy system some information is stored in the Customer table and some in the VitalStats table.

And lets write the configuration for customer in which we will split the entity into two different tables using the Map and ToTable function as shown below.

CustomerConfiguration.cs

public class CustomerConfiguration : EntityTypeConfiguration<Customer>
{
    public CustomerConfiguration()
    {
        this.Map(c =>
       {
           c.Properties(p => new
           {
               p.Id,
               p.FirstName,
               p.LastName,
               p.DOB
           });
           c.ToTable("Customer");
       })
        .Map(v =>
       {
           v.Properties(p => new
           {
               p.Id,
               p.BloodPressure,
               p.BloodType,
               p.Cholesterol,
               p.RestingHeartBeat
           });
           v.ToTable("VitalStats");
       });
    }
}

And here is my DbContext class.

public class HealthContext : DbContext
{
    DbSet<Customer> Customer { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

MySQL .NET Entity Framework Code-First Migration

I was recently downloading and trying to run a MySQL docker image which worked just fine on top of a Hyper-V based Ubuntu 14.04 LTS. Don’t worry, this is still Entity Framework Code-First migration article with Visual Studio 2015 and MySQL 5.6.27 (latest as of today).

I setup a Docker MySQL Image on my Ubuntu based Docker host. I was thinking what could be a best way to test this instance out with .NET than testing it with our good friend Entity Framework Code-First migration. This is really going to hit the MySQL instance hard by creating DB on its own and creating all the Model Tables on its own with little or no involvement of MYSQL DDL /DMLs.

Prerequisites : –

To start using Entity Framework 6 and Visual Studio 2015 is necessary to install

MySQL Connector/Net 6.9.7 (GA) (It may work with earlier releases, but this is the one I used for my POC)
MySQL for Visual Studio 1.2.4 GA (Starting with version 6.7, Connector/Net will no longer include the MySQL for Visual Studio integration. That functionality is now available in a separate product called MySQL for Visual Studio available using the MySQL Installer for Windows)

Action Begins

Create a new Console Application. Select Framework as 4.5 or 4.0. This is because MySQL.Data.Entity Nuget package is supported only on these frameworks, though EntityFramework 6 is supported on .net 2.0, .net 4.0 and .net 4.5.
Open Nuget Package Manager Console by navigating through Toos –> Nuget Package Manager –> Package Manager Console… and run the below commands to install the Nuget packages needed for this project. Make sure that you select correct Project in Package Manager console if you are having multiple Projects in same Solution.
1. Install-package EntityFramework -Version 6.0.0
2. Install-package MySql.Data # as of today it installs 6.9.7
3. Install-package MySql.Data.Entity # as of today it installs 6.9.7 version
4. Install-package MySql.Data.Entities # as of today it installs 6.8.3 version (required for MYSQLGenerator in Step #9 below)
Add MySQL ConnectionString to App.config / Web.config. Ensure the providerName is exactly same as provider defined in entityFramework-> providers section

</connectionStrings>

You can remove any defaultConnectionFactory configs from entityFramework section.
Add a DBContext class

class myDBContext : DbContext {

public myDBContext() : base(“mysqlconn”)

{

}

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

base.OnModelCreating(modelBuilder);

}

public DbSet<Person> People { get; set; }

}

Add a Person class in Model folder

class Person

{

public int Id { get; set; }

public string Name { get; set; }

public int Age { get; set; }

}

Compile the Project. this is most important as all further steps depend on ‘Reflection’ libs of the Project.
Enable the Migrations from Nuget Package Manager console

PM> enable-migrations

Checking if the context targets an existing database…

Code First Migrations enabled for project mysqltester.

PM>

Set SQL Generator in the Migration Configurations class.In the Migrations/Configuration.cs – Constructor. Add below line to set SQLGenerator attribute.

SetSqlGenerator(“MySql.Data.MySqlClient”, new MySql.Data.Entity.MySqlMigrationSqlGenerator());

Create initial migration, which means the SQL Generation works for the Code-First entity Migration defined above.

PM> add-migration initial

Scaffolding migration ‘initial’.

The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running ‘Add-Migration initial’ again.

PM>

Check if update-database works , which means the EF was able to use the DBContext defined and connection from app / web.config.

PM> update-database

Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.

Applying explicit migrations: [201510041122429_initial].

Applying explicit migration: 201510041122429_initial.

Running Seed method.

PM>

Write a Sample Program to test/check the DBContext and data..

class Program

{

static void Main(string[] args)

{

myDBContext dbc = new myDBContext();

foreach (var item in dbc.People)

{

Console.WriteLine(“Name {0}, Age {1}”, item.Name, item.Age);

}

string ans = “n”;

{

Person newperson = new Person();

string temp=””;

int age = 0;

{

Console.WriteLine(“Enter Age”);

temp = Console.ReadLine();

} while (! (int.TryParse(temp, out age) && age > 0));

temp = null;

newperson.Age = age;

Console.WriteLine(“Enter Name “);

newperson.Name = Console.ReadLine();

dbc.People.Add(newperson);

Console.Write(“More People ?”);

ans = Console.ReadLine();

} while (ans.Equals(“y”, StringComparison.CurrentCultureIgnoreCase));

dbc.SaveChanges();

}

All the source code for this article can be found at GitHub repository here.

C# Tuple: a multiple component Collection

The general definition of Tuple defines it is an ordered set of data constituting a record.
It is used as data structure or collection to hold data. The good thing is that each value part could be different type. It could be a good substitute for 2 dimensional arrays structure (the reason why I started using it).

I came across to Tuple, when I wanted a collection with pair of values. For example, a similar structure like Dictionary but something like CustomCollection

Before .Net 4.0, the obvious solution would had been to define custom type and use it with a list or array to build the collection.

It is not difficult to create such utility class but when you work on big application and tons of classes are floating around, the initial opinion is to avoid having another custom type and use System classes as much as possible. Coming from the same thought, I ended up using Tuple to build my logic.
Tuple is a System class which provide ways to create a structure to hold almost unlimited values and any type of values.

Each component in Tuple is referred by its sequence number for e.g. Item1, Item2, Item3, Items4 ….
// Tuple structure

http://msdn.microsoft.com/en-us/library/system.tuple.aspx

public static class Tuple

    {       

      public static Tuple&lt;T1&gt; Create&lt;T1&gt;(T1 item1);        

       public static Tuple&lt;T1, T2&gt; Create&lt;T1, T2&gt;(T1 item1, T2 item2);

       public static Tuple&lt;T1, T2, T3&gt; Create&lt;T1, T2, T3&gt;(T1 item1, T2 item2, T3 item3);

       public static Tuple&lt;T1, T2, T3, T4&gt; Create&lt;T1, T2, T3, T4&gt;(T1 item1, T2 item2, T3 item3, T4 item4);

       public static Tuple&lt;T1, T2, T3, T4, T5&gt; Create&lt;T1, T2, T3, T4, T5&gt;(T1 item1, T2 item2, T3 item3, T4 item4, T5 item5);

       public static Tuple&lt;T1, T2, T3, T4, T5, T6&gt; Create&lt;T1, T2, T3, T4, T5, T6&gt;(T1 item1, T2 item2, T3 item3, T4 item4, T5 item5, T6 item6);

       public static Tuple&lt;T1, T2, T3, T4, T5, T6, T7&gt; Create&lt;T1, T2, T3, T4, T5, T6, T7&gt;(T1 item1, T2 item2, T3 item3, T4 item4, T5 item5, T6 item6, T7 item7);

       public static Tuple&lt;T1, T2, T3, T4, T5, T6, T7, Tuple&lt;T8&gt;&gt; Create&lt;T1, T2, T3, T4, T5, T6, T7, T8&gt;(T1 item1, T2 item2, T3 item3, T4 item4, T5 item5, T6 item6, T7 item7, T8 item8);

    }

Note the names given to each constructor or to the size of tuple.
For the above definition, you can make out that there are maximum of 8 components that could defined with Tuple. However, Microsoft refers the 8^th component as “Rest” property, which allows nesting further tuples to it. Hence, in a way unlimited tuples could be defined by nesting the each level in Rest property or last component.

The MSDN link to Tuple Class (http://msdn.microsoft.com/en-us/library/system.tuple.aspx ) give few example but limited to only 7 components.

I have a written a small example to give complete picture showing one of the possible reason of using it.

The idea is to give $200 to employees working in non-metro region. There could be many ways to get this problem solved but for sake of simplicity this is best I thought of.

User Interface
Image 1: Shows a Give Misc Payments button and a blank grid view. All the employees in non metro will be given $200 and others will have zero.

When Give Misc Payment button is pressed.

Source code

using System;

using System.Collections.Generic;

using System.Windows.Forms;

namespace Windows.POC

{

  public partial class TupleTest : Form

  {

    public TupleTest()

    {

      InitializeComponent();

      dataGridView1.DataSource = new List<Employee>();

    }

    ///

<summary>

    /// Fills the data grid view with miscelleneous payments

    /// </summary>

    private void btnMiscPayments_Click(object sender, EventArgs e)

    {

      List<Tuple<string, Employee>> listEmployeeLocation = GetEmployeeWithLocation();

      List<Employee> lstEmployees = new List<Employee>();

      // The idea is to give a $200 to people working in non-metro regions as they have to spend extra time commuting to these places.

      foreach(var t in listEmployeeLocation)

      {

        if(t.Item1 == "Non-Metro")

        {

          t.Item2.MiscPayments = 200;

        }

        else

        {

          t.Item2.MiscPayments = 0;          

        }

        lstEmployees.Add(t.Item2);

      }

      dataGridView1.DataSource = lstEmployees;

    }

    ///

<summary>

    /// Returns the populated List of employees with their location

    /// </summary>

    private List<Tuple<string, Employee>> GetEmployeeWithLocation()

    {

      List<Tuple<string, Employee>> listEmployeeLocation = new List<Tuple<string, Employee>>();

      // Shorthand for creating and initializing the object with data

      listEmployeeLocation.Add(Tuple.Create("Non-Metro", new Employee { EmpID = 1, FirstName = "Sam", LastName = "Wood", Department = "HR" }));

      listEmployeeLocation.Add(Tuple.Create("Metro", new Employee { EmpID = 2, FirstName = "Mark", LastName = "Will", Department = "Accounts" }));

      listEmployeeLocation.Add(Tuple.Create("Non-Metro", new Employee { EmpID = 3, FirstName = "Julie", LastName = "Diego", Department = "HR" }));

      listEmployeeLocation.Add(Tuple.Create("Metro", new Employee { EmpID =4, FirstName = "Kim", LastName = "Mills", Department = "Finance" }));

      listEmployeeLocation.Add(Tuple.Create("Metro", new Employee { EmpID = 5, FirstName = "Mahir", LastName = "Khan", Department = "IT" }));

      listEmployeeLocation.Add(Tuple.Create("Non-Metro", new Employee { EmpID = 6, FirstName = "Jade", LastName = "Todd", Department = "IT" }));

      return listEmployeeLocation;

    }

  }

  ///

<summary>

  /// Employee entity class

  /// </summary>

  public class Employee

  {

    public Employee()

    { }

    public int EmpID { get; set; }

    public string FirstName { get; set;}

    public string LastName { get; set; }

    public string Department { get; set; }

    public Single MiscPayments { get; set; }

  }

}

The example below uses the 8^th components and suggest one of the way of using it.
The 8^th or TRest came out as bit an exercise. It was not as straight as I thought it would be. Moreover, the syntax itself turn out be bit lengthy than usual (I am not sure but feel there must be better or clever ways to write it) however, this is what I learnt and presenting you here.
So continuing to same example above, I added 8^th item to the EmployeeList. The 8^th item is a nested tuple.

//List with 8th item or nested tuple.

List<Tuple<string, int, string, string, string, string, string, Tuple<string, string>>> listEmployeeTuple = new List<Tuple<string, int, string, string, string, string, string, Tuple<string,string>>>();

Though, I keep saying 8^th element but total count of elements in my Tuple is nine, it could also be referred at nintuples similar to Microsoft terminology as specified above.

// This one worked -- note the how non-static Tuple class is used

Tuple<string, int, string, string, string, string, string, Tuple<string, string>> firstEmployee = new Tuple<string, int, string, string, string, string, string, Tuple<string, string>>("Non-Metro", 1, "Sam", "Wood", "HR", "8867", "5543789044", new Tuple<string, string>("People Management", "Administration"));

      listEmployeeTuple.Add(firstEmployee);

//Another working example with nested tuple created using Static class

Tuple<string, int, string, string, string, string, string, Tuple<string, string>> secondEmployee = new Tuple<string, int, string, string, string, string, string, Tuple<string, string>>("Non-Metro", 1, "Sam", "Wood", "HR", "8867", "5543789044", Tuple.Create("People Management", "Administration"));

      listEmployeeTuple.Add(secondEmployee);

While working on this blog I learnt that there two separate classes defined for Tuples. One is the static class which is ideally used when creating straight 8 non nested octuples.
Other one, a non static, is used when creating more than 8 items or nested tuples similar to what I demonstrated above.

Static Tuple Class Definition

Simple or non static Tuple Class Definition

Accessing nested tuples
Accessing nested tuples is an easy part. The Rest property is simply used to get the last element.

Tuple<string, int, string, string, string, string, string, Tuple<string, string>> firstEmployee = new Tuple<string, int, string, string, string, string, string, Tuple<string, string>>("Non-Metro", 1, "Sam", "Wood", "HR", "8867", "5543789044", new Tuple<string, string>("People Management", "Administration"));

string primarySkill = firstEmployee.Rest.Item1;

string seondarySkill = firstEmployee.Rest.Item2;

Further nested tuples are accessed by appending Rest to the Rest, in accordance to the number of nests.

For example, 2 nested levels, it will be like
firstEmployee.Rest.Rest.Item1;
I found Tuple a very interesting concept and it helped me quickly write some business logic which would have otherwise lead me to write custom classes.
I don’t claim that all above examples or code or syntax is best way to write code but best to my knowledge and with best intention to share knowledge.
I am sure many coders must have known better or alternate ways to write similar logic. I would be glad to learn all those ways.
Have fun with Tuple.

Queries

DotNetSlackers: ASP.NET News for lazy Developers