What is O/RM?
Object/Relational Mapping (O/RM) is a mechanism to store and/or retrieve data from domain objects without considering how they are related with their data sources, which makes the application maintainable and extendable. Object/Relational Mapping (O/RM) encapsulates the code which needed to manipulate the data that allows programmer to interact with data in same language, so programmer no longer has to use SQL. ORM manages the mapping between set of domain objects and relational database objects. ORM automates standard CRUD operation (Create, Read, Update & Delete) so that the programmer doesn't need to write it manually.
What is Entity Framework?
Entity framework is an Object/Relational Mapping (O/RM) framework, an enhancement to ADO.NET which provides a mechanism for accessing & storing the data in the database. It can be used in ASP.NET applications for data related operations.
Why Micro ORM?
Most of the ORM tools like NHibernate or Entity Framework has many features those are implemented in years as required, but in a real world, developers are using a small subset of them and complexity of unused features may downgrade the performance of an application.
Micro ORM only provides few set of features like read data from the database and map them to domain objects. Micro ORM requires very fewer configurations and are easy to use compared to full ORM.
E.g. Micro ORM dapper provides extension methods to DbConnection class and adding a reference to dapper will allow accessing database using those extension methods and if consider the performance it provides a result with time near to ADO.NET.
Why ORM?
ORM supports relationship for domain objects means related objects can be loaded automatically if required. To achieve this with Micro ORM query needs to be formed accordingly which will add complexity in code and query.
Entity framework provides designer to create domain classes and relationship between those classes while with Micro ORM classes and relationship needs to be constructed manually.
Entity framework provides caching for returning cached result set if a similar request has been made recently.
What to choose?
Micro ORM
- If having a requirement to produce the best performance, prefer dapper over Entity Framework.
- If having a requirement to migrate legacy code of ADO.NET, will help to improve code with minimum changes especially with dapper which provides extension methods to DbConnection.
Entity Framework
- If want to use relationships for domain classes, to load related objects Entity Framework is preferred.
- If want to use OData which works over IQueryable and to make it more effective Entity Framework can be used to gain performance.
Integrate Dapper micro ORM with ASP.NET MVC
- Create Student and Address classes
public class Student
{
public Student()
{
this.Address = new List<Address>();
}
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public List<Address> Address { get; set; }
}
public class Address
{
public int Id { get; set; }
public string AddressType { get; set; }
public string StreetAddress { get; set; }
public string City { get; set; }
public string State { get; set; }
public string ZipCode { get; set; }
public int StudentId { get; set; }
}
Now Create IStudentRepository.cs interface and StudentRepository.cs classes for data access.
public interface IStudentRepository
{
List<Student> GetAll();
Student Get(int id);
Student Insert(Student student);
Student Update(Student student);
bool Remove(int id);
Student GetDetail(int id);
}
public class StudentRepository : IStudentRepository
{
private IDbConnection _db;
public StudentRepository()
{
this._db = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
}
public List<Student> GetAll()
{
return this._db.Query<Student>("SELECT S.Id, S.FirstName, S.LastName, S.Email FROM dbo.Student S").ToList();
}
public Student Get(int id)
{
return this._db.Query<Student>("SELECT S.Id, S.FirstName, S.LastName, S.Email FROM dbo.Student S WHERE S.Id = @Id", new { Id = id }).SingleOrDefault();
}
public Student Insert(Student student)
{
var query = "INSERT INTO Student (FirstName, LastName, Email) VALUES(@FirstName, @LastName, @Email); " + "SELECT CAST(SCOPE_IDENTITY() as int)";
student.Id = this._db.Query<int>(query, student).Single();
return student;
}
public Student Update(Student student)
{
var query = "UPDATE Student SET FirstName = @FirstName, LastName = @LastName, Email = @Email WHERE Id = @Id";
this._db.Execute(query, student);
return student;
}
public bool Remove(int id)
{
var affectedRecords = this._db.Execute("DELETE FROM Student WHERE Id = @Id", new { Id = id });
return affectedRecords == 1;
}
public Student GetDetail(int id)
{
using (var results = this._db.QueryMultiple("dbo.GetStudent", new { Id = id }, commandType: CommandType.StoredProcedure))
{
var student = results.Read<Student>().SingleOrDefault();
var addresses = results.Read<Address>();
if (student != null && addresses != null)
{
student.Address.AddRange(addresses);
}
return student;
}
}
Use above repository in ASP.NET MVC StudentController and create an instance for StudentRepository
public class StudentController : Controller
{
private IStudentRepository _studentRepository;
public StudentController()
{
this._studentRepository = new StudentRepository();
}
// GET: /Student/Detail/1
public ActionResult Detail(int id)
{
return this.View(this._studentRepository.GetDetail(id));
}
}
Similarly insert, update and delete can be implemented in ASP.NET MVC using student repository of dapper micro ORM data access layer.