DPS907 notes – Tue Sep 10

Persistent store (SQL Server). Entity Framework. Code first. Code first migrations. View model classes.


Using a persistent store in an ASP.NET Web API app

Last week, we used the server’s memory to store data for our web service.

Today, you will learn to use a persistent store.

The best match for a web service built on ASP.NET Web API is SQL Server. The ASP.NET runtime engine enables an app to use a “local” database, or a conventional database server located somewhere in the IT infrastructure.

For the few weeks, we will use a “local” database. A local database appears as an .mdf file, in the project’s App_Data folder. When your app is active, the ASP.NET runtime engine starts a database engine to manage the interaction with the database. If we configure the pieces correctly, then all of this is done auto-magically.

Later in the course, we will use a conventional database server to store our data. More on that later.


Guide to today’s topics

We will take advantage of the knowledge and experience that you gained last week. Recall that you learned a recommended approach to building an ASP.NET Web API web service, by creating data classes, a data initializer, and a data service or manager class. We’ll modify that approach, to accommodate the persistent store.

The table below compares last week’s approach – the ‘In-memory store’ – with today’s new approach, ‘Persistent store (RDBMS)’.

You can think of the following as how-to-assemble list, or a recipe for an app.


In-memory store Persistent store (RDBMS)
AppDomainClasses AppDomainClasses
Connection string in Web.config
DataContext class in the Models folder
StoreInitializer StoreInitializer (but the code’s a bit different)
Code first data migrations (which we’ll add soon)
Execute the StoreInitializer when the app starts Execute the StoreInitializer when the app starts (but the code’s a bit different)
DataService DataService (not present in the first few examples, but we’ll see it again later)
View model classes (aka DTO)
AutoMapper to map between AppDomainClasses and view model classes
Controllers Controllers


Entity Framework technologies

Modern ASP.NET web apps and web services use Entity Framework (EF) technologies for data persistence.

Almost all students in this (Fall 2013) course have learned something about the EF technologies. Here’s a list of links to overviews and information about EF technologies.

Entity Framework Overview

Entity Data Model (introduction, overview)

Entity Data Model Key Concepts

entity type

association type

property, and navigation property

Local Data Overview


There are two specific EF technologies that we will use extensively:

Code first (now)

Code first migrations (a bit later in the course)


The bottom line is that the process we started using – creating our app domain data model classes in the models folder (in a source code file named AppDomainClasses.cs) – is the process that we will continue to follow.


Other technologies which may be new to you

In addition to EF, you will learn and use new ways to build your web service. These include:

  • Repositories – per-entity classes that manage data service operations
  • View models – also known as ‘data transfer objects’ – to customize the shape of the app’s data
  • Convention-based coding, including mapping classes


Step-by-step approach

The following sections take you through the items in the right side of the table above.


AppDomainClasses for your app’s entities

No changes here – it works the same way. However, there are a few ideas to remember.

When you write a data class that includes a collection property, you must write a default constructor. In the constructor, you must initialize the collection property. While you’re in the constructor, if you wish, you can create default values for other properties.

Looking at the other side of the collection property, when you write a class that’s associated with another class, add a property for that class.

Later, you will use data annotations to improve your app domain data model.


Connection string in Web.config

If you intend to use a persistent store (i.e. a relational database management system), your app needs a connection string.

A connection string defines:

  1. the database platform provider name,
  2. the server’s name and address,
  3. the store/database name, and
  4. access credentials.

In your app, it is stored in the Web.config source code file.

As noted earlier, we will use the per-instance LocalDB engine, and a store file that’s located in the App_Data folder. Here’s an example of a connection string (click to see a larger image):

Connection string


To create a new connection string, a simple method is to copy then paste the existing connection string, and then edit the new one.

There are a few important parts of the connection string. The value of the “name” attribute is very important. We will use it in our app’s code. In the image below, it is highlighted by the red-bordered rectangle.

Next, in the “connectionString” attribute, the value of the “AttachDbFilename” item is important. It tells you where the store file will be located. The ” |DataDirectory| ” string is mapped to the “App_Data” folder. In the image below, it is highlighted by the green-bordered rectangle.

Finally, the “Initial Catalog” item is important. It has the actual name of the database. We do not use it in our code, but you may use it if you use one of the management tools. In the image below, it is highlighted by the grey-coloured rectangle.

