SimpleMembership, Membership Providers, Universal Providers and the new ASP.NET 4.5 Web Forms and ASP.NET MVC 4 templates

The ASP.NET MVC 4 Internet template adds some new, very useful features which are built on top of SimpleMembership. These changes add some great features, like a much simpler and extensible membership API and support for OAuth. However, the new account management features require SimpleMembership and won't work against existing ASP.NET Membership Providers. I'll start with a summary of top things you need to know, then dig into a lot more detail.
Summary:

• SimpleMembership has been designed as a replacement for the previous ASP.NET Role and Membership provider system
• SimpleMembership solves common problems developers ran into with the Membership provider system and was designed for modern user / membership / storage needs
• SimpleMembership integrates with the previous membership system, but you can't use a MembershipProvider with SimpleMembership
• The new ASP.NET MVC 4 Internet application template AccountController requires SimpleMembership and is not compatible with previous MembershipProviders
• You can continue to use existing ASP.NET Role and Membership providers in ASP.NET 4.5 and ASP.NET MVC 4 - just not with the ASP.NET MVC 4 AccountController
• The existing ASP.NET Role and Membership provider system remains supported, as it is part of the ASP.NET core
• ASP.NET 4.5 Web Forms does not use SimpleMembership; it implements OAuth on top of ASP.NET Membership
• The ASP.NET Web Site Administration Tool (WSAT) is not compatible with SimpleMembership

The following is the result of a few conversations with Erik Porter (PM for ASP.NET MVC) to make sure I had some the overall details straight, combined with a lot of time digging around in ILSpy and Visual Studio's assembly browsing tools.

SimpleMembership: The future of membership for ASP.NET

The ASP.NET Membership system was introduced with ASP.NET 2.0 back in 2005. It was designed to solve common site membership requirements at the time, which generally involved username / password based registration and profile storage in SQL Server. It was designed with a few extensibility mechanisms - notably a provider system (which allowed you override some specifics like backing storage) and the ability to store additional profile information (although the additional  profile information was packed into a single column which usually required access through the API). While it's sometimes frustrating to work with, it's held up for seven years - probably since it handles the main use case (username / password based membership in a SQL Server database) smoothly and can be adapted to most other needs (again, often frustrating, but it can work).
The ASP.NET Web Pages and WebMatrix efforts allowed the team an opportunity to take a new look at a lot of things - e.g. the Razor syntax started with ASP.NET Web Pages, not ASP.NET MVC. The ASP.NET Web Pages team designed SimpleMembership to (wait for it) simplify the task of dealing with membership. As Matthew Osborn said in his post Using SimpleMembership With ASP.NET WebPages:

With the introduction of ASP.NET WebPages and the WebMatrix stack our team has really be focusing on making things simpler for the developer. Based on a lot of customer feedback one of the areas that we wanted to improve was the built in security in ASP.NET. So with this release we took that time to create a new built in (and default for ASP.NET WebPages) security provider. I say provider because the new stuff is still built on the existing ASP.NET framework. So what do we call this new hotness that we have created? Well, none other than SimpleMembership. SimpleMembership is an umbrella term for both SimpleMembership and SimpleRoles.

Part of simplifying membership involved fixing some common problems with ASP.NET Membership.

Problems with ASP.NET Membership

ASP.NET Membership was very obviously designed around a set of assumptions:

• Users and user information would most likely be stored in a full SQL Server database or in Active Directory
• User and profile information would be optimized around a set of common attributes (UserName, Password, IsApproved, CreationDate, Comment, Role membership...) and other user profile information would be accessed through a profile provider

Some problems fall out of these assumptions.

Requires Full SQL Server for default cases

The default, and most fully featured providers ASP.NET Membership providers (SQL Membership Provider, SQL Role Provider, SQL Profile Provider) require full SQL Server. They depend on stored procedure support, and they rely on SQL Server cache dependencies, they depend on agents for clean up and maintenance. So the main SQL Server based providers don't work well on SQL Server CE, won't work out of the box on SQL Azure, etc.
Note: Cory Fowler recently let me know about these Updated ASP.net scripts for use with Microsoft SQL Azure which do support membership, personalization, profile, and roles. But the fact that we need a support page with a set of separate SQL scripts underscores the underlying problem.
Aha, you say! Jon's forgetting the Universal Providers, a.k.a. System.Web.Providers! Hold on a bit, we'll get to those...

Custom Membership Providers have to work with a SQL-Server-centric API

If you want to work with another database or other membership storage system, you need to to inherit from the provider base classes and override a bunch of methods which are tightly focused on storing a MembershipUser in a relational database. It can be done (and you can often find pretty good ones that have already been written), but it's a good amount of work and often leaves you with ugly code that has a bunch of System.NotImplementedException fun since there are a lot of methods that just don't apply.

Designed around a specific view of users, roles and profiles

The existing providers are focused on traditional membership - a user has a username and a password, some specific roles on the site (e.g. administrator, premium user), and may have some additional "nice to have" optional information that can be accessed via an API in your application.
This doesn't fit well with some modern usage patterns:

• In OAuth and OpenID, the user doesn't have a password
• Often these kinds of scenarios map better to user claims or rights instead of monolithic user roles
• For many sites, profile or other non-traditional information is very important and needs to come from somewhere other than an API call that maps to a database blob

What would work a lot better here is a system in which you were able to define your users, rights, and other attributes however you wanted and the membership system worked with your model - not the other way around.

Requires specific schema, overflow in blob columns

I've already mentioned this a few times, but it bears calling out separately - ASP.NET Membership focuses on SQL Server storage, and that storage is based on a very specific database schema.

Update: This schema has been improved a lot with Universal Providers. The views and stored procedures have been removed, and the tables are simplified.

Still, the main issues are unchanged: you're not in control of the schema, and any profile data is stored in property value blobs in the Profiles table:

SimpleMembership as a better membership system

As you might have guessed, SimpleMembership was designed to address the above problems.

Works with your Schema

As Matthew Osborn explains in his Using SimpleMembership With ASP.NET WebPages post, SimpleMembership is designed to integrate with your database schema:

All SimpleMembership requires is that there are two columns on your users table so that we can hook up to it – an “ID” column and a “username” column. The important part here is that they can be named whatever you want. For instance username doesn't have to be an alias it could be an email column you just have to tell SimpleMembership to treat that as the “username” used to log in.

Matthew's example shows using a very simple user table named Users (it could be named anything) with a UserID and Username column, then a bunch of other columns he wanted in his app.

Then we point SimpleMemberhip at that table with a one-liner:

WebSecurity.InitializeDatabaseFile("SecurityDemo.sdf", "Users", "UserID", "Username", true);

No other tables are needed, the table can be named anything we want, and can have pretty much any schema we want as long as we've got an ID and something that we can map to a username.

Broaden database support to the whole SQL Server family

While SimpleMembership is not database agnostic, it works across the SQL Server family. It continues to support full SQL Server, but it also works with SQL Azure, SQL Server CE, SQL Server Express, and LocalDB. Everything's implemented as SQL calls rather than requiring stored procedures, views, agents, and change notifications.
Note that SimpleMembership still requires some flavor of SQL Server - it won't work with MySQL, NoSQL databases, etc. You can take a look at the code in WebMatrix.WebData.dll using a tool like ILSpy if you'd like to see why - there are places where SQL Server specific SQL statements are being executed, especially when creating and initializing tables. It seems like you might be able to work with another database if you created the tables separately, but I haven't tried it and it's not supported at this point.
Note: I'm thinking it would be possible for SimpleMembership (or something compatible) to run Entity Framework so it would work with any database EF supports. That seems useful to me - thoughts?
Note: SimpleMembership has the same database support - anything in the SQL Server family - that Universal Providers brings to the ASP.NET Membership system.
UPDATE: Newer updates of Universal Providers - I believe starting with the 1.2 release on 8/16 - are now really database agnostic, so they'll work on any database that has an Entity Framework provider.

Easy to with Entity Framework Code First

The problem with with ASP.NET Membership's system for storing additional account information is that it's the gate keeper. That means you're stuck with its schema and accessing profile information through its API.
SimpleMembership flips that around by allowing you to use any table as a user store. That means you're in control of the user profile information, and you can access it however you'd like - it's just data. Let's look at a practical based on the AccountModel.cs class in an ASP.NET MVC 4 Internet project. Here I'm adding a Birthday property to the UserProfile class.

