In this tutorial I show you how easily you can create a simple ASP.NET MVC web application with Entity Framework and N-Tier architecture. The application will a simple movie inventory for home usage. You can add movies to the database with some parameters, and you can add movies to your inventory by selecting the format (DVD, Blu-ray, etc…). It will have a small admin part, where master data can be edited.

First of all you should know some basics of N-Tier, Entity Framework, MVC, ASP.NET and of course C#. If you would like to have a nice looking site then HTML and CSS is also needed.

Basicly I separated this application into 4 layers (as per N-Tier):

• Data Transfer Object (DTO)
• Data Access Layer (DAL)
• Business Logic Layer (BLL)
• User Interface (UI)

In this chapter I show you how to create the first layer: DTO

I chose the “Code first from database” Entity model, because I like designing and creating the database first in SQL Management Studio. It is much easier to create table relations in Management Studio diagram creator.

I created 4 tables. With the following SQL scripts you can create them easily in SQL Management Studio.

Category table:

CREATE TABLE [dbo].[Category]([Id] [int] IDENTITY(1,1) NOT NULL,
[CategoryType] [nvarchar](50) NOT NULL,
[LastModified] [datetime] NOT NULL,
[Visible] [bit] NOT NULL CONSTRAINT [DF_Category_Visible] DEFAULT ((1)),
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
([Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

Movie table:

CREATE TABLE [dbo].[Movie](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](50) NOT NULL,
[Year] [int] NOT NULL,
[CategoryId] [int] NOT NULL,
[ImdbRating] [int] NOT NULL,
[LastModified] [datetime] NOT NULL,
CONSTRAINT [PK_Movie] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Movie] WITH CHECK ADD CONSTRAINT [FK_Movie_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([Id])
GO

ALTER TABLE [dbo].[Movie] CHECK CONSTRAINT [FK_Movie_Category]
GO

Format table:

CREATE TABLE [dbo].[Format](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FormatType] [nvarchar](50) NOT NULL,
[LastModified] [datetime] NOT NULL,
[Visible] [bit] NOT NULL CONSTRAINT [DF_Format_Visible] DEFAULT ((1)),
CONSTRAINT [PK_Format] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Inventory table:

CREATE TABLE [dbo].[Inventory](
[Id] [int] IDENTITY(1,1) NOT NULL,
[MovieId] [int] NOT NULL,
[FormatId] [int] NOT NULL,
[LastModified] [datetime] NOT NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Inventory] WITH CHECK ADD CONSTRAINT [FK_Inventory_Format] FOREIGN KEY([FormatId])
REFERENCES [dbo].[Format] ([Id])
GO

ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_Inventory_Format]
GO

ALTER TABLE [dbo].[Inventory] WITH CHECK ADD CONSTRAINT [FK_Inventory_Movie] FOREIGN KEY([MovieId])
REFERENCES [dbo].[Movie] ([Id])
GO

ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [FK_Inventory_Movie]
GO

Creating these tables in this order I wrote is very important because of the relations.

You may recognize that all tables have Id property and 2 tables have Visible property. This is important because of the abstraction in the classes.

If you check the table relations in a diagram, you should see this:

Now create a new empty solution in Visual Studio and call it MovieInventory. Here we add all the projects (layers) one by one. Each project name starts with MovieInventory, then comes a dot (because of namespace hierarchy) and the layer name.

Let’s add the first project: MovieInventory.DTO
This should be a simple Class Library (.NET Framwork) project. After creating it, delete the default Class1.cs file and add a new ADO.NET Entity Data Model item, name it MovieInventoryContext. Select Code First from database option and save the connection string somewhere (notepad will be fine). You’ll need it in UI project. Unselect “save connection strings in app.config” and start importing all the 4 tables.
Now manually add 2 classes: BaseIdentity and VisibleIdentity. Both must be abstract public classes. BaseIdentity will have one [Key] property (Id) and LastModified property. VisibleIdentity should be inherited from BaseIdentity and will have only 1 property (Visible).

Open Movie and Inventory classes. Inherit them from BaseIdentity and remove Id and LastModified properties (they will get it from parent class).

Open Category and Format classes. Inherit them from VisibleIdentity and remove Id, LastModified and Visible properties.

If you have [System.Diagnostics.CodeAnalysis.SuppressMessage…] annotations, remove them, don’t need them.

Let’s see through those annotations. They are needed and some of them will be really useful. As you may think the [Table(“TableName”)] tells to Entity Framework (EF) that the following class is mapped to the “TableName”. You can rename you class to anything you like, EF will know which one is it, if this annotation exists.

Above the reference-type properties you will find [Required] annotation. This is for NOT NULL columns in SQL. Value type fields don’t need this, because if they are nullable, you can write for example int? or double?. [Display] annotation with Name property will be very useful. This name can be show on UI instead of the property name. For example FormatType property name would look wierd. We want to see only Format, that’s why I put it in Display Name.

The relation is set with those virtual properties:

It’s simple. Take a look at SQL tables: Category – Movie: 1…n connection. This means 1 category could belong to many movies (is it true, right? Not only the Saw is the horror movie…). In class architecture try to look it “upside-down”: category class has ICollection<Movie>, so if you search it up, 1 category may have many movies in this collection. The other side: a movie can have only 1 category, that’s why that property is not a collection. If you understand both sides (SQL and classes), you will understand any relational database structure.

Your solution structure should look something similar:

This is DTO in nutshell. In next part we’ll check our DAL project. This one will handle our context.