Play framework MySQL query where

Code First to an existing database

  • 6 minutes to read

This video and step-by-step walkthrough introduce you to Code First development for an existing database. Code First allows you to define the model using C # or VB.NET classes. Optionally, additional configuration can be performed using attributes in their classes and properties, or using a fluid API.

to watch video

This video is now available on Channel 9.

requirements

You need to Visual Studio 2012 or Visual Studio 2013 installed to complete this walkthrough.

You also need version 6,1 (or higher) the Entity Framework Tools for Visual Studio . For more information about installing the latest version of the Entity Framework Tool, see Get Entity Framework.

1. Create an existing database.

Typically, if you are targeting an existing database, it will already be created, but in this walkthrough we need to create an accessible database.

Now we generate the database.

  • Open Visual Studio.

  • View-> Server Explorer

  • Right click on Data Connections-> Add Connection ...

  • If you over Server Explorer not connected to a database, you must Microsoft SQL Server select as data source.

  • Connect to your local DB instance and enter as the database name Blog

  • Choose OK and you will be asked if you want to create a new database and select Yes out.

  • The new database will now appear in Server Explorer, right click on it and select new query out.

  • Copy the following SQL command into the new query, right-click the query and select To run .

2. Build the application

To ensure this, a simple console application is created that uses Code First to access data:

  • Open Visual Studio.
  • File-> New-> Project ...
  • In the menu on the left, select and Console applicationWindows out.
  • Give codefirstexistingdatabasesample as a name.
  • click on OK.

3. Reverse engineering model

We use the Entity Framework Tools for Visual Studio to help us generate code that is mapped to the database. These tools only generate code that you can enter by hand if necessary.

  • Project-> Add new element ...

  • Select in the left menu Data and then click ADO.NET Entity Data Model

  • Give bloggingcontext as the name and click OK .

  • This will make the Entity Data Model Assistant started.

  • Choose from Database Code First and click further

  • Select the connection to the database you created in the first section and click further .

  • Check the box next to Tables to import all tables, and click

After the reverse engineering process has been completed, a number of elements are added to the project. let's see what has been added.

Configuration file

An App.config file was added to the project. this file contains the connection string for the existing database.

You will also see some other settings in the configuration file. These are the default EF settings that Code First where databases should be created. Since we are mapping to an existing database, these settings are ignored in our application.

Derived context

The project was given a bloggingcontext -Class added. The context represents a session with the database and enables data to be queried and saved. The context makes a ** dbset- ** available for each type in our model. You'll also notice that the default constructor has a base constructor with the syntax Name = calls. This tells Code First that the connection string to use for this context should be loaded from the configuration file.

You should always use the syntax Name = use when using a connection string in the configuration file. This ensures that if the connection string does not exist, the Entity Framework is triggered instead of creating a new database by convention.

Model classes

Eventually the project were also a Blog and a post -Class added. These are the domain classes that make up our model. You will see data annotations applied to the classes to indicate the configuration where Code First conventions do not match the structure of the existing database. For example, the StringLength -Note on Blog.Name and " Blog. Url "because they have a maximum length of 200 in the database (the Code First Standard is to use the maximum length supported by the database provider- nvarchar (max) in SQL Server).

4. read & write data

Now that we have a model, it is time to use it to access some data. Implement the Main Method in Program.cs as shown below. This code creates a new instance of the context and then uses it to create a new Blogto insert. A LINQ query is then used to find all Blogs from the database alphabetically according to titleretrieve.

You are now ready to run and test the application.

What if my database is changed?

The Code First Database Wizard is designed to generate a starting point set of classes that you can then tweak and modify. If the database schema is changed, you can either edit the classes manually or do another reverse engineer to override the classes.

Use Code First Migrations on an Existing Database

If you want to use Code First Migrations with an existing database, see Code First Migrations to an Existing Database.

Summary

In this walkthrough, we examined Code First development with an existing database. We used the Entity Framework Tools for Visual Studio to reverse a set of classes associated with the database that can be used to store and retrieve data.