In a world full of C# we go Back to Basic!

READ MORE
entity frameworkhow-tonetnet core

How to: Quickly get into Entity Framework

.

March 7, 2019 by

One of the best things about .NET is using Entity Framework out-of-the-box. I remembered those days when i used sqlconnection, sqlcommand, etc., yeah it was good times.

If you are still using those commands, i will teach you something better, it is your decision if you want to use your way, or “THE WAY” ✨.

If you want to cheat and see the end, you can download the project from here

Prequisites

  • Visual Studio 2017, you can download the community edition here
  • Optional: If you want to use .NET Core, you need to download the installer here
  • And as always, a lot of desire to learn πŸ§™β€β™‚οΈ

What is Entity Framework?

The official definition by Microsoft is:

Entity Framework is an object-relational mapper (O/RM) that enables .NET developers to work with a database using .NET objects. It eliminates the need for most of the data-access code that developers usually need to write

In less words, a ninja way to connect to your database.


"Image from Microsoft explaining Entity Framework"

We will do 2 different web apps using Full/classic .NET Framework (until Microsoft give us officially ASP.NET Core with VB support):

  • ASP.NET MVC using SQL adapters, connections and commands
  • ASP.NET MVC using Entity Framework

So, let’s back to basic!

Playing with ASP.NET MVC using SQL adapters, connections and commands

Creating the database

The first thing we need to do it’s create a database, if you don’t know how to create it, follow my path young padawan or if you know how to do it, just copy and paste the (code) [go-to-sql-code]

In your Visual Studio with the project we created before open, click on View > Server explorer.

With the server explorer opened, right click on Data Connections > Create a new SQL Server database.

And finally, type the following:
– Name server: (localdb)\MSSQLLocalDB
– Server connection: Windows authentication
– Name of the database: TutorialDB

And that’s it! We created the database, right click on the connection we created and then New Query.

Copy and paste the code, click on the SQL tab in the menu, and finally, Execute.

(sql-code)

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'Movies' AND xtype = 'U')
  CREATE TABLE Movies(
    id INT IDENTITY(1,1) NOT NULL,
    Name VARCHAR(100),
    Genre VARCHAR(50)
  )
GO

INSERT INTO Movies
VALUES	('Deadpool 2', 'Action'),
  ('Avengers: Infinity War', 'Fantasy'),
  ('Incredibles 2', 'Adventure'),
  ('Black panther', 'Sci-Fi')

If you see “(4 row(s) affected))”, we are done, yeah, it’s was quite boring, but it’s neccesary if we want to continue.

"NOTE: Don’t forget to add the connection string of this database we created! Go to Web.config file and add the code after the configuration tag."
<connectionStrings>
    <add name="MovieDBContext" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=TutorialDB;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
</connectionStrings>

Coding time πŸŽ‰

After we created the database, it’s time to move on to our Visual Studio project.

Before i continue, let me tell you something, if you don’t know anything about MVC pattern, you need to check it out first (other post)[link-to-MVC-post] about the MVC, because maybe you will not get it all.

With the above explained, it’s time to continue, we need to create a model, a view and add a function in the controller.

Imports System.Data.SqlClient
Imports System.Threading.Tasks

Public Class Movie
    Public Property id As Integer
    Public Property Name As String
    Public Property Genre As String

#Region "data"
    Async Function GetAll() As Task(Of List(Of Movie))
        Dim list As New List(Of Movie)
        Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("MovieDBContext").ConnectionString)
            con.Open()
            Using SqlCommand = New SqlCommand("SELECT * FROM Movies", con)
                Using dr As SqlDataReader = Await SqlCommand.ExecuteReaderAsync
                    While dr.Read
                        list.Add(New Movie With {.id = dr.GetInt32(0), .Name = dr.GetString(1), .Genre = dr.GetString(2)})
                    End While
                    Return list
                End Using
            End Using
            con.Close()
        End Using
    End Function
#End Region
End Class
"Models/Movie.vb"
@ModelType List(Of Movie)
@Code
    ViewData("Title") = "Movies"
End Code

<table class="table">
    <thead>
        <tr>
            <th scope="col">id</th>
            <th scope="col">Name</th>
            <th scope="col">Genre</th>
        </tr>
    </thead>
    <tbody>
        @For Each movie In Model
            @<text>
                <tr>
                    <td>@Html.DisplayFor(Function(x) movie.id)</td>
                    <td>@Html.DisplayFor(Function(x) movie.Name)</td>
                    <td>@Html.DisplayFor(Function(x) movie.Genre)</td>
                </tr>
            </text>
        Next
    </tbody>
