Learning Entity Framework

Learning Entity Framework

Home \ Journal \ Learning Entity Framework
Published: 01/08/2019

Where I started

Any class that inherits from DbContext is using the Entity Framework. DbContext is an Entity Framework class and to use this you need to include the namespace using statement.

Screenshot 2019-08-01 at 20.03.16.png

On the property within the class you use:

Screenshot 2019-08-01 at 20.04.12.png

These represent the entities that you want to query and persist. The training example shows how you'd use DbSets<> to retrieve the restaurants and restaurant reviews data e.g.

Screenshot 2019-08-01 at 20.04.54.png

In the relevant controller (e.g HomeController.cs) you'll need to create an instance of the class you're using with Entity Framework.

Screenshot 2019-08-01 at 20.05.42.png

This is known as Instantiating.

You then go to the Action (Personal Reminder: Actions are methods that sit inside a controller) and set the model within that action as a list from our Entity Framework Class.

Screenshot 2019-08-01 at 20.06.45.png

In your action you then can return the view including the model you created.

Screenshot 2019-08-01 at 20.07.27.png

When this is run the Entity Framewrok will go into the SQL Server, find the stored restaurants, retrieve all of them and put them into a list. Although if you're starting from scratch you will have no restaurants, building the project will check to see if a database exists, if it doesn't it will create one for you, you can view this in Visual Studio's SQL Server Object Explorer and manually enter data.

Working Examples

Models

Restaurants

Screenshot 2019-08-01 at 20.09.13.png

Restaurant Reviews

Screenshot 2019-08-01 at 20.13.46.png

Entity Framework Controller class

Don't forget, to use DbContext you need to add the EF namespace into scope.

Screenshot 2019-08-01 at 20.14.35.png

Home controller class

Screenshot 2019-08-01 at 20.15.29.png

Connection Strings

To connect to the database you need a connection string, these can be hard coded in your controler although it is bad practice and you should use you the web.config file in the route of the application.

You will find a connectionString tag in the web.config file already, which uses a default connection string for a Data Source=(LocalDb) which is a Local database using SQL server. The project template puts this in place when you create the application.

The Catalog may need to be tweaked so that it doesn't inlcude aspnet and the numbers over your name, this will make it easier to read and maintain. There is also a similar tweak with the name of the file that it attaches to in the data directory in that you can remove aspnet and the random code at the end but do not remove the .mdf extension at the end.

To enable the Entity Framework to use your new connection string, you go to the controller that's using DbContext and setting properties with DbSets<> and create a base class contrustor, give it the name of the connection string you set in the web.config.

Screenshot 2019-08-01 at 20.16.41.png

Running the application again, will point to a new database which will be empty but you can still use the Data base explorer and find the new database and add data manually. You should also be able to see the database files in the App_Data file in the solution explorer (you may need to 'Show All Files' for them to be visible).

Entity Framework Migrations

How to influence how the entity framework creates a schema and how to have it populate initial data into the data base. You need to use Entity Framework Migrations... Migrations are a feature of the Entity Framework that allows you to configure database schemas with C# code and migrations and can then track changes that you're making in your entity classes.

Where to get started

You need to use the powershell/ package manager console and use the following command:

Screenshot 2019-08-01 at 20.17.28.png

You need to specify a Context Type Name which is the name of your DB Context that you'd like to enable Migrations for.

By pressing enter, it starts some logic that is part of the code first migrations of the Entity Framework that goes into the environment, looks at classes, looks at the seeds and looks at any existing database. It will then add a new folder with new files into your solution. The folder is called Migrations and the files inside are called Configuartion.cs and a schema change script written in C#.

The Configuration Class

The Configuation Class is about controlling code first Migrations, how you want it to perform and when should it run.

The most notable setting is the:

Screenshot 2019-08-01 at 20.18.19.png

By default it will be false, this means that the Entity Framework wont make any changes in your database unless you specifically tell the Entity Framework that you want it to make changes.

By setting this to true as in the beginning you often want be able to make changes in the C# code and have the database be ready for the application to run and update. Once the App is a little more advanced you may want to set this to false again to prevent any unwanted changes being applied to the database.

The Seed Method

The seed method is where you can tell the Entity Framework to populate the database with some inital data, every time you update the database it's going to envoke the seed method. This gives you the opportunity to populate the tables that need data.

The seed Method should look similar too:

