4/18/13

Entity Framework - Introduction (Part 2 of 3)

Entity Framework - Introduction (Part 2 of 3)

(Part 1)(Part 3)



Part 2 Model First

In part one we looked at creating a database fist project and generated a model from our pre-existing database, in part 2 we are going to build a model first and then create the database from the model. To begin this part of the tutorial we'll create a new class library project.

Create a new Visual Studio Class Library Project


Once the new project has been created, right click on the project name and select Add | New Item


Under C# Select Data | ADO.NET Entity Data Model


Select Empty Model and Finish


Building the Model

Next we'll add some entity objects on the design surface and start building our model. Entity objects can be created either by dragging and dropping items from the toolbox to the design surface or right clicking on the design surface and selecting Add | New Item.

Add From Toolbox


Add from Designer Surface


First I'll add a new Entity and call it Movie, notice that by default a new ID property is created with an int32 type and set to an identity.


Next, I'll add two more scalar properties to the Movie entity, title and date. 
Note: The default for a new scalar property is string and non-nullable. Note: the max length attribute of our scalar property will be used when the database is created for the max length of the column.


Change the type of the date property to DateTime


The next Entity will be called Genre, create one scalar property in the Genre called Category with a default type of string. The last Entity I'll create will be called Actor, I'll create one new scalar property called Name with a default type of string.

The Entity diagram should now look something like this


Create the Entity Relationships (Associations)

Right click on Movie and Select Add | Association


Ensure that Movie and Actor are selected in the dropdown boxes, Change the Multiplicity to Many to Many, this is because there can be many actors in a movie and actors can be in many movies. The new association will create 2 new navigation properties, Actors and Movies.


The Design Diagram has been updated showing the new association, many movies to many actors



Next create another association for Movie to Genre, this association will be many to one because movie can only have 1 genre but genre can be associated with many movies.




Generate the Database from the Model

Next we're going to create our SQL database from our model design. 
Note: The database that we are going to create needs to already exist on the SQL Server, just a shell of a database that we can point to. The command to Generate the database from the model actually just generates the schema in the existing database.

Create a new database on the SQL server called MovieLibrary

Before we generate the database from the model, let's take a look at the metadata that describes the database and metadata that describes the mapping between the model and the database.

To view the metadata, right click on the ModelFirst.edmx file in the Visual Studio Solution Explorer and select Open With | XML




There are two highlighted sections in the XML file, the first is the storage schema that describes the database and the second is the mapping layer, these sections are currently empty because we have not generated the database from the model yet.


Next go back to the Designer view of the model and right click on the design surface and select Generate Database From Model.

Create a new connection to the SQL server, select the new MovieLibrary database.


Select Next

'

Select Finish and the SQL file will be created that can be run against the SQL Server.



Right click anywhere in the file and select Execute. You'll be prompted from the SQL Server to enter credentials. After credentials have been entered you should get a message in the T-SQL window saying the Command Completed Successfully.

Take a look inside the database and see if the tables created in the model are now in the database.



This completes part 2 of the Entity Framework introduction, in part 3 I will discuss using the newly created models to leverage the Entity Framework.

(Part 1)(Part 3)

4/17/13

Microsoft Entity Framework Intro (Part 1 of 3)


Entity Framework - Introduction (Part 1 of 3)

(Part 2, Part 3)


The following tutorial will explain some basic concepts using the Microsoft Entity Framework, I'll attempt to explain this with a few examples to help demonstrate some of the capabilities.

Recomendations for this tutorial, other versions of these products may work however these are the versions used in this tutorial.

1. Microsoft Visual Studio 2012
2. SQL Server 2008R2
3. AdventureWorks database (2008R2 version) downloaded and installed from http://msftdbprodsamples.codeplex.com/downloads/get/478216

Introduction

The Microsoft Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write. This creates, in effect, a "virtual object database" that can be used from within a project to access database objects using C# code instead of having to write a whole lot of SQL queries. In this post we'll add a sample database to our SQL server, create a new Entity Framework model from our existing sample database and try and get an understanding of the model.

Part 1 - Database First

Adding the AdventureWorks Database - If you do not have the demo database attached to your SQL Server you can download the database from the link above. Once downloaded attach the database to SQL Server.

Step 1 - Copy the database files into the correct file locations for your SQL Server. The .mdf file should be copied into the data folder where all the other SQL Databases exist and the .ldf file should be copied into the the Log folder where all the other Log files exist.

Step 2 -From the SQL Server Management Console right click on databases and select Attach.


Step 3 - Select Add and browse to the correct .mdf file located where you copied it on the SQL Server


Step 4 - Select the ellipsis next to the log file and ensure the log file path is correct



Step 5 - Ensure the AdventureWorks database is correctly attached to your SQL Server


Creating the Visual Studio Project - Next we'll create a Visual Studio Project to demonstrate some of the Entity Framework capabilities.

Note: If you are using a version of Visual Studio other than 2012, you may not have the Entit

In this example I'll create a new class library project, I'll be able to add or reference this project in any new solution that I create in order to leverage the Entity Framework.

Create a new class library and name it EFModel


Right click on the project name and select add, add a new ADO.NET Entity Data Model



Create an Entity Data Model From a database



Create a new connection to your database


rename the connection settings AWEntities


Select Tables and Views to be populated from the database into our new Entity Model and select Finish


We can now see a new Entity Framework model has been created called EFModel, this will be instantiated in our project when we want to leverage the Entity Framework.


The designer view of the Entity Model will be displayed, we can view all of the relationships or associations in the designer. Notice the relationship between Customer and SalesOrderHeader is a one to many relationship (depicted by the 1 on customer and the asterisks on SalesOrderHeader)



In our project an App.Config file has been created, within this file an Entity connection string can be found.


If we right click on the white space in the designer view of our Entity Model and select Mapping Details, then select one of the entities in the diagram, the database column to class property mapping can be viewed or modified.


The mapping details shows the relationships between the database columns and the model properties, these are also customize-able.


Conclusion

This wraps up part one of the introduction to the Entity Framework, we covered getting a sample database installed on our SQL Server 2008R2 Database Server, Creating a new Class Library project, adding an Entity Framework model created from our existing AdventureWorks database and briefly looking at our completed Model. In part two we'll cover creating a SQL database from an existing model.

(Part 2, Part 3)