</table>
"Views/Home/Movies.vbhtml"
Imports System.Threading.Tasks
Public Class HomeController
    Inherits System.Web.Mvc.Controller

    Function Index() As ActionResult
        Return View()
    End Function

    Function About() As ActionResult
        ViewData("Message") = "Your application description page."

        Return View()
    End Function

    Function Contact() As ActionResult
        ViewData("Message") = "Your contact page."

        Return View()
    End Function

    Async Function Movies() As Task(Of ActionResult)
        Dim list As New List(Of Movie)
        Dim m As New Movie
        list = Await m.GetAll
        Return View(list)
    End Function
End Class
"Controllers/HomeController.vb"

Easy peasy. We need to focus in this area.

Function GetAll() As List(Of Movie)
    Dim list As New List(Of Movie)
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("MovieDBContext").ConnectionString)
        con.Open()
        Using SqlCommand = New SqlCommand("SELECT * FROM Movies", con)
            Using dr As SqlDataReader = SqlCommand.ExecuteReader
                While dr.Read
                    list.Add(New Movie With {.id = dr.GetInt32(0), .Name = dr.GetString(1), .Genre = dr.GetString(2)})
                End While
                Return list
            End Using
        End Using
        con.Close()
    End Using
End Function

What are we doing? Well, we are bringing all the movies that exists in the database, just that, you need to open the connection, put the command you need to bring and execute the code. 12 lines of code.

The result of all our work above is this:

What if i tell you that with Entity Framework we can reduce it just in one line? Do you believe in me? Or what about we can create a database in SQL Server and with a few steps migrate it to MySQL? Still not believing in me?

Today we will do it in SQL Server, in another post we will connect us to MySQL. Not this time my friend.

Playing with ASP.NET MVC using Entity Framework

Before we continue, it’s neccesary in this project install the Entity Framework package, so in the Console Package run:

Install-Package Entity Framework

Let’s move on.

Creating the database

Where is the magic cory? Where is the magic? Maybe you are telling to me.

Well, well, well.

Entity Framework have 3 different ways to connect to your database:

  • Entity Framework Database-First
    • Advantages:
      • The visual designer is beautiful using Visual Studio
      • It’s very easy to implement because it’s automatic
      • And the most important thing, it’s because you like more SQL
    • Disadvantages:
      • The automatic generated code will be big, very very big, if you have a problem or something, you will cry
      • If you want to add a column or something you need to generate again de edmx every time
  • Entity Framework Code-First
    • Advantages:
      • You control everything about the code, because we are creating all from the very beginning
      • You don’t touch any of the database, Entity framework it’s the boss
      • You don’t need to bring on the sql query everywhere, because you can generate the database on-demand
    • Disadvantages:
      • You need to understand Entity framework more than the other 2 ways
      • You need to use Linq
      • If the database it’s big, you may be have a lot of trouble
  • Entity Framework Model-First
    • Advantages:
      • The visual designer is very easy to understand, it’s like you are using database diagram in SQL Management Studio
      • It’s very productive in small projects, if you don’t like coding in Visual Basic or SQL, it’s the best of two worlds
    • Disadvantages:
      • You don’t have any control of the database or the generated code, Entity framework will do the work for you
      • Be careful what are you doing, because if you make a change or something you will lose data

Obviously, there is a Entity Framework Core, and it’s quite different, but we will talk about later.

We will use Entity Framework Code-First because it’s the more easy way to control everything without touching SQL (like the other project).

In your Visual Studio project, in the Models folder, add 2 files, one will be called Movie.vb and the other one, MovieDBContext.vb.

Imports System.Data.Entity

Public Class MovieDBContext
    Inherits DbContext

    Public Sub New()
        MyBase.New("DefaultConnection")
    End Sub

    Public Property Movies As DbSet(Of Movie)
End Class
"Models/MovieDBContext.vb"
Imports System.Data.Entity
Imports System.Threading.Tasks

Public Class Movie
    Public Property id As Integer
    Public Property Name As String
    Public Property Genre As String

#Region "data"
    Private ReadOnly _dbContext As New MovieDBContext
    Async Function GetAll() As Task(Of List(Of Movie))
        Return Await _dbContext.Movies.ToListAsync
    End Function
#End Region
End Class
"Models/Movie.vb"

Do you remember what i tell you about to convert 12 lines of code in just one? Check the GetAll function above.

