#database migrations

// Models > OdeToFoodDb.cs using System; using System.Collections.Generic; using System.Data.Entity; using System.Linq; using System.Web; namespace OdeToFood.Models { // Connection string public OdeToFoodDb() : base("server=production;initial catalog=fooddb;integrated security=true") { } public class OdeToFoodDb : DbContext { public DBSet Restaurants { get; set; } public DBSet Reviews { get; set; } } }

Method 2

You can store connection strings in the Web.config file that exists in the root of the application. In OdeToFoodDb.cs, just use the name of the connection string you want to use instead of hard coding the value. This way, you don't need to recompile the application when you want to point to a different database. All you need to do is update the Web.config file.

Place the connection string between the opening and closing tags of connectionStrings in the Web.config file. Fill in the appropriate values for the connection string's attributes.

// Web.config (root directory) ... <connectionStrings> <add name="DefaultConnection" connectionString="DataSource=(LocalDB)\v11.0; Initial Catalog=OdeToFoodDb; Integrated Secutiry=SSPI; AttachDBFilename=|DataDirectory|\OdeToFoodDb.mdf" /> </connectionStrings> ...

// Models > OdeToFoodDb.cs public OdeToFoodDb() : base("name=DefaultConnection") { }

You should be able to see the database in the Solution Explorer inside the App_Data folder. You may need to select the App_Data folder and then click the "Show All Files" icon in the Solution Explorer for it to display. It should end with the file extension *.mdf.

Configure How the Entity Framework Creates Your Schema

You can use the feature called Entity Framework Migrations. It allows you to configure the database schemas using C# code. It also allows you to initially populate some data into the databases, track changes that you make in your entity classes, and keep the database schema in sync with the changes you make in your C# code.

Get Started with Migrations

Open the Package Manager Console. It's essentially a PowerShell command line.

Method 1: View > Other Windows > Package Manager Console

Method 2: Use the Quick Launch Search Box on the top right.

Enable Migrations via the command line.

Type in: Enable-Migrations -ContextTypeName OdeToFoodDb

You need to specify the database context you want to enable Migrations for, which in this case is OdeToFoodDb.

A new folder called Migrations should appear inside the root directory of the project.

Configuration.cs

Inside the newly made Migrations folder is a Configuration.cs file and another file that's basically a schema change script. The Configuration.cs file is all about controlling Code First Migrations (how do you want it to perform, when should it run, etc.).

Example: Configuration.cs

// Migrations > Configuration.cs namespace OdeToFood.Migrations { using System; using System.Data.Entity; using System.Data.Entity.Migrations; using System.Linq; internal sealed class Configuration : DbMigrationConfiguration { public Configuration() { AutomaticMigrationsEnabled = true; } protected override void Seed (OdeToFood.Models.OdeToFoodDb context) { context.Restaurants.AddOrUpdate( r => r.Name, new Restaurant { Name = "Sabatino's", City = "Baltimore", Country = "USA" }, new Restaurant { Name = "Great Lake", City = "Chicago", Country = "USA" }, new Restaurant { Name = "Smaka", City = "Gothenburg", Country = "Sweden", Reviews = new List { new RestaurantReview { Rating = 9, Body = "Great Food!" } } }); } // End Seed() } // End Class Configuration }

The AutomaticMigrationsEnabled property inside the Configuration.cs file is set to false by default. This means that the Entity Framework won't make any changes to your database unless you explicitly tell it to. It's a good idea to keep it set to false when you're working on a project that's more mature since you'll want to be more careful with the changes you make to the database. When you're intially starting out on a project, it's not a bad idea to set it to true to allow you to make changes and just have the database be ready for you when running the application.

The Seed() method is where you can tell the Entity Framework to populate the database with some initial data. It's good for populating tables that always need data such as a table that holds a list of postal codes or countries or for inserting and updating test data. The Seed() method executes when the database is first created and every time the database is updated (Migration updates). You usually run a database update when you want to migrate the schema.

You use the passed in argument of the Seed() method called context to insert data into the database. The property named Restaurants for the context object, refers to the Restaurants table. context.Restaurants walks up to the Restaurants DbSet on our OdeToFoodDb context.

The first parameter of the AddOrUpdate() method is what decides whether to add the following restaurant into the DbSet or update it with the following information. If the restaurant name already exists in the DbSet, it will update the restaurant's info, else it will add the row into the table. Use the AddOrUpdate() method so your information isn't duplicated every time the Seed() method runs.

How to Update the Database

You can configure an application to automatically apply updates or you can do it explicitly via the Package Manager Console. Type this command into the console: Update-Database -Verbose.

The command should output any changes made to your C# classes, which would require it to change the database. If no changes were made, it should output something like "No pending code-base migrations". It should also output "Running Seed method" so you know that the data specified in the Seed() method has been added to the database.

Sync the Database with Your Model on Database Updates

Example

Say you add want to add a property to a restaurant review such as the reviewer's name. First, just add the property in the RestaurantReview.cs class file in the Models folder and do a build.

// Models > RestaurantReview.cs using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace OdeToFood.Models { public class RestaurantReview { public int Id { get; set; } public int Rating { get; set; } public string Body { get; set; } public int RestaurantId { get; set; } // Added property public string ReviewerName { get; set; } } }

Next you can do one of two things: 1. You can use the Package Manager Console to tell the Entity Framework that you need a Migration script to move the current database schema into a new database schema that can store ReviewerName. 2. You can let the Entity Framework figure out how to do it by just updating the database via the Package Manager Console. Refer to the "How to Update the Database" section on how to do that. Note: You can only use the second method if AutomaticMigrationsEnabled is set to true in the Configuration.cs file.

Migration Script File

Remember that inside the Migrations folder is the Configuration.cs file and a Migration script file, which has a name that starts with a bunch of numbers. It would look something like this: "201210161548264_InitialCreate.cs". It's a schema change in C# code. It's not really a SQL script, but C# code that can generate SQL scripts.

The Entity Framework keeps track of which files have been applied to a database, which ones need to be applied and in what order to apply them in. This is done via a system table inside of your database called "_MigrationHistory".

Example

Below is an example of part of a Migration script file.

// Migrations > 201210161548264_InitialCreate.cs namespace OdeToFood.Migrations { using Systems; using Systems.Data.Entity.Migrations; public partial class InitialCreate: DbMigration { public override void Up() { CreateTable( "dbo.Restaurants", c => new { // (1) Id = c.Int(nullable: false, identity: true), Name = c.String(), City = c.String(), Country = c.String(), }) .PrimaryKey(t => t.Id); CreateTable( "dbo.RestaurantReviews", c => new { Id = c.Int(nullable: false, identity: true), Rating = c.Int(nullable: false), Body = c.String(), RestaurantId = c.Int(nullable: false), }) .PrimaryKey(t => t.Id) .ForeignKey("dbo.Restaurants", t => t.RestaurantId, cascadeDelete: true) // (2) .Index(t => t.RestaurantId); // (3) // (4) } ... } }

(1) Setting identity: true specifies that Id will be an identity column, which means in SQL Server, it will auto populate when you insert a new row.

(2) A foreign key is used to reference another table. In this case, the foreign key for a review in the RestaurantReviews table will link the review to a specific restaurant.

(3) An Index is applied to RestaurantId, because it will probably be important to query reviews when given a specific RestaurantId so we can find all the reviews for a given restaurant.

(4) You can run many types of commands inside the Up() function, which will run during a Migration. You can even run raw SQL statements via the Sql() method. Example: Sql("UPDATE ...");

Sometimes the fields generated inside the database are given types that we don't want. You can change this via data annotations.

Related Links