Data Access Approaches Of Entity Framework

Introduction

The intent of this article is to explain the three data access approaches that Microsoft’s Entity Framework provides. There are several good articles on the internet on this topic but I would like to cover this topic in a more detailed way and in the form of a tutorial that would be a primer for someone who is starting to learn Entity Framework and its approaches. We’ll go step by step to explore each approach via which we can access the database and data using EF in our application. I’ll use Entity Framework version 6.2, .NET 4.6, and Visual Studio 2017 for the tutorial. For the database, we would be using SQL Server. You can make use of a local DB if you do not have SQL Server installed. I’ll explain the database first and model first approaches in this article; while the code first approach and code first migrations will be used in the following article.

Series Info

We'll follow a five-article series to learn the topic of Entity Framework in detail. All the articles will be in the tutorial form except the last one where I'll cover the theory, history, use of Entity Framework. Following are the topics of the series.

Learning Entity Framework (Day 1): Data Access Approaches of Entity Framework in .NET

Learning Entity Framework (Day 2): Code First Migrations in Entity Framework in .NET

Learning Entity Framework (Day 3): Code First Migrations in ASP.NET WebAPI 2.0 in .NET

Learning Entity Framework (Day 4): Code First Approach using Entity Framework Core in .NET
Learning Entity Framework (Day 5): Entity Framework (The Theory)

Entity Framework

Microsoft Entity Framework is an ORM (Object-relational mapping). The definition from Wikipedia is very straightforward for ORM and pretty much self-explanatory.

“Object-relational mapping (ORM, O/RM, and O/R mapping tool) in computer science is a programming technique for converting data between incompatible type systems using object-oriented programming languages. This creates, in effect, a "virtual object database" that can be used from within the programming language. ”

Being an ORM, Entity Framework is a data access framework provided by Microsoft that helps to establish a relation between objects and data structure in the application. It is built over traditional ADO.NET and acts as a wrapper over ADO.NET and is an enhancement over ADO.NET that provides the data access in a more automated way, thereby, reducing a developer’s effort to struggle with connections, data readers, or datasets. It is an abstraction over all those and is more powerful with the offerings it makes. A developer can have more control over what data he needs, in which form and how much. A developer having no database development background can leverage Entity framework along with LINQ capabilities to write an optimized query to perform DB operations. The SQL or DB query execution would be handled by Entity Framework in the background and it will take care of all the transactions and concurrency issues that may occur.

Entity Framework Approaches

It is very common to know the three approaches that Microsoft Entity Framework provides. The three approaches are as follows,

Model First,
Database First, and
Code First
- Generate database from the data model classes.
- Generate data model classes from an existing database.

The Model-First approach says that we have a model with all kinds of entities and relations/associations using which we can generate a database that will eventually have entities and properties converted into database tables and the columns and associations and relations would be converted into foreign keys respectively.

The Database-First approach says that we already have an existing database and we need to access that database in our application. We can create an entity data model along with its relationship directly from the database with just a few clicks and start accessing the database from our code. All the entities, i.e., classes, would be generated by EF that could be used in the application's data access layer to participate in DB operation queries.

The Code-First approach is the recommended approach with EF, especially when you are starting the development of an application from scratch. You can define the POCO classes in advance and their relationships and envision how your database structure and data model may look like by just defining the structure in the code. Entity Framework, at last, will take all the responsibility to generate a database for you for your POCO classes and for the data model and will take care of transactions, history, and migrations.

With all three approaches, you have full control over updating the database and code as per the need at any point in time.

Model First

Using a Model-First approach, a developer may not need to write any code for generating a database. Entity Framework provides the designer tools that could help you make a model and then generate a database out of it. The tools are more of a drag and drop controls that just need inputs like what your entity name is, what properties it should have, how it is related to other entities and so. The user interface is very easy to use and interesting.

Model designer, when good to go, will help you to generate DDL commands that could be directly executed from Visual Studio or on your database server to create a database out of your created model. This creates an EDMX file that stores the information of your conceptual model, storage model, and mapping between both. The only drawback that I can see is that dropping the database completely and recreating it would be a challenge with this approach.

Database First

We use the Database-First approach when we already have an existing database and need to access that in our application. Establishing the data access methodology for existing database with Entity Framework will help us to generate the context and classes in our solution through which we can access the database. It is the opposite of a Model-First approach. Here, a model is created via a database and we have full control to choose what tables to include in the model, and what stored procedures, functions, or views to include. Your application may be a sub-application that does not need all the tables or objects of your big database, so you can have liberty here to control what you want in your application and what not. Whenever the database schema changes, you can easily update the entity data model by just one click in the designer or entity data model and that will take care of mapping and create necessary classes in your application.