Connection string



DataContext class in the Models folder

A “data context” is a class that functions as the gateway to the database platform.

Create the data context class in the Models folder. It is a simple class, that inherits from DbContext, and has two notable features:

  • Its constructor uses the value of the connection string’s “name” attribute (e.g. DataContext)
  • It includes a DbSet<TEntity> property for every entity


DataContext class


StoreInitializer class (edited)

The purpose of the store initializer remains the same – add initial data to your data store.

Its code is just a bit different:

  • It inherits from a “database initializer” (specifically,DropCreateDatabaseIfModelChanges<TContext>). As a result, it has more capabilities than last week’s store initializer.
  • It does not have a constructor. Instead, you implement the Seed method, which effectively has the same purpose as the constructor in your past examples.

Study the store initializer code below. You must specifically do the following:

  1. Add the System.Data.Entity namespace directive
  2. Make sure that your class inherits from DropCreateDatabaseIfModelChanges<TContext>
  3. Implement the (void) Seed method (with the “protected override” modifiers)
  4. When creating a new entity object, configure its properties as you normally would
  5. Then, add it to the appropriate data context entity set/collection
  6. Finally, when appropriate, call context.SaveChanges()


Store Initializer


Execute the StoreInitializer when the app starts

As in the previous example, you must run/execute the store initializer, by adding code to the Global.asax.cs source code file, in its Application_Start method. The code is a bit different:

System.Data.Entity.Database.SetInitializer(new Models.StoreInitializer());


Write a DataService class

The purpose of the manager remains the same – provide access to your app’s data and data-related functionality.

Its code is just a bit different:

  • It declares and initializes a reference to the app’s data context.
  • Its operations work on TEntity objects and collections.



Introduction to “view model” classes

We need to talk about view model classes. They take on the exact shape of the data you wish to work with in a view (as defined in the model-view-controller architecture).

This is an important topic. Here’s the questions that we’ll ask and answer:

  1. What are view model classes? Why have them?
  2. How to create view model classes
  3. Redesign the “DataService” class to become “repository” classes
  4. Patterns for using the new view model classes and repositories
  5. What is AutoMapper? What problem does it solve?
  6. Configuring maps and using them in repositories


What are view model classes? Why have them?

As noted above, a view model class takes on the exact shape of the data you wish to work with in a view.

The properties in a view model class are based on the properties in an app domain model class.

Our goal is to prevent app domain model classes from being visible and accessible in controllers and views. Instead, in controllers and views, we will use view model classes. This implies that we must map data between app domain model classes, and view model classes.

This will help to implement the “separation of concerns” principle.


How to create view model classes

Create a new folder, named “ViewModels”.

Create a new source code file for a class. We suggest that you create a separate source code file to hold the view model classes needed for each controller. For example, assume that you have a “Suppliers and Products” application domain. Therefore, you should create a source code file named “VM_Supplier.cs”, and another named “VM_Product.cs”.

What classes do you write? The answer is dependent upon your app’s use cases. However, there are some common and obvious use case patterns. Follow these suggestions as you write view model classes for your first few apps.

As just stated above, assume a “Suppliers and Products” application domain. A supplier can have zero or more products, and a product is always linked to one supplier.

Notice the use of the entity’s name in the classes. Notice also the inheritance chain.

List – a class that can be used in a simple lookup list

  • public class ProductList
  • For all kinds of users
  • Include the Id property, and one or two descriptive properties

Public – a class that can be used by public users

  • public class ProductPublic
  • For all kinds of users
  • Can be used for add-new tasks – therefore, it could also be named “Add“, e.g. ProductAdd
  • Therefore, include properties that must be present in an add-new task
  • Do NOT include the Id property

Base – for get-some and get-one tasks

  • public class ProductBase : ProductPublic
  • For public or trusted users
  • Adds the Id property to the ‘…Public’ class (and any other useful/required properties)

Edit – for update-item tasks

  • public class ProductEdit : ProductBase
  • For partially-trusted users
  • Includes all (most) properties that you allow to be edited

Full – to deliver all (most) properties of an object

  • public class ProductFull : ProductEdit
  • For trusted users
  • Includes all (most) properties that you allow to be viewed or edited

You do not have to write all these classes.

Conversely, you may need more classes to handle some use cases. Don’t be afraid of that.







  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: