Entity Framework, Its Approaches, And How It Maps The Conceptual Schema With Storage Schema

Entity Framework

It is a tool to access the database. More accurately, it’s classified as an Object/Relational Mapper (ORM) which means it maps the data in a relational database into objects of our applications.

Entity Framework

Before .NET Framework 3.5, developers used the ADO.NET Framework to open the connection, fetch the data from the database, insert the data to the database, create the database connection, make the DataSet containers, convert the data into the database, and then remember it to close the database connection as well. It was really a huge headache for the developers to write a large amount of code. Microsoft introduced Entity Framework (ORM) to make the code less cumbersome so you can do more. Now, we have  predefined functions which insert our data, read our data and do everything you want to play with data.

Entity Framework provides the class DbContext which is actually the gateway or bridge which connects our model classes to the database. DbContext represents 1 or more DbSets which represents the table.

Entity Framework

We use LINQ to query, these DbSets and EFs will translate our LINQ queries to SQL queries at runtime. It opens the connection to the database, reads the data, maps it to the object, and adds them to DbSet in the DbContext.

Entity Framework

Why it is possible that we can apply LINQ to DbSet objects?

We can query our objects with LINQ. We know that DbSet properties are mapped to the Database in DbContext class but it is an object and we can apply LINQ to in-memory objects, database objects, and XML objects.

As we add/modify/remove the object in this DbSet, EF keeps track of these changes, and then when we ask it to persist the changes, again, it will automatically generate the SQL statements and executes them on the database.

Entity Framework

Don’t be confused about the above description because when we’ll use Entity Framework, these things will automatically be clear to you.

There are two ways or two workflows to use EF.

  • DB First
  • Code First

We’ll cover both the approaches in our ASP.NET MVC 5 Series.

Database First

This is the traditional approach in most of the software houses because most of the things they are doing are database dependent. Let me explain.

Actually, the database isn’t just all about storing the data, creating tables, and filtering the results. The topic the of the database is so vast these days, we can define stored procedures, functions, views, and we can define the transactions to make the most of the application logic at database side. The benefit of this approach is we make most of the application logic at the database side, so we need to just implement it in the application side. But if we write these logics in the application, then surely we need to write hundreds of lines of code.

So, it is important that you should know Database First, it is quite easy. We can also apply LINQ in Database First. Here, first of all, we design the database in our SQL Server, MySQL, and Oracle as whatever we want to use for our application. And then, we import the database into our application.
Entity Framework

Code First

In Code First approach, we make the model classes in our application first. And here we can apply the validations on the properties as well. Entity Framework automatically maps our model classes and creates the tables for us according to our model class. The properties of model classes define the fields of the table. If we try to change our model classes to change the database, then we need to handle these changes through Migrations.

If you see most of the tutorials, then you’ll get to the point that most of the instructors are going with this approach because we can do anything with this approach.

But honestly speaking, don’t just learn code first -- you should also know how to implement a database first approach for job perspective, because the database first approach makes your database development and designing better as well.

Entity Framework

But if you see from another perspective, most of the code we’ve already done here our model classes are ready, so this approach makes it easy for us to implement the testing.

First of all, we’ll implement Code First Approach to learn some programming concepts, entity framework and how it works under the hood, how we can query the database objects and then we’ll move to the database first approach.

Let’s understand the code first mapping scenario.

Entity Data Model

Entity Framework, first of all, creates the Entity Data Model in the memory where it maps our properties and generates the table fields according to it.

Conceptual model basically is our entity domain class where we define the different properties with different datatypes. Entity Framework builds the conceptual model with the help of domain classes, context class, default convention and maps the properties to the storage model.

Storage Model is our database where Entity Framework finally maps the table according to our classes.

Now, you might be thinking what the default convention is. Let’s suppose we have ‘Student’ entity class.

  1. public class Student  
  2. {  
  3.           public int Id { get; set; }  
  4.           public string Name { get; set; }  
  5.           public string Address { get; set; }  
  6. }  

Now, the default convention here is, Entity Framework will create the Table in the database with ‘Student’ name. We already know that there are a few conventions to make the primary key in the table:

  • ‘Id’ property
  • <class name > + ‘Id’ i.e. (StudentId)

If we use these property names then Entity framework will make this property as the primary key of the table and we can see there is another hint for the entity framework, that it is ‘int’ type. And in databases, the primary key is also of numeric type. We already know that ‘int’ type can’t be null and on the opposite side ‘primary key’ can’t also be null so Entity Framework maps the things in this way, it is very easy for EF to follow the default conventions and make the things.

Now, ‘string’ is nullable type, which means it can be null. So, EF makes the field names ‘Name’ and ‘Address’ nullable here in the table.

By default, the schema of the table is ‘dbo’.

Let me tell you the C# datatypes mapping with SQL Server data types.

C# Data TypesSQL Server Data Types
decimaldecimal(18, 2)
sbyteNo Mapping(It throws Exception)
charNo Mapping
objectNo Mapping

This is the brief summary of default conventions in EF.

But the above table is just describing the types of fields of table w.r.t the property datatypes of classes. But we want to make the fields required, we want to limit the field input text size then we need to override the default conventions.

But we can also override the default convention of EF. And there are 2 ways.

  • Data Annotations
  • Fluent APIs

Data Annotations are easy to use but here we have some kind of limitations. But in Fluent APIs, we can make anything. We’ll discuss them separately later on.

Now, let me show you the complete mapping of how EF maps our entity and context classes with the storage models.

Entity Framework

Image credit goes to:


One last thing, you might be thinking about the property which itself has an entity datatype.

  • ‘Student’ Grade has the ‘Grade’ entity class data type

Actually, this is the navigational property. The purpose of the navigational property is we make the relationship between 2 entities like here student and Grade has a 1 to 1 relationship which means that 1 student must have 1 grade.

And if you need to make the one to many relationship, then you’ll make the property datatype as ICollection<>

  1. public class Student  
  2. {  
  3.          ….  
  4.           ….  
  5.    public virtual ICollection<Grade> Grades { get; set; }  
  6. }  

According to the ASP.NET MVC Documentation, it is the best practice to make the navigational properties as virtual and if it is showing (many) parts then it always uses ICollection.