Code First

Using the Code-First approach, a developer’s focus is only on the code and not on the database or data model. The developer can define classes and their mapping in the code itself and since now Entity Framework supports inheritance, it is easier to define relationships. EF takes care of creating or re-creating the database for you and not only this; while creating a database, you can provide seed data, i.e., the master data that you want your tables to have when the database is created. Using code first, you may not have an EDMX file with relationships and schema as it does not depend upon Entity Framework designer and its tools and would have more control over the database since you are the one who created classes and relationships and managing it.

There is a new concept of code-first migrations which makes the code-first approach easier to use and follow; however, in this article, I’ll not use migrations but old method of creating DB context and DB set classes so that you understand what is under the hood. Code-first approach could also be used to generate code from an existing database, so basically it offers two methods in which it could be used.

Entity Framework Approaches in Action

Enough of theory, let’s start with the implementation part one by one and step by step to explore and learn each approach. I’ll use a sample project and a console application to connect with the database using EF for all the three approaches. I’ll use basic sample tables to explain the concept. The intent here is to learn the concept and implement it and not to create a large application. When you learn it, you can use the concepts with any large enterprise level application or any big database server which can have thousands of tables. So, we’ll follow the KISS strategy and keep it simple here.

Model First

Create a simple .NET Framework console application by opening your Visual Studio and choosing the console application template. We can choose any application type like a web application that could be ASP.NET web forms, MVC or Web API or Windows application/WPF application. You can give a name to the project and solution of your choice.

We’ll have Program.cs, the only class and App.config in our project.

Code
1. using System;
2. using System.Collections;
3. using System.Collections.Generic;
4. using System.Linq;
5. using System.Text;
6. using System.Threading.Tasks;
7. namespace EF_MF {
8. class Program {
9. static void Main(string[] args) {}
10. }
11. }

Right-click the project and click on "Add a new item". This will open the window to add a new item, just go to Data as shown in the below image and choose ADO.NET Entity Data Model as shown in the following. Give it a name, for e.g., EFModel and click on Add.

Once you click Add, you’ll be shown to choose Model Contents, and this is the place where you choose what approach you want to use for data access out of the three EF approaches. So, choose Empty EF Designer because we would be using model first approach and create a model from scratch.

Once you click “Finish”, you see the empty designer window that is the .edmx file. The name of the .edmx file in solution is the name that we provided while adding the EF designer model. In the toolbox, you see the tools available that you could use to create entities and associations between them.

Drag and drop the Entity tool from the toolbox into the designer. It will create an empty entity as shown below with one property named Id, saying it is a primary key. Here you can rename the entity and add more scalar properties.

Right click on the entity created and add a new scalar property as shown in the following. Rename the name of the entity from Entity1 to Student. You can rename the entity by double-clicking on the entity name and right click and rename the entity.

Name the scalar property as “Name”.

In an analogous way, add a new entity named Class and add a new property named ClassName. We here are trying to create a student and a class relationship where a class can have multiple students. So, we have an option to choose Association from toolbox as shown below and drag the Association tool from Class to Student and it showed 1-to-many relationship.

We are not adding more entities and trying to understand the basic functionality of these two entities. Right-click on the designer and click on “Generate Database from Model…” option to generate the scripts.

Once you click on “Generate Database from Model…” option, you’ll be asked to choose a data connection as shown in the following. You can choose a new connection or an existing one. I’ll choose a new connection but before that, I’ll create an empty database on my SQL server so that I do not have to modify my scripts to provide a database name. By default, the generated scrips create tables in the master database if DB name is not specified.

Open your SQL Server and create a new database and name it as per your choice. I am naming it StudentDB as shown in the following,

Coming back to the window where we needed to provide the connection details. Choose your data source and server name as shown in the following The server name should be the server where you created the empty database. Now in the selecting database option, expand the dropdown and you should see your database name. Select the database name.

Once you select the database name, a connection string would be generated as shown below and it will say that the connection string would be saved in the App.Config file with the name EFModelContainer. EFModelContainer is the name of the connection string. Since it is an EF generated connection string, you see it has the information about EF CSDL, MSL and SSDL files as well that would be present in our application. Click Next to proceed.

The next step is to choose your Entity Framework version. We’ll use 6.x i.e. it will automatically pick the latest stable version with EF6. Click Next.

As a last step of the wizard, you’ll see the needed SQL scripts created for us. You can choose to rename the scripts but by default, it takes the name as <model name>.edmx.sql. I’ll leave it as it is and click Finish to proceed.