What are we doing there? Well, let’s talk about DbContext.

What is a DbContext?

The official definition by Microsoft is:

A DbContext instance represents a combination of the Unit Of Work and Repository patterns such that it can be used to query from a database and group together changes that will then be written back to the store as a unit. DbContext is conceptually similar to ObjectContext

In less words, is one of the most important things in the Entity framework, it’s like a bridge between your code and the database.

With the DbContext class you can do:

  • Querying
  • INSERT, UPDATE, DELETE
  • Manages relationships

Just for example, there is alot more, but it’s really difficult to find someone who had been used Entity Framework 100%.

Let’s take a look the MovieDBContext file.

Public Class MovieDBContext
    Inherits DbContext

This is the most important thing in the file, because without it, all the things to write below didn’t work, why it’s important? because we are extending the DbContext class adding here the connectionString and the “tables” we will create later.

Public Sub New()
        MyBase.New("MovieDBContext")
End Sub

Public Property Movies As DbSet(Of Movie)
"connectionString and tables"

Next, what are we doing here? We are saying to the DbContext which connectionString name we will use later (MovieDBContext) and adding the tables we want to create/querying/update/delete.

"NOTE: Add this in the top of the file after the closing configSections tag"
<connectionStrings>
  <add name="MovieDBContext" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=TutorialDB;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
</connectionStrings>
"Web.config"

It’s time to create the database! Open the Console Package and write Enable-Migrations, with these, depending of the configuration of your pc, it will take time to load everything in the project, but you will know when it finishes when a Configuration.vb appear in front of your eyes and a new folder called Migrations.

Everything is already setup, but before we continue, we need to add a “Migration”.

What are migrations?

In simple words, It’s a tool that turns your models and relationships into a way that Entity Framework fully understands and apply to the database.

Let’s create a Migration, run Add-Migration InitialΒ in the Console Package, when it finishes another file with popup with two subs:

  • Up
    • It’s the thing will be applied into the database, in this example, will create a table called “Movies” with id, name and genre columns.
  • Down
    • The opposite of Up, it will reverse the changes applied in the database, in this example, will drop the table.

To finish, write Update-Database in the Console Package and we-are-done!

Coding time πŸŽ‰

Remember the last project? Well, we need the same HomeController and the same Movies View.

Imports System.Threading.Tasks

Public Class HomeController
    Inherits System.Web.Mvc.Controller

    Function Index() As ActionResult
        Return View()
    End Function

    Function About() As ActionResult
        ViewData("Message") = "Your application description page."

        Return View()
    End Function

    Function Contact() As ActionResult
        ViewData("Message") = "Your contact page."

        Return View()
    End Function

    Async Function Movies() As Task(Of ActionResult)
        Dim list As New List(Of Movie)
        Dim m As New Movie
        list = Await m.GetAll
        Return View(list)
    End Function
End Class
"Controllers/HomeController.vb"
@ModelType List(Of Movie)
@Code
    ViewData("Title") = "Movies"
End Code

<table class="table">
    <thead>
        <tr>
            <th scope="col">id</th>
            <th scope="col">Name</th>
            <th scope="col">Genre</th>
        </tr>
    </thead>
    <tbody>
        @For Each movie In Model
            @<text>
                <tr>
                    <td>@Html.DisplayFor(Function(x) movie.id)</td>
                    <td>@Html.DisplayFor(Function(x) movie.Name)</td>
                    <td>@Html.DisplayFor(Function(x) movie.Genre)</td>
                </tr>
            </text>
        Next
    </tbody>
</table>
"Views/Home/Movies.vbhtml"

Press F5 and check the results, will be the same as before.

Let’s go back a little bit, remember how can we get the results?

Return Await _dbContext.Movies.ToListAsync

Yeah, this special line, what are we doing here? Well, we are telling the _dbContext variable to bring on the movies, all of them in a list. Cool, right? Entity Framework do all the hard work for us.

Today we learn:

  1. How to create a database using Visual Studio without Entity Framework
  2. What is Entity Framework
  3. DbContext
  4. Migrations
  5. ToListAsync Function
  6. The different ways to connect to database
  7. Difference between SqlCommand and Entity Framework
  8. How to create a Database with Entity Framework
  9. How to apply migrations

Are you in trouble? Have any questions? Contact me

dmcory
Written by

dmcory

I am a mexican Back-End Developer with 6 years of experience and 3 year of MSSQL Database Administrator. Experienced with all stages of the development cycle for web apps. Strong experience in project management and leadership.

TOP
Copied!