Code First development with the Entity Framework
EntityFramework (EF) is data access library that lives in the System.Data.Entity namespace and was first introduced in .NET 3.5 SP1. Now at version 4.3, Microsoft recently added a code-centric development option known as code first which allows developers to define database models by creating standard classes which Visual Studio then uses to build your SQL database.
The tutorials cover the basics, but for real world projects one of the items that trips up developers is extending the built in membership table to store additional information about users, and then seeding that data when the database is recreated.
This walkthrough will take the Internet Application MVC 4 Visual Studio template and add a number of custom classes, extend the userprofile table, and then seed the database with default values every time the model changes.
1. Create a new MVC 4 project
Open up Visual Studio and create a new Internet Application, as this one uses the SimpleMembership provider that we will extend.
2. Add references to the Membership and Role providers
Even though the application has membership support built into the template, we will add the provider references into the web.config file in order for EF to create these tables automatically.
Add the following lines in the <system.web> section in your web.config:
<membership defaultProvider="SimpleMembershipProvider"> <providers> <clear/> <add name="SimpleMembershipProvider" type="WebMatrix.WebData.SimpleMembershipProvider, WebMatrix.WebData" /> </providers> </membership> <roleManager enabled="true" defaultProvider="SimpleRoleProvider"> <providers> <clear/> <add name="SimpleRoleProvider" type="WebMatrix.WebData.SimpleRoleProvider, WebMatrix.WebData"/> </providers> </roleManager> </system.web>
3. Create your data classes.
In this simple example, we’ll have an Article model which contains details of published articles, and link this to a Categories table as shown in the following diagram:
Create a new class by right clicking on the Models folder, selecting Add and then choosing Class.
Add the following 2 classes:
article.cs
public class Article
{
[Key]
public int ArticleID { get; set; }
[Required(ErrorMessage = "Article Title is required.")]
[MaxLength(200, ErrorMessage = "Article Title cannot be longer than 200 characters.")]
public string Title { get; set; }
[DisplayFormat(DataFormatString = "{0:d}", ApplyFormatInEditMode = true)]
[Display(Name = "Date Published")]
public DateTime? DatePublished { get; set; }
[MaxLength(100, ErrorMessage = "Article Author cannot be longer than 100 characters.")]
public string Author { get; set; }
public int? CategoryID { get; set; }
public virtual Category Category { get; set; }
public string Body {get; set;}
}
Note the nullable type for CategoryID, this is the foreign key for the Categories table and defines an optional one to many relationship.
category.cs
public class Category
{
[Key]
public int CategoryID { get; set; }
public string CategoryName { get; set; }
public virtual ICollection<Article> Articles { get; set; }
}
At this point, we’ll add additional fields to the User Profile table by modifying the AccountModel.cs class. Replace the definition for UserProfile with the following:
[Table("UserProfile")]
public class UserProfile
{
[Key]
[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int UserId { get; set; }
public string UserName { get; set; }
[Required]
[Display(Name = "First name")]
[MaxLength(50, ErrorMessage = "First Name cannot be longer than 50 characters.")]
public string FirstName { get; set; }
[Required]
[Display(Name = "Last Name")]
[MaxLength(50, ErrorMessage = "Last Name cannot be longer than 50 characters.")]
public string LastName { get; set; }
[Display(Name = "Full Name")]
public string FullName
{
get
{
return FirstName + " " + LastName;
}
}
[Display(Name = "Email Address")]
[MaxLength(50, ErrorMessage = "Email Address cannot be longer than 50 characters.")]
public string EmailAddress { get; set; }
[Display(Name = "Work Phone")]
[MaxLength(20, ErrorMessage = "Work Phone cannot be longer than 20 characters.")]
public string WorkPhone { get; set; }
[Display(Name = "Mobile Phone")]
[MaxLength(20, ErrorMessage = "Mobile Phone cannot be longer than 50 characters.")]
public string MobilePhone { get; set; }
}
4. Configure your database context
We’re going to remove the built-in UsersContext database definition, and move it to it’s own folder.
Delete the following section from the AccountModel.cs file:
public class UsersContext : DbContext
{
public UsersContext()
: base("DefaultConnection")
{
}
public DbSet<UserProfile> UserProfiles { get; set; }
}
Create a new folder in your solution (I called this DAL) and add a new class with the following
public class EvonetContext : DbContext
{
public EvonetContext()
: base("DefaultConnection")
{
}
public DbSet<Article> Articles {get; set;}
public DbSet<Category> Categories { get; set; }
public DbSet<UserProfile> UserProfiles { get; set; }
}
Now search and replace throughout your solution for any instances of UsersContext and replcce them with what you called your DbContext (in the example above, mine is called EvonetContext). You now have a single database context for your account and custom classes that EntityFramework will use.
Build your solution to ensure you haven’t made any errors, and have included all the required namespaces.
5. Initialise the Membership Provider
The InitializeSimpleMembershipAttribute is generated by the MVC 4 Internet template and lives in the Filters folder. It provides a lazy initialization of the underlying providers, and creates the database for storing membership, roles, and logins.
Because it can initialised later than when you need to call it, it is recommended you move the InitialiseDatabaseConnection() call to your global.asax file and remove the [InitializeSimpleMembership] call from the top of your AccountController.
Firstly add the InitialiseDatabaseConnection call to the AuthConfig.cs file in the App_Start folder:
public static void RegisterAuth()
{
WebSecurity.InitializeDatabaseConnection(
"DefaultConnection",
"UserProfile",
"UserId",
"UserName",
autoCreateTables: true);
}
Next, delete the InitialiseSimpleMembership.cs file from the Filters folder.
Finally, comment out or delete the [InitialiseSimpleMembership] line from the top of your account controller.
6. Enable Migrations
Now we can enable entity framework code first migrations. You do this by going to the Package Manager Console and typing in enable-migrations:
PM> enable-migrations
Checking if the context targets an existing database…
Code First Migrations enabled for project Evonet.
This command adds a Migrations folder to your project, which contains a configuration.cs class. This class allows you to configure how Migrations behaves for your context, and this is where you’ll seed your database with initial values.
7. Seed the database
Modify the code in the configuration.cs class that was created in the Migrations folder when you enabled migrations to initialise Simple Membership, add a test user, and seed the database with some initial data.
We also need to include another call to InitialiseDatabaseConnection or the member functions won’t work – it’s not called from our AuthConfig.cs file when we run the update-database command, hence the duplication.
internal sealed class Configuration : DbMigrationsConfiguration<Evonet.Models.EvonetContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = true;
}
protected override void Seed(Evonet.Models.EvonetContext context)
{
WebSecurity.InitializeDatabaseConnection(
"DefaultConnection",
"UserProfile",
"UserId",
"UserName",
autoCreateTables: true);
if (!Roles.RoleExists("Administrator"))
Roles.CreateRole("Administrator");
if (!WebSecurity.UserExists("test"))
WebSecurity.CreateUserAndAccount(
"test",
"password",
new
{
FirstName = "Test",
LastName = "Test",
EmailAddress = "user@server.com"
});
if (!Roles.GetRolesForUser("test").Contains("Administrator"))
Roles.AddUsersToRoles(new[] { "test" }, new[] { "Administrator" });
var categories = new List<Category>
{
new Category {CategoryName="Law", Articles = new List<Article>()},
new Category {CategoryName="Construction", Articles = new List<Article>()},
new Category {CategoryName="Media", Articles = new List<Article>()},
};
categories.ForEach(s => context.Categories.Add(s));
var articles = new List<Article>
{
new Article {Title="An article about Law"},
new Article {Title="An article about Construction"},
new Article {Title="An article about Media"},
};
articles.ForEach(s => context.Articles.Add(s));
categories[1].Articles.Add(articles[1]);
context.SaveChanges();
}
}
8. Update the database to reflect changes in the model
Now go to the Package Manager Console and type in update-database – verbose to create (or update) your database to reflect the model changes you have made:
PM> update-database -verbose
Using StartUp project ‘Evonet’.
Using NuGet project ‘Evonet’.
Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.
Target database is: ‘Evonet’ (DataSource: (LocalDb)\v11.0, Provider: System.Data.SqlClient, Origin: Configuration).
No pending code-based migrations.
Applying automatic migration: 201304130142483_AutomaticMigration.
CREATE TABLE [dbo].[Articles] (
[ArticleID] [int] NOT NULL IDENTITY,
[Title] [nvarchar](200) NOT NULL,
[DatePublished] [datetime],
[Author] [nvarchar](100),
[CategoryID] [int],
[Body] [nvarchar](max),
[Category_CategoryID] [int],
CONSTRAINT [PK_dbo.Articles] PRIMARY KEY ([ArticleID])
)
CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] NOT NULL IDENTITY,
[CategoryName] [nvarchar](max),
CONSTRAINT [PK_dbo.Categories] PRIMARY KEY ([CategoryID])
)
CREATE TABLE [dbo].[UserProfile] (
[UserId] [int] NOT NULL IDENTITY,
[UserName] [nvarchar](max),
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[EmailAddress] [nvarchar](50) NOT NULL,
[WorkPhone] [nvarchar](20),
[MobilePhone] [nvarchar](20),
CONSTRAINT [PK_dbo.UserProfile] PRIMARY KEY ([UserId])
)
CREATE INDEX [IX_CategoryID] ON [dbo].[Articles]([CategoryID])
CREATE INDEX [IX_Category_CategoryID] ON [dbo].[Articles]([Category_CategoryID])
ALTER TABLE [dbo].[Articles] ADD CONSTRAINT [FK_dbo.Articles_dbo.Categories_CategoryID] FOREIGN KEY ([CategoryID]) REFERENCES [dbo].[Categories] ([CategoryID])
ALTER TABLE [dbo].[Articles] ADD CONSTRAINT [FK_dbo.Articles_dbo.Categories_Category_CategoryID] FOREIGN KEY ([Category_CategoryID]) REFERENCES [dbo].[Categories] ([CategoryID])
CREATE TABLE [dbo].[__MigrationHistory] (
[MigrationId] [nvarchar](255) NOT NULL,
[Model] [varbinary](max) NOT NULL,
[ProductVersion] [nvarchar](32) NOT NULL,
CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY ([MigrationId])
)
BEGIN TRY
EXEC sp_MS_marksystemobject ‘dbo.__MigrationHistory’
END TRY
BEGIN CATCH
END CATCH
[Inserting migration history record]
Running Seed method.
That’s it! You can now build and run your project, and login with the username of ‘test’ and password of ‘password’
You won’t be able to register a user just yet though, because You’ll bneed to modify the register functions to include the additional fields we defined in our account model.
9. Modifying the registration pages
Firstly, modify the register model in /Models/AccountModel.cs to add the first name and last name:
public class RegisterModel
{
[Required]
[Display(Name = "User name")]
public string UserName { get; set; }
[Required]
[Display(Name = "First name")]
[MaxLength(50, ErrorMessage = "First Name cannot be longer than 50 characters.")]
public string FirstName { get; set; }
[Required]
[Display(Name = "Last Name")]
[MaxLength(50, ErrorMessage = "Last Name cannot be longer than 50 characters.")]
public string LastName { get; set; }
[Required]
[StringLength(100, ErrorMessage = "The {0} must be at least {2} characters long.", MinimumLength = 6)]
[DataType(DataType.Password)]
[Display(Name = "Password")]
public string Password { get; set; }
[DataType(DataType.Password)]
[Display(Name = "Confirm password")]
[Compare("Password", ErrorMessage = "The password and confirmation password do not match.")]
public string ConfirmPassword { get; set; }
}
Now add the fields to the register view in /Views/Account/Register.cshtml
<legend>Registration Form</legend> <ol> <li> @Html.LabelFor(m => m.UserName) @Html.TextBoxFor(m => m.UserName) </li> <li> @Html.LabelFor(m => m.FirstName) @Html.TextBoxFor(m => m.FirstName) </li> <li> @Html.LabelFor(m => m.LastName) @Html.TextBoxFor(m => m.LastName) </li> <li> @Html.LabelFor(m => m.Password) @Html.PasswordFor(m => m.Password) </li> <li> @Html.LabelFor(m => m.ConfirmPassword) @Html.PasswordFor(m => m.ConfirmPassword) </li> </ol>
Finally, modify the Register action in /Controllers/AccountController.cs:
// Attempt to register the user
try
{
WebSecurity.CreateUserAndAccount(model.UserName,
model.Password,
new {
FirstName = model.FirstName,
LastName = model.LastName
},
false);
WebSecurity.Login(model.UserName, model.Password);
return RedirectToAction("Index", "Home");
}
catch (MembershipCreateUserException e)
{
ModelState.AddModelError("", ErrorCodeToString(e.StatusCode));
}
Now you should be able to register new accounts.
One final note, if you are using SQL LocalDb, don’t ever delete the physical .mdf and .log files for your database without going through the SQL Server Object Explorer in Visual Studio or in SQL Server Management Studio. If you delete the files only, you end up with an error like the following in a web application:
Cannot attach the file ‘…\App_Data\DepartmentDb.mdf’ as database ‘DepartmentDb’.
Or the following error in a desktop app:
SqlException: Cannot open database “DepartmentDb” requested by the login. The login failed.
In this case the database is still registered in LocalDb, so you’ll need to go in the Object Explorer and also delete the database here before SQL Server will recreate the files.
Hope this helps!