You’ll see the script located in solution explorer now. Double click to open it and it opens in a window where you have an option to directly execute it.

Before executing the scripts let’s first install Entity Framework latest stable version from the Nuget package manager. It is very simple to do. Go to Tools in Visual Studio, then choose NuGet Package Manager->Package Manager Console as shown in the following,

The NuGet Package Manager console window will be opened at the bottom of Visual Studio by default. Now choose the project for which the Entity Framework package needs to be installed. And in the command that says PM> type Install-Package EntityFramework and press enter. We do not specify the version of the Entity Framework as we want the latest stable package to be downloaded and added to our project as a DLL.

Once done with installing Entity Framework, go back to the script window and on the top left, you see the button to execute the scripts as shown below. Press the button to execute the scripts.

Once you click on Execute, a new window will show up asking for server and database details. Fill in the details specific to your server and database as shown below and click Connect.

Once done, go to your database server and you’ll see the tables are created for our database StudentDB. The names of the tables are pluralized, and Student table has a foreign key reference to Classes table and the foreign key is automatically created named Class_Id referencing Classes table. It is magical, isn’t it?

In our solution explorer, we see the .edmx file and the context classes created and model classes for Student and Class entities. This is all done in the background by EF designer. So, till now we did not write a single code and got all the code generated by EF.

Open the EFModel.Context.cs class file and we see the name of the DbContext class that got generated is EFModelContainer. Remember that is the name of our connection string stored in App.Config. The name of the context class has to be the same as connection string name for EF to know the relation. So, you can have multiple DB context classes in the same solution with different names and pointing to different connection strings. You can explore more on DbContext class and what other ways you can make its relation to the connection string in the config file. One another way is to call its base constructor by passing name of the connection string as a parameter to its parameterized constructor. But for the sake of understanding, we’ll stick to this implementation.

Now it’s time to test our implementation and check if the entity framework is actually working and helping us in database operations or not. So, in the Program.cs class’s Main method we’ll try to write some code that saves a new class for us in the database. Create a new object of EFModelContainer and in the container, we get the entity classes collection coming from DbContext. Add a new Class. The class is the name of the entity class generated for us via the designer. And name the class as “Nursery”. We do not have to specify the id attribute for the class, as EF will automatically handle this and provide an Id to a newly added record. The code to add a new class named “Nursery” is shown in the following image. The container.SaveChanges statement is the statement that when executed will add a new record in the database for us.

Code
1. using System;
2. using System.Collections;
3. using System.Collections.Generic;
4. using System.Linq;
5. using System.Text;
6. using System.Threading.Tasks;
7. namespace EF_MF {
8. class Program {
9. static void Main(string[] args) {
10. EFModelContainer container = new EFModelContainer();
11. container.Classes.Add(new Class() {
12. ClassName = "Nursery"
13. });
14. container.SaveChanges();
15. }
16. }
17. }

Just run the application and let the main method code execute. Once done, go to your database and check the Classes table, you’ll see a new record is added in the Classes table with the class name “Nursery” which is what we provided while wanted to add a record. So, it works . Notice the Id that is auto generated by entity framework.

Now, let’s try something new and try to add a new class but this time with students. We have a relationship of class with students that is a class can have many students and a student will belong to one class. Check the created model classes for Student and Class if you want to explore how the relationship is maintained in the classes. So, this time, we’ll add a new class and add some students to that class. Entity Framework should automatically add these students to the Students table and make the relationship with the Class Following is the simple self-explanatory code for doing this.
1. static void Main(string[] args) {
2. EFModelContainer container = new EFModelContainer();
3. ICollection < Student > students = new List < Student > {
4. new Student() {
5. Name = "Mark"
6. },
7. new Student() {
8. Name = "Joe"
9. },
10. new Student() {
11. Name = "Allen"
12. }
13. };
14. container.Classes.Add(new Class() {
15. ClassName = "KG", Students = students
16. });
17. container.SaveChanges();
18. }
In the above code, we create an EFModelContainer object and a list of Students by adding three students into it. Now add a new class to the container object, just like we did in the last example and assign students to the Students property of the Class object. Last but not least, container.SaveChanges().

Run the code and go to the database. Check the Classes table and see a newly created class row with name “KG” that we supplied from the code.

Now, go to the Students table and we got three students created there which we supplied from code and check the Class_Id column that has the foreign key reference to the newly created class with Id 2. Amazing

Like this, you can perform complex queries and other CRUD operations on your database by writing simple code. Try to perform more operations like editing, deleting fetching the records to understand more. Let’s move to our next topic that is database first approach with Entity Framework.

Database First

Like we did in model first approach, create a new console application and name it EF_DBF.

Code
1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Text;
5. using System.Threading.Tasks;
6. namespace EF_DBF {
7. class Program {
8. static void Main(string[] args) {}
9. }
10. }

The second step is to add a new ADO.NET Entity Data Model to this project. Name it as your choice. I named it ModelDBF.

Now, from the choose model window, we’ll choose the option of EFDesigner from the database, this will help us to create an entity framework designer from the existing database.

Next, choose the connection for the database, i.e provide the details on the wizard for your existing database. I’ll take the database we created with our model first approach i.e. StudentDB. Once we choose the database, we see the entity framework connection string and the name of the connection string to be saved in App.Config i.e. StudentDBEntities. You can also change it if you want. Click Next.

Choose the EF version. I already explained the meaning of 6.x. We’ll choose the same and click Next.

Now in this step, you would be shown all the database objects related to the database you selected initially, and it is your choice to include or exclude the objects you need. The objects could be tables, views or stored procedures. Since we do not have views and stored procedures, we’ll only choose our two tables as shown in the following. Since we already have the table’s name in pluralized forms, I do not want to complicate this by again pluralizing it and appending one more ‘s’ to my entity classes, so I unchecked that option of pluralizing the entity names. Provide model namespace or leave it as it is with the default name provided and click Finish.

Once you click Finish, you see the entities created in the EF designer for the database objects we selected from the database. We notice that it is like what we had when we manually created the entities and generated a database out of it. This EF designer also takes care of the foreign key relationship and shows the one to many associations between class and student entities.

Time to add Entity Framework package like we did in the first approach discussed. Make sure you choose the right project i.e. the current project where you need to add EF. Type the command in package manager Console and press enter.

Now, when we open the generated ModelDBF.Context.cs, we see the name of the partial class as StudentDBEntities i.e. the name of the connection string we got stored in App.Config. I have already explained the logic behind it in the last,

Time to see some action now. Add the code below to the Program.cs Main() method.
1. static void Main(string[] args) {
2. StudentDBEntities container = new StudentDBEntities();
3. ICollection < Students > students = new List < Students > {
4. new Students() {
5. Name = "Harry"
6. },
7. new Students() {
8. Name = "Jane"
9. },
10. new Students() {
11. Name = "Nick"
12. }
13. };
14. container.Classes.Add(new Classes() {
15. ClassName = "Class 1", Students = students
16. });
17. container.SaveChanges();
18. container.Students.Add(new Students() {
19. Class_Id = 1, Name = "Ben"
20. });
21. container.SaveChanges();
22. }
In the above code, we are trying to create an object of StudentDBEntities class, i.e., our context class and a collection of students to be added to our database. To check if the relationship is working fine or not, we’ll add a new class named “Class 1” and assign the Students property to our students collection and SaveChanges() again to check if individual student insertion is working or not, we’ll add a new student named “Ben” to the Students model and assign the class id to 1 i.e. the existing class we have in database and SaveChanges(). Put a breakpoint in Main method and press F5.

When the application runs it will hit the breakpoint. Navigate through the statements by pressing F10 and stop at line 24 i.e. before we add a new student. Since we already executed the code for saving changes for newly added class. Let’s go to the database and check.

In the database, we see the newly added class has a new row in Classes table with ID 3.

And in the Students table, we see that three students that we added from code got inserted in the table with the class id as 3 i.e. the newly created class.

Now get back to Visual Studio and execute the line for adding a new student.

Code

container.Students.Add(new Students() {Class_Id = 1, Name = "Ben"});
container.SaveChanges();

Once done, check the database and we see a new student having the name “Ben” added to our Students table having Class_Id 1 that we assigned in code.

We see our database first approach also working fine. Again, you can try other DB operations in the code at your will and play with the code to explore more. Let’s move on to code first approach.

Conclusion

In this article, we closely looked at how we can leverage Entity Framework approaches and as per our need use those. I took the basic console application to explain the concepts, but these could be used in any enterprise level application that uses WebAPI’s, Asp.net projects or MVC projects as well. We briefly discussed the pros and cons of the approaches used and tried to create small sample applications to see those working. There is a lot in Entity Framework to explore; e.g. what is underlying architecture, how the architecture works, transaction management, loadings, etc.

I personally find EF as one of the best and powerful ORMs to use that seamlessly integrates with any .NET application. I purposely skipped the code first approach and code first migrations in this article as it would make the article lengthy. In my next article, I’ll explain the code first approach using Entity Framework and Code First Migrations in Entity Framework. Download the complete free eBook (Diving into Microsoft .NET Entity Framework).

Next Article >>