This is the fourth part of building line of business application using MVC 5, you can read the previous parts through the following links
- Building a LOB application with MVC 5 – Part 0
- Building a LOB application with MVC 5 – Part 1
- Building a LOB application with MVC 5 – Part 2 – Models and Generic Repository
In the previous part, we created all the model objects, a Generic repository interface and a generic service layer interface with its implementation.
In this part, we will build the generic repository implementation using EntityFramework 6, and before we proceed, I will introduce EntityFramework first.
What is EntityFramework?
EntityFramework is Microsoft’s Object Relational Mapper (ORM) framework, it is used to map objects to database tables and vice versa, it uses mapping file or metadata to generate a dynamic SQL at run time.
This is a different model than the one we used to do, which basically depend on Stored Procedures and code generation tools to save time, there is a debate regarding performance of dynamic SQL against compiled stored procedures, but in my point of view, this is not a major concern now and you can utilize caching to overcome the difference, besides, with EntityFramework you can still utilize Stored Procedures for important operations that may need special performance needs.
EntityFramework Usage Models
EntityFramework can be used in 3 ways
- Database First: Start with the database, and use it to generate code, this model should not be used for new projects, it is not flexible as you can’t edit the generated code directly but through a partial classes and all the mapping exist in a single file which make it hard to share between team members if more than one wants to apply changes at the same time.
- Model First: Start with a model and design the entities, attributes and its relationships, use this model to generate both the classes and database, once this happened for the first time, it will be the same as Database First
- Code First: Start with a pure classes and then use something called Migration to generate the database, this model is the recommended way to go and EF 7 has no database first model, it is all about Code First now, with Code First, you can have snapshots of your code and database, and you can rollback at any point to any previous version.
Build the Generic Repository
To start generating our generic Repository implementation, we will add the EntityFramework package to the AppointmentManager.Repository.EntityFramework project as this will be our repository project.
In order to do that, we will use NuGet which is a package manager for .Net, we use it to install, uninstall and update packages instead of referencing DLL files directly from a shared folder, the package can be a simple DLL or code file, JavaScript, special configuration or entries in web.config
To install the package, Click Tools -> NuGet Package Manager -> Package Manager Console
Make sure “AppointmentManager.Repository.EntityFramework” is selected in the default project drop down list, then type the following command and press enter
install-package EntityFramework
In the project “AppointmentManager.Repository.EntityFramework”, do the following:
- Add Reference to “AppointmentManager.Repository” and “AppointmentManager.Models”
- Add a new class named EFRepository
public class EFRepository<T, K> : IRepository<T, K> where T : class
{
private DbContext _context;
private DbSet<T> _entitySet;
public EFRepository(DbContext context)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
this._context = context;
_entitySet = _context.Set<T>();
}
public T Add(T item)
{
_entitySet.Add(item);
_context.SaveChanges();
return item;
}
public bool Delete(T item)
{
_entitySet.Attach(item);
_entitySet.Remove(item);
_context.SaveChanges();
return true;
}
public bool DeleteByID(K id)
{
var item = _entitySet.Find(id);
_entitySet.Remove(item);
_context.SaveChanges();
return true;
}
public T GetByID(K id)
{
return _entitySet.Find(id);
}
public IQueryable<T> GetAll()
{
return _entitySet;
}
public IQueryable<T> GetAll(Expression<Func<T, K>> orderBy)
{
return _entitySet.OrderBy(orderBy);
}
public IQueryable<T> GetAll(int pageIndex, int pageSize)
{
return _entitySet.Skip((pageIndex - 1) * pageSize).Take(pageSize);
}
public IQueryable<T> GetAll(int pageIndex, int pageSize, Expression<Func<T, K>> orderBy)
{
return _entitySet.Skip((pageIndex - 1) * pageSize).Take(pageSize).OrderBy(orderBy);
}
public IQueryable<T> Find(Expression<Func<T, bool>> predicate)
{
return _entitySet.Where(predicate);
}
public IQueryable<T> Find(Expression<Func<T, bool>> predicate, int pageIndex, int pageSize, Expression<Func<T, K>> orderBy)
{
return _entitySet.Where(predicate).Skip((pageIndex - 1) * pageSize).Take(pageSize).OrderBy(orderBy);
}
public bool Update(T item)
{
_entitySet.Attach(item);
_context.SaveChanges();
return true;
}
}
Build the project and make sure it compiles successfully
Add a new class called AppointmentManagerContext, this class is needed only for the migration to work, we will not be using it as the EFRepository class is using the base DbContext class, you can still change it and add properties of type DbSet<T> for each entity in the application, this will be helpful specially if you want to do some joins between different entities.
public class AppointmentManagerContext : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<User>()
.Map<Customer>(m =>
{
m.Requires("UserType").HasValue((int)UserRole.Customer);
m.ToTable("Users");
}
).Map<ServiceProvider>(m =>
{
m.Requires("UserType").HasValue((int)UserRole.ServiceProvider);
m.ToTable("Users");
}
)
.ToTable("Users");
modelBuilder.Entity<ServiceProviderReview>().HasRequired<Appointment>(a => a.Appointment);
base.OnModelCreating(modelBuilder);
}
}
After that, we need to generate our database using Code First Migration, open the Package manager console and type the following command, make sure the project AppointmentManager.Repository.EntityFramework is selected
Enable-Migrations
You may encounter an issue regarding the TimeSlot class that it has no key defined, just add an integer property called ID to this class and rerun the command again
Then run the following command to add the first migration which will compare the model classes to the database and add code to generate the database, later when we add new classes or properties, we will run the command again which will add another migration that will generate code to add the new properties to the database.
Add-Migration -Name Initial
After running the command, you will notice that a new class named Initial_[time].cs, where [time] is the date time for the time the file was generated
Before we proceed, I would like to explain the content for the method OnModelCreating:
Since we have all classes inheriting from the BusinessEntity class, and we have the Customer and ServiceProvider classes inherit from the User class, we must tell EntityFramework how it will generate tables for these classes.
By default, EntityFramework will generate a new class for each entity with its properties including these properties of the parent class which is fine in the BusinessEntity class as we want all common properties like ID and CreationDate to exist in all tables.
But for the Customer and ServiceProvider classes, we need a table per hierarchy, both classes must be mapped to a single table called Users, to do that, we added the code at line 5 where we tell EF to map the User class to another 2 sub-classes and for each one we told it to use the same table name “Users”, this way EF will combine the properties of the 3 classes and add it to one table.
There are another 2 types of inheritance, table per type (TPT), in our case, it would have been 3 tables (User, ServiceProvider and Customer), The third option is table per class (TPC) which will generate only 2 tables (ServiceProvider and Cutomer) and each table will have the properties in the parent User class.
So far, we still don’t have our database created, and to do that, we will have to run this command in the package manager console
Update-Database
After you do, you will have the following error
Introducing FOREIGN KEY constraint 'FK_dbo.Appointments_dbo.Users_ServiceProviderID' on table 'Appointments' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.
This is the time we will have to change the generated migration Initial file manually to override the cascade action.
Open the intiial file, in line 70 and line 148, change cacadeDelete to false and run the update-database command again
Now we have our backend ready, and the database is created, but we didn’t specify any connection strings, so where has been the database created? by default there is a connection string named DefaultConnection and it reference the localdb database, so open management studio and connect to server (localdb)\mssqllocaldb
you should find the database created with the name of the context class, if you want to override that, you can add a connection string named Defaultconnection in the project configuration file with the database name and server you want and then run the update-database command again.
Lets make sure now our backend is working fine, create a new unit test project and name it “AppointmentManager.Repository.EntityFramework.Test”
Add reference to the following projects:
- AppointmentManager.Repository.EntityFramework
- AppointmentManager.Repository
- AppointmentManager.Models
Rename the default file “UnitTest1” to GenericRepositoryTest
Rename the default method to TestInsertion and put the following code
[TestClass]
public class GenericRepositoryTest
{
[TestMethod]
public void TestInsertion()
{
var context = new AppointmentManagerContext();
IRepository<ServiceType, int> serviceRepository = new EFRepository<ServiceType, int>(context);
var serviceType = new ServiceType()
{
CreatedBy = "admin",
CreationDate = DateTime.Now,
LastModificationDate = DateTime.Now,
Name = "Test service"
};
serviceType = serviceRepository.Add(serviceType);
Assert.IsNotNull(serviceType);
Assert.AreNotEqual(serviceType.ID, 0);
serviceType = serviceRepository.GetByID(serviceType.ID);
Assert.IsNotNull(serviceType);
serviceRepository.Delete(serviceType);
serviceType = serviceRepository.GetByID(serviceType.ID);
Assert.IsNull(serviceType);
}
}
Right click the method in VS and choose Run Tets and make sure the test succeeds, you can double check by putting a break point before the line that deletes the serviceType object and check the database and ensure the record is there
By this, we have a read repository that we can use to manage our data, in the next part we will start building the UI.
Haitham Shaddad Blog, Design and Build Web Applications with Azure and .Net 