Screenshot 2019-08-01 at 20.18.55.png

Here is a basic example of how to populate the database with some Restaurants, you will need to add some namespaces for this to build successfully. (using System.Collections.Generic and 'ProjectName'.Models)

Screenshot 2019-08-01 at 20.19.31.png

This code is looking at the DbSet we created for Restaurant in the EfClassNameDb context and its add or update the following restaurants.

What this means is it looks to see in the database if any of these Restaurants exist and its looking them up by name so it will see if there is a Sabatino's in the Restaurants table and if there is one it will update the data if not it will add an entry, it will even add a review for the Smaka restaurant for the last entry.

Everytime you run a migration updated, this seed method will run which is why its important we're not just running an Add as we might update the database multiple times which would add multiple versions of the same Restaurant, by including update it looks to see if they already exist before adding anything.

To update the database you want to go to the powershell/ package manager console and use the following command:

Screenshot 2019-08-01 at 20.20.19.png

Using the -Verbose flag allows you to view the SQL statements being applied to the target database.

Two important pieces of output here are:

Screenshot 2019-08-01 at 20.20.19.png (1)

This means nothing in our C# class has changed so it didn't have to change database.

Screenshot 2019-08-01 at 20.22.33.png

The seed method has been run that will put this information into the database which you should be able to see by either refreshing the application (if you've created the views to show the data) or by going into the Database Explorer and viewing the Data within the table.

You may also want the database to be synchronised with your Model, for example if you want a review to also show the name of the person leaving the review you would be able to update the RestaurantReview.cs Model to add a new property called Reviewer Name.

Screenshot 2019-08-01 at 20.23.21.png

Then when you run update-database -verbose again it will update the database and add the new data. The only reason you can update this correctly by simply running this command is due to having the AutomaticMigrationsEnabled set to true.

Screenshot 2019-08-01 at 20.23.55.png

Once you have updated the database you will see in the output that the Entity Framework has added the Reviewer Name to the table. To add a default name you can just update the seed method and re-run the update-database -verbose command.

Screenshot 2019-08-01 at 20.24.24.png

LINQ

LINQ stands for Language INtegrated Query.

There are two different style queries you can write with LINQ:

Screenshot 2019-08-01 at 20.25.40.png

These LINQ queries are known as the Comprehension Query Syntax.

The second uses extension methods and lambda expressions:

Screenshot 2019-08-01 at 20.26.25.png

These LINQ queries are known as the Extension Method Syntax. Using these enables you to use some additional features such as Skip() and Take() which are commonly used for paging.

There are a lot of LINQ Operators available, to explore the syntax and possibilities you can here at: 101 LINQ Samples.

Trying out some LINQ in our application

In this LINQ Query we ordering by number of reviews in Descending order, we have had to create a new ViewModel for the list of RestaurantReviews to give it the list of properties that we need for the queries below.

(Example is using the Extension Method Syntax)

Screenshot 2019-08-01 at 20.27.30.png

Filtering Queries

Filtering can be does using a Where Clause. The .Where() clause allows you to return results based on filters you've created.

Instead of using a Where() operator in a hard coded value as we have done in the examples above we should add a parameter as user may come to the page searching for a specific term.

Add a parameter to the Index ActionResult named searchTerm and provide a default value of null, which is slightly redundant because if the MVC run time doesnt find a searchTerm in the request it will pass along a null for this string parameter. Now we can use the searchTerm with a .Where() operator.

Screenshot 2019-08-01 at 20.28.05.png

To use this you can build your solution and view the Index Page in your browser, in the URL you can add a query to the end e.g.

Screenshot 2019-08-01 at 20.28.58.png

This will search for every Restaurant with a name that starts with Z. You can use filtering for many things including common options such as search inputs and blog filters. You'd just need to build your form so you can submit the get requests.

Remember you'll need to name your input to match your Index Action Parameter name e.g. searchTerm this is because ASP.NET matches things up in the request by name.

Screenshot 2019-08-01 at 20.29.33.png

It's nice to use a method="get" request on the form as it means that someone can use the url including the query string and send this to others and they will have the same result. You do have to be careful about when to use method="get". You wouldn't want to use it for a form that is submitting information to the server to save in the database, something that is processing a credit card or personal information are considered post operations. These are known as write operations and a search is known as a read and is perfectly safe to use a method="get".

to be continued...

I will continue to document my training progression and share here in my journal