[Table("UserProfile")]
public class UserProfile
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int      UserId { get; set; }
    public string   UserName { get; set; }
    public DateTime Birthday { get; set; }
}

Now if I want to access that information, I can just grab the account by username and read the value.

var context = new UsersContext();
var username = User.Identity.Name;
var user = context.UserProfiles.SingleOrDefault(u => u.UserName == username);
var birthday = user.Birthday;

So instead of thinking of SimpleMembership as a big membership API, think of it as something that handles membership based on your user database. In SimpleMembership, everything's keyed off a user row in a table you define rather than a bunch of entries in membership tables that were out of your control.

How SimpleMembership integrates with ASP.NET Membership

Okay, enough sales pitch (and hopefully background) on why things have changed. How does this affect you? Let's start with a diagram to show the relationship (note: I've simplified by removing a few classes to show the important relationships):

So SimpleMembershipProvider is an implementaiton of an ExtendedMembershipProvider, which inherits from MembershipProvider and adds some other account / OAuth related things. Here's what ExtendedMembershipProvider adds to MembershipProvider:

The important thing to take away here is that a SimpleMembershipProvider is a MembershipProvider, but a MembershipProvider is not a SimpleMembershipProvider.
This distinction is important in practice: you cannot use an existing MembershipProvider (including the Universal Providers found in System.Web.Providers) with an API that requires a SimpleMembershipProvider, including any of the calls in WebMatrix.WebData.WebSecurity or Microsoft.Web.WebPages.OAuth.OAuthWebSecurity.
However, that's as far as it goes. Membership Providers still work if you're accessing them through the standard Membership API, and all of the core stuff  - including the AuthorizeAttribute, role enforcement, etc. - will work just fine and without any change.
Let's look at how that affects you in terms of the new templates.

Membership in the ASP.NET MVC 4 project templates

ASP.NET MVC 4 offers six Project Templates:

• Empty - Really empty, just the assemblies, folder structure and a tiny bit of basic configuration.
• Basic - Like Empty, but with a bit of UI preconfigured (css / images / bundling).
• Internet - This has both a Home and Account controller and associated views. The Account Controller supports registration and login via either local accounts and via OAuth / OpenID providers.
• Intranet - Like the Internet template, but it's preconfigured for Windows Authentication.
• Mobile - This is preconfigured using jQuery Mobile and is intended for mobile-only sites.
• Web API - This is preconfigured for a service backend built on ASP.NET Web API.

Out of these templates, only one (the Internet template) uses SimpleMembership.

ASP.NET MVC 4 Basic template

The Basic template has configuration in place to use ASP.NET Membership with the Universal Providers. You can see that configuration in the ASP.NET MVC 4 Basic template's web.config:

This means that it's business as usual for the Basic template as far as ASP.NET Membership works.

ASP.NET MVC 4 Internet template

The Internet template has a few things set up to bootstrap SimpleMembership:

• \Models\AccountModels.cs defines a basic user account and includes data annotations to define keys and such
• \Filters\InitializeSimpleMembershipAttribute.cs creates the membership database using the above model, then calls WebSecurity.InitializeDatabaseConnection which verifies that the underlying tables are in place and marks initialization as complete (for the application's lifetime)
• \Controllers\AccountController.cs makes heavy use of OAuthWebSecurity (for OAuth account registration / login / management) and WebSecurity. WebSecurity provides account management services for ASP.NET MVC (and Web Pages)

WebSecurity can work with any ExtendedMembershipProvider. There's one in the box (SimpleMembershipProvider) but you can write your own. Since a standard MembershipProvider is not an ExtendedMembershipProvider, WebSecurity will throw exceptions if the default membership provider is a MembershipProvider rather than an ExtendedMembershipProvider.
Practical example:

1. Create a new ASP.NET MVC 4 application using the Internet application template
2. Install the Microsoft ASP.NET Universal Providers for LocalDB NuGet package
3. Run the application, click on Register, add a username and password, and click submit

You'll get the following execption in AccountController.cs::Register: To call this method, the "Membership.Provider" property must be an instance of "ExtendedMembershipProvider".

This occurs because the ASP.NET Universal Providers packages include a web.config transform that will update your web.config to add the Universal Provider configuration I showed in the Basic template example above. When WebSecurity tries to use the configured ASP.NET Membership Provider, it checks if it can be cast to an ExtendedMembershipProvider before doing anything else.
So, what do you do?
Options:
If you want to use the new AccountController, you'll either need to use the SimpleMembershipProvider or another valid ExtendedMembershipProvider. This is pretty straightforward.
If you want to use an existing ASP.NET Membership Provider in ASP.NET MVC 4, you can't use the new AccountController. You can do a few things:

1. Replace  the AccountController.cs and AccountModels.cs in an ASP.NET MVC 4 Internet project with one from an ASP.NET MVC 3 application (you of course won't have OAuth support). Then, if you want, you can go through and remove other things that were built around SimpleMembership - the OAuth partial view, the NuGet packages (e.g. the DotNetOpenAuthAuth package, etc.)
2. Use an ASP.NET MVC 4 Internet application template and add in a Universal Providers NuGet package. Then copy in the AccountController and AccountModel classes.
3. Create an ASP.NET MVC 3 project and upgrade it to ASP.NET MVC 4 using the steps shown in the ASP.NET MVC 4 release notes.

None of these are particularly elegant or simple. Maybe we (or just me?) can do something to make this simpler - perhaps a NuGet package. However, this should be an edge case - hopefully the cases where you'd need to create a new ASP.NET but use legacy ASP.NET Membership Providers should be pretty rare. Please let me (or, preferably the team) know if that's an incorrect assumption.

Membership in the ASP.NET 4.5 project template

ASP.NET 4.5 Web Forms took a different approach which builds off ASP.NET Membership. Instead of using the WebMatrix security assemblies, Web Forms uses Microsoft.AspNet.Membership.OpenAuth assembly. I'm no expert on this, but from a bit of time in ILSpy and Visual Studio's (very pretty) dependency graphs, this uses a Membership Adapter to save OAuth data into an EF managed database while still running on top of ASP.NET Membership.

Note: There may be a way to use this in ASP.NET MVC 4, although it would probably take some plumbing work to hook it up.

How does this fit in with Universal Providers (System.Web.Providers)?

Just to summarize:

• Universal Providers are intended for cases where you have an existing ASP.NET Membership Provider and you want to use it with another SQL Server database backend (other than SQL Server). It doesn't require agents to handle expired session cleanup and other background tasks, it piggybacks these tasks on other calls.
• Universal Providers are not really, strictly speaking, universal - at least to my way of thinking. They only work with databases in the SQL Server family.
• Universal Providers do not work with Simple Membership.
• The Universal Providers packages include some web config transforms which you would normally want when you're using them.

What about the Web Site Administration Tool?

Visual Studio includes tooling to launch the Web Site Administration Tool (WSAT) to configure users and roles in your application.

WSAT is built to work with ASP.NET Membership, and is not compatible with Simple Membership. There are two main options there:

1. Use the WebSecurity and OAuthWebSecurity API to manage the users and roles
2. Create a web admin using the above APIs
3. Since SimpleMembership runs on top of your database, you can update your users as you would any other data - via EF or even in direct database edits (in development, of course)

Creating an Easy Data Access Layer using Entity Framework

Data Access Layers

The purpose of a Data Access Layer is to simplify access to data stored in a persistent storage separate from your application.

In the past, creating a functional and easy-to-use Data Access Layer (DAL) in your application could be a tedious and long-winded process. Following this tutorial, you will learn how to easily generate a DAL from a database with minimal effort. You will learn how to automatically generate classes that map your database entities, their relationships, and even the stored procedures to .NET objects and methods. You’ll also discover how to update your DAL when the database has been changed. Finally, you’ll learn how to extend your automatically created classes to really customise your DAL.

Pre-Requisites

The following are required for this tutorial:
• SQL Server
• SQL Server Management Studio
• Some flavour of Microsoft Visual Studio
• The Entity Framework DLL (I use http://www.microsoft...s.aspx?id=8363)

The Database

Creating the Instance

I’m going to start by creating a SQL Server database for our application. It’s possible to use many database types, but that is beyond the scope of this tutorial. Note that if you’re using Visual Studio Express you might need to create a class library using Visual Web Developer to connect to anything other than SQLCE databases (including SQL Server). We’re going to start really simple by creating a database that just tracks Users, Products and Orders.

Firstly, from SQL Server Management Studio, click the “Connect” drop-down and select “Database Engine”. You can also use “File” -> “Connect Object Explorer” and use the drop-down to select “Database Engine”. You’ll see the “Connect to Server” dialog as in the screenshot below.

Spoiler

Use this to connect to your SQL Server instance as usual. You should then be able to view your available databases in the Object Explorer as in the below screenshot. I haven’t got any at the moment, but that’s about to change.

Spoiler

Right-click on the “Databases” folder and click “New Database”, after which the “New Database” dialog should then appear as below.

Spoiler

Set your database name. I’m going to call mine “EntityTutorial”. Now click OK, and the database will be created. You should be able to see it sitting in your Object Explorer.

Creating the Tables

To start with, our application database is going to be very simple. We’re going to have 3 tables; “Users”, “Products”, and “Orders”. Expand your database, and right-click the “Tables” folder. Next, select “New Table” and you will then be able to design your table. Using the “Properties” window, name the table “Users”. We need to also add the columns “UserID” (an “int” type that does not allow nulls), “UserName” (nvarchar(5), not nullable), “FirstName” and “LastName” (nvarchar(5), nullable), and “EmailAddress”(nvarchar(MAX), not nullable). Before we finalise this table, we need to make the UserID field start from 1 and auto-increment itself. That means the IDs for this table will be assigned on creation, and we don’t need to worry about them. Click on the “UserID” column, and you will see the “Column Properties” section of the window has its details. In the “Identity Specification” section, set “(Is Identity)” to “Yes”, and ensure the fields “Identity Increment” and “Identity Seed” fill with a 1. Save and close this table design.

Do the same to create two more tables. “Products” will need columns for “ProductID” (int, not nullable, identity), “ProductName” (nvarchar(100), not null), “Description” (nvarchar(MAX), null), “Price”(float, not null), “AddedBy” (int, not null), and “AddedOn”(datetime, not null). The “Orders” table needs columns “OrderID” (int, not null, identity), “Product” (int, not null), “OrderedBy” (int, not null), “OrderedOn” (datetime, not null), and “Quantity” (int, not null).

Spoiler

All three tables should now be showing in object explorer, as in the screenshot above. However, if you examine them, you’ll notice there are no Primary Keys on any table. We want the ID field (first column) from each table to be the Primary Key, so we need to add them. Right-click a column on the “Users” table, select “Modify” and you’re taken back to the designer. Then, right-click the column selector to the left of the “UserID” column and select “Set Primary Key”; there should now be a yellow key next to it. Do the same for the “Products” and “Orders” tables, setting the “ProductID” and “OrderID” columns as Primary Keys.

You might have been sharp enough to pick up on the fact that we’ve created some columns that will connect to another column in another table. We’ll set these as Foreign Keys now. Right-click the “Keys” folder for the “Products” table, and select “New Foreign Key…” You’ll see a “Foreign Key Relationships” dialog like the screenshot below.

Spoiler

Click the ellipsis (“…”) button and you’ll see a “Tables and Columns” dialog. Change the “Primary key table” to “Users”, set the left column to “UserID” and the right to “AddedBy”. That means that when a Product gets added, it will need to reference the UserID of the user who added it. Click “OK”, then “Close”.

Spoiler

Do the same linking the “Product” field on the “Orders” table to the “ProductID” field on the “Products” table, and the “OrderedBy” field on the “Orders” table to the “UserID” field on the “Users” table. You will have to add a second relationship in the “Tables and Columns” dialog or you will overwrite your previous relationship. Our database is complete, for now.

The Data Access Layer

Connecting to the Database

So, we have a simple database for our application. Now, we need an application for our simple database. Using Visual Studio, let’s create a new Console project named “EntityTutorial”. The first thing I’m going to do is add another project to this solution: a Class Library called “DataAccessLayer”. I’m also going to delete the “Class1” file generated with that library.

First, we need an EDMX: this is a file that defines the mapping to the database. These are really easy to generate in Visual Studio. Right-click your “DataAccessLayer” project and select “Add”, then “New Item…” An “Add New Item” dialog will appear. Select “ADO.NET Entity Data Model” and name it “TutorialModel.edmx”.

Spoiler

When you click “Add”, you’ll be taken to the “Entity Data Model Wizard”. First, select “Generate From Database” and click “Next”. Then, you’ll need to create a new connection to your database: Select “New Connection”, ensure your Data Source is “Microsoft SQL Server (SqlClient)” and select the “Server name” and “Database name” fields. Note as per my previous warning, if using Visual Studio Express you may have to use Visual Web Developer Express to create this connection.

Spoiler

Click “Test Connection”, and hopefully it will succeed. Click “OK” and in the previous part of the Wizard, you’ll see a new connection string, as well as a checkbox asking if you want to save the connection string. Make sure you have, so you can substitute a different database location further down the line. Click “Next, and the Wizard will retrieve information from your database. Select all the Tables and click “Finish”.

Spoiler

Once the EDMX has been created, you’ll see it looks similar to the database we made, including the relationships (as indicated by the dotted lines). Great! We have a connection from our application to the database.

Generate the Entity Classes

Next, we want to generate the classes we will use in our application that will be modelled on our database. We will also generate a “DbContext” class that will give us read/write access to the database so we can get and manipulate these objects from storage, as well as save any state changes.

To begin with, you’ll need to get the “Entity Framework 4.x [or 5.x] DbContext Generator” component from the Online Gallery in Extension Manager. Then, right-click your EDMX and select “Add Code Generation item…” Select “EF [ver] DbContext Generator” and set the name as “TutorialModel.tt” and click “Add”.

Seconds later, this should have finished. You’ll have two new items in the Solution Explorer: “TutorialModel.Context.tt” and “TutorialModel.tt”. If you expand the “TutorialModel.Context.tt” you should also see “TutorialModel.Context.cs”.

Below the “TutorialModel.tt” you have “Order.cs”, “Product.cs”, “TutorialModel.cs” and “User.cs”.

Open the User.cs class; you’ll see it has properties to match each database field, and an ICollection to represent the relationships. Also check out the “TutorialModel.Context.cs” class; this inherits from DbContext, giving us access to the underlying database. However, this project will need to reference Entity Framework; add a reference to the project and navigate to where the DLL is saved on your machine.

A quick note: NEVER make changes to these classes. They’re generated automatically, and can be overwritten when regenerated. Later, we’ll learn how to extend these classes.

Using the Classes

Creating a New User

At some point we’re going to need to use what we’ve made here, so let’s add a reference in our EntityTutorial console application to our DataAccessLayer project. You’ll also need to add a reference to Entity Framework. Now, in the Program.cs class file, add a method with the following signature:

1	private static bool AddUser(string userName, string firstName, string lastName, string emailAddress)

This helper method will allow us to easily create a user and save it in the database. It will also return true if the save was successful.
We need to create a context. The good news is, the DbContext class implements the IDisposable interface, so we can wrap it in a using statement, like so:

1	using (var dbContext = new EntityTutorialEntities())

That forces the context to be disposed of implicitly when out of scope. Within curly braces, we then need to create an object of type User (I’m going to use an object initializer to save effort):

1	var user = new User

2

{

3	UserName = userName,

4	FirstName = firstName,

5	LastName = lastName,

6	EmailAddress = emailAddress

7

};

Now we need to add this User object to the database context’s Users collection.

1	dbContext.Users.Add(user);

The database context class has methods we can invoke as well as the collections of objects representing the tables. The SaveChanges() method will, unsurprisingly, save changes to the database, and its return value is the number of changes saved.

1	var changesSaved = dbContext.SaveChanges();

Finally, we will return true if the changesSaved variable has a value of greater than or equal to 1.

1	return changesSaved >= 1;

The whole method looks like this:

Spoiler

Let’s test that works. Add this line to the Main method in your console app’s Program.cs:

1	var addedUser = AddUser("MrShoes", "Mister", "Shoes", "mrshoes@shoefits.com");

Hopefully, you understand what that line does: it creates a user with the user name “MrShoes”, first name “Mister”, last name “Shoes”, email address “mrshoes@shoefits.com”, and the variable addedUser holds a Boolean value indicating whether the changes were saved or not. We probably want to output something to the screen.

1	Console.WriteLine(addedUser ? "A user was added." : "No user could be added.");

2	Console.ReadKey();

Run the console application and let’s see the result.

Spoiler

Oh no! An unhandled exception! The message for this exception is: "No connection string named 'EntityTutorialEntities' could be found in the application config file." Yes, we do have an app.config with that information in the class library; we just need to copy the file into the console project. You can easily do that in VS by right-clicking the App.config file and selecting “Copy”, then right-click the console project and select “Paste”. Run it again and you should see:

Spoiler

Seems like it’s been successful. Let’s check the Users table in the database.

Spoiler

You’ll see a new entry in this table matching what we added in code; even better, the row has a UserID property that was automatically generated.

Getting a User from the Database

Obviously, we need to be able to read from the database as well as writing to it. We use the same DbContext object to access the database entities, which will automatically be mapped to the classes we’ve created.

If you wanted to get all the Users, for example, you’d use:

1	var allUsers = dbContext.Users;

The variable allUsers would then be of type DbSet and, in our current state, will include one single User object. If you wanted to get the users with an ID greater than 7, you’d use:

1	var usersAfterSeven = dbContext.Users.Where(u => u.UserID > 7);

In this case, the “usersAfterSeven” variable would be of type IQueryable and in our example hold no entries (since our only user has an ID of 1). The .Where() extension method is very useful when using Entity Framework, but can be fairly intimidating if you’re not familiar with using a lambda as a predicate. It’s beyond the scope of this tutorial, but I’m sure you’ll quickly pick up how to use them.

1	var firstUser = dbContext.Users.FirstOrDefault();

The above line will return a single User object, the first in the database, or null if there are no Users stored. What’s useful is that you can also supply a lambda expression as an argument to the .FirstOrDefault extension method. For example, if you want to get a User by its UserName property, you’d use:

1	return dbContext.Users.FirstOrDefault(u => u.UserName == 1);

That will return our User from the database. Let’s create another method in our console app to help us get Users by UserName.

Spoiler

Now let’s try this out. Change your Main method to match:

Spoiler

Run that, and you should get this output:

Spoiler

Creating a New Product

We’re going to create a method that will help us easily add Products to the database. This method will also take an object of type User as a parameter. Create a method with the following signature:

1	private static bool AddProduct(string productName, string description, double price, User addedBy)

We need to create the database context as before.

1	using (var dbContext = new EntityTutorialEntities())

Now, within curly braces, we use an object initializer to create a Product object.

1	var product = new Product

2

{

3	ProductName = productName,

4	Description = description,

5	Price = price,

6	AddedOn = DateTime.Now,

7	AddedBy = addedBy.UserID

8

};

You notice that for the AddedBy property, we’re accessing the UserID of the User object we’ve passed as a parameter. This will ensure we are creating the correct relationship between entities. We’re also using DateTime.Now to store as the AddedOn date. As before, we need to add the Product to the context’s Products collection.

1	dbContext.Products.Add(product);

The method should look like this, after we’ve finished it off the same way we did AddUser:

Spoiler

Change your Main method like so:

Spoiler

Run this, then check your database.

Spoiler

We now have a new Product, it has been assigned its own ProductID, and the AddedBy matches the User’s UserID field.

Getting a Product from the Database

Returning the Products is done the same as with Users. Four our purposes, we’ll use a helper method:

Spoiler

You might also want a method that lets you easily get the Products that were created by a User. Because the relationship exists in the database, Entity Framework will allow us to access them simply as a property of a User object.

Spoiler

Creating a New Order

By now, you won’t be surprised to hear that we’re going to make a helper method just like our others to create Orders.

Spoiler

We’ll add an order from our Main method.

Spoiler

Run this and check your Orders table in the database.

Spoiler

You can see it has been added, and the ProductID and OrderedBy field match the User and Product we created earlier.

Dealing with Changes

Often, especially when developing a new product, the database might change. Whether it’s new tables, new columns, or stored procedures, if you wish to use them in your application you’ll need to update the mapping in your EDMX. Fortunately, that’s just as easy as it was to generate the EDMX in the first place.

Adding a New Column

In SQL Server Management Studio, right-click your Users table and select “Design”. Add a new column “LastActive” of type datetime, and allow nulls. Close and save this table.

Spoiler

The column now exists in the table, but not our Entities nor our mapping. Return to the EDMX in Visual Studio, right-click and select “Update Model from Database…” You’ll then be shown the Update Wizard.

Spoiler

Click “Finish” and it will refresh the current tables. After a while, you’ll see the “LastActive” field has been added to your EDMX. Check your User.cs file and… oh, it hasn’t changed. Not yet, at least. Save your EDMX and check again.

Spoiler

OK, we now have the LastActive property. It’s important to remember that the code generation tool won’t be re-run until the EDMX is saved.

Adding a New Table

Sometimes an entirely new table has been added to the database, and you’ll need to start using it in your application. This is just as easy as adding a column.
To start with, in SQL Server Management Studio, we’ll create a new table called “News”, with the columns ItemID(int, not null, identity), Date(datetime, not null), Heading (varchar(200), not null), Body (text, not null), and AddedBy(int, not null). Set ItemID as the Primary Key, and create a relationship linking AddedBy to the UserID field in the Users table.

We’ll need to map this in our EDMX. Go back to Visual Studio, right-click the EDMX and select “Update Model from Database…” This time, you’ll be able to select the “News” table to add, so do that and select “Finish”.

Spoiler

The “News” table has now been mapped to entities, and the relationship has been created. Save, and you should now have a News.cs class file.

Spoiler

You should also see a new collection named “News” in the User class.

Spoiler

Adding a Stored Procedure

Sometimes, the database will have stored procedures that you’ll need to call. This is also possible using the mapping of Entity Framework, and it makes calling a stored procedure as simple as invoking a method.

In SQL Server Management Studio, navigate to “Programmability” -> “Stored Procedures” in your database. Right-click and select “New Stored Procedure”. Replace the template text with the following:

Spoiler

Run this and the procedure will be created. You will be able to see it in the Stored Procedures folder. Now, let’s add it to our mapping. Again, go to Visual Studio, right-click your EDMX and select “Update Model from Database…” Now you will be able to select the “DeleteUser” stored procedure; do so, then click “Finish”.

This time, you won’t see anything visually added to your model. That makes sense, since we’ve only added a stored procedure and not an object. We need to use “Function Import” to add this to our context class. Go to the “Model Browser” window in VS (if you can’t view it, right-click on the EDMX and select “Model Browser”) and, there in the Stored Procedures folder you should see our “DeleteUser” procedure. Right-click it and select “Add Function Import”.

Spoiler

The “Add Function Import window will now appear. We don’t need to change anything here, we’re not returning anything and we don’t need to change the name. Click “OK”, then save your EDMX. The EntityTutorialEntities class should now look like this:

Spoiler

Ok, we can see that all the Entities we’ve created are collected here, and we now have a DeleteUser method. How do we call it?

1	dbContext.DeleteUser("MrShoes");

Extending the Classes

I mentioned earlier that you should never change the automatically generated classes. That’s because any update will rerun the code generator, and will therefore overwrite any changes you make. However, the people who made the code generator knew you might need to extend the classes, and so they made them partial classes.

To extend the classes, then, you only need to create a new class with the same name in the same namespace, and also make it partial. That way, you can access all the properties and methods of the generated classes and create your own.

Create a new UserExtend.cs file in your DataAccessLayer project, so we can extend the User entity. Add the keyword to the class definition, and rename it “User” from “UserExtend”.

Let’s give it a property that returns the number of Orders placed by the User.

1	public int NumberOfOrders { get { return Orders.Count; } }

Now you see we can access the Orders collection from this extended class, even though it is defined in a separate file. They are the same class defined in multiple files.

Maybe you want to use this class as an IIdentity so your application can keep track of which user is logged in. Change the class signature:

1	public partial class User : IIdentity

Remembering to add the statement:

1	using System.Security.Principal;

And implement the interface members:

1	public string Name{ get { return UserName; } }

2	public string AuthenticationType { get { return "Standard"; } }

3	public bool IsAuthenticated { get { return true; } }

Now our generated class implements a useful interface!

Summary

By now you’ll see just how easy it can be to automatically generate a DAL from a database with automatically generated code. Obviously, it’s possible to create much more complex data layers than the one shown, but this should server as a good reference for a starting point.