Transcript Entity Framework

Entity Framework
CODE FIRST – PRACTICAL WALKTHROUGH
21.07.2015
1
Entity Framework Code First– practical
walkthrough
This presentation will be explain how Entity Framework Code First can be used
Entity Framework Code First will be called EF Code First
EF Code First is used as an object relational mapper (ORM), where the representation
of a database is described through a set of classes, called the POCO classes
EF Code First uses conventions to describe different parts of a database, such as tables, keys,
constraints, relations to other tables and their cardinality and so on
Different practical examples will be presented to show how to do different techniques
21.07.2015
2
How to get started with EF Code First
A database is of course required for EF Code First
EF Code First will be able to create a database to be used in a development environment
For the database in the production environment, it is recommended to use database scripts to
create and update the database. It is possible to generate update scripts for any revision or
migration of the database.
EF Code First migrations can update the database to a target revision in development
environments
To get started with EF Code First, check that Entity Framework is installed. The current version
at the time of March 2014 is Entity Framework 6.1.0. Use the package manager console to
install EF if not installed yet.
The command to install the EF Nuget package is: install-package EntityFramework
21.07.2015
3
Starting using EF Code First
First off, a context type must be created.
This is a class that will be the
primary class to interact with the database.
To create such a class, create a public class
that inherits from the class DbContext in the
System.Data.Entity namespace
After a class is added, enable EF Code Migrations
Type the following command into the package
manager console:
Enable-Migrations
21.07.2015
4
Example of a db context class
The following simple code is an example of a simple db context class
using System.Data.Entity;
namespace ConsoleApplication1 {
public class DemoDbContext : DbContext
{
public DemoDbContext()
{
}
public DbSet<PasSystem> PasSystems { get; set; }
}
}
Notice that tables are defined as instances of the generic class DbSet, where the
type argument specifies the POCO class that will represent the table with the same name.
21.07.2015
5
Using a dbcontext class
The syntax to query a EF Code First dbcontext class is
Linq to Entities, also used in model based Entity Framework
To query the database, the following code shows a simple example:
 using (var context = new SchoolEntities())
{
context.Departments.Add(new Department {
Name = «Development department",
StartDate = new DateTime(2004, 1, 1)
});
context.SaveChanges();
}
21.07.2015
6
Adding migrations
Migrations are versions of the database. The database can be updated a target migration or previous migration.
It is possible to move in any direction of these migrations and also create update scripts for changing the
database to a revision.
Obviously, in production, extra care should be taken to avoid data loss. Also, most DBAs will
require database update scripts. EF Code First migrations can generate update scripts to target any revision.
In a development environment, updating a database to a target migration can be done via the package manager
console, as long as the running user has got the permission to do so
To add a migration, use the package manager console and type:
Add-Migration MyMigration
This will add a migration as a partial class in the Migrations folder of the current solution. The migration is the
transform from the current state of the database to the target state, which is changed through changing the
POCO classes defining the database.
The POCO classes that make up the database is listed in the DbContext class directly or indirectly by one of these
classes referring to another POCO class through a navigation property.
21.07.2015
7
Updating to a revision of the database
To update to a target revision of the database, type the following command in the package manager
console:
Update-Database or to watch the SQL being run: Update-Database -verbose
To generate a script instead to manually update the database, type the following command in the
package manager console:
Update-Database –Script or for example:
Update-Database –SourceMigration SomeMigration –TargetMigration SomeOtherMigration
This will generate a script for updating the database to the pending migration.
To update the database to a target migration, type the following command in the package manager
console
Update-Database –targetmigration MyMigration
To update to before the first migration: Update-Database –targetmigration:$InitialDatabase
21.07.2015
8
Conventions of EF Code First
EF Code First is based on conventions to modify and interact with a target database
These conventions must be followed to define the structure of the database (schema)
One convention is that columns are defined as public automatic properties
The primary key can be defined by defining a property called the name of the POCO class and
suffixing it with «Id». E.g. the table Patient can look like the following, where the PatientId
column will be the primary key (the primary key can also be named just Id or ID):
public class Patient
{
public int PatientId { get; set; }
public string PatientName { get; set; }
public int Age { get; set; }
}
21.07.2015
9
Using attributes to specify keys and
constraints
It is possible to use attributes on properties to specify constraints and keys
To specify a primary key, the attribute [Key] can be used on the property. This is
more specific and allows setting a primary key to another name than «tableId».
To specify a foreign key, the attribute [ForeignKey] can be used. Note that the foreign
key also must follow the naming convention «tableId» to identify the table this foreign key
specifies a relation to the current POCO class or table
To specify a constraint such as length of a string in the POCO class, the [MaxLength(n)] attribute can
be used. To specify a length of ten characters for a string, use [MaxLength(10)]
This will then be mapped to a nvarchar(10) column
In addition, the fluent API can be used to specify constraints, keys and relations. For example:
modelBuilder.Entity<Department>().Property(d => d.Name).HasMaxLength(50);
One should consider not mixing styles here and consistently use attributes, fluent API or both. Note
that attributes are not able to describe all aspects of a database, so understanding the fluent API will
be required
21.07.2015
10
Database initializers
When initializing the database, it is possible to set a database intializer somewhere in the startup
code of the application with the static method:
Database.SetInitializer – for example:
Database.SetInitializer(new MigrateDatabaseToLatestVersion<SomeDbContext,Configuration>());
The call is usually done once the application starts. It is possible to create a custom database initializer by
implementing the interface IDatabaseInitializer
Other available initializers are: DropCreateDatabaseIfModelChanges, DropCreateDatabaseAlways,
CreateDatabaseIfNotExists
Also note that automatic migrations can be turned on or off by setting the following protected property to false
in the Configuration class in the Migrations folder:
 AutomaticMigrationsEnabled = false;
Usually, it is better to manually update the database through the package manager console in development
environments, while updating the database in the production environment by manually running update scripts.
Most DBAs will require this, as the code running the updating of migrations must have special privileges on the
database if an update will be performed through code of the database. There setting
AutomaticMigrationsEnabled to false gives manual control of when the database should be migrated.
21.07.2015
11
Reverse engineering a database
Reverse engineering a database is possible
To do this, install the extension
Entity Framework Power Tools Beta 4
To start reverse engineering, right click on a
project and choose Entity Framework>
Reverse Engineer Code First
21.07.2015
12
Reverse engineering contd.
Before the reverse engineering can commence,
the connection to the database must be set up.
The reverse engineering will generate the POCO classes
and additional classes such as the db context and
define relationships between classes using mapping classes
which uses the fluent API of EF Code First
Using reverse engineering, it is possible to get a generated
EF Code First db context to an existing database that
supports migrations.
This allows quick start-up of starting with EF Code First and
and moving from another ORM, such as Entity Framework
using a model (EDMX file)
21.07.2015
13
SQL Server Data Tools (SSDT)
Another great add-on for Visual Studio is SSDT –
SQL Server Data Tools. It is available to different
versions of Visual Studio, such as VS 2012 and
VS 2013
The addon makes it easy to inspect tables of
SQL Server databases and see the schema details
of the tables
Effectively this gives the developer much
of the functionality of SQL Management Studio
inside Visual Studio
Download page is here:
http://msdn.microsoft.com/en-us/data/hh297027
21.07.2015
14
Database creation and target db
When creating a database, EF Code First will default to create a new database on
the local SQLEXPRESS instance (.\SQLEXPRESS)
If not SQLEXPRESS is installed, (LocalDb)\v11.0 is used (installed with VS2012 and VS2013)
It is possible to use another database, by specifiying this in a connection string in the
application configuration file and specify the connection string key in the constructor of the
dbcontext class.
Example. Note correspondence between name attribute in the connection string and Name= in
the constructor:
 <add name="CodeFirstSimpleDemoContextContext" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=CodeFirst
Simple.DemoContext;Integrated Security=True;MultipleActiveResultSets=True"
providerName="System.Data.SqlClient"
/>
public CodeFirstSimpleDemoContextContext() : base("Name=CodeFirstSimpleDemoContextContext"){
.. }
21.07.2015
15
Navigation properties in POCO classes
To add navigation properties to the POCO classes, add a property with
the type of the other table. To specify navigation property to a
single entity in the other table specify a property with this type.
To specify a navigation property to a collection of entities in the other
table, specify a property of type ICollection of the type of the other table.
To support lazy loading, specify the virtual keywordof the property.
The lazy loading will result in quicker execution in different scenarios. The loading of
entities are postponed until they are referenced directly in the code.
An example relationship between the two tables Course and Department is
shown on the next slide. One department can have many courses, so the relationship
is 1:n. This means that the Course table will have a foreign key to the Department table.
21.07.2015
16
EF Code First 1:n relationship
The following POCO classes define a 1:n relationship between tables Department and
Course. One department can have many courses, but a course has only one department.
Because virtual keyword is used, these navigation properties have lazy loading. Also note
that ICollection is used here.
DEPARTMENT
using System;
using System.Collections.Generic;
namespace CodeFirstSimple {
public class Department
{
public Department()
{
this.Courses = new HashSet<Course>();
}
public int DepartmentId { get; set; }
public string Name { get; set; }
public decimal Budget { get; set; }
public DateTime StartDate { get; set; }
public int? Administrator { get; set; }
public virtual ICollection<Course> Courses { get; set; }
COURSE
namespace CodeFirstSimple {
public class Course
{
public int CourseId { get; set; }
public string Title { get; set; }
public int Credits { get; set; }
public int DepartmentId { get; set; }
public virtual Department Department { get; set; }
}
}
}
}
21.07.2015
17
EF Code First 1:0..1 relationship
Also known as optional 1:1 relationship.
Example below. Table Instructor and table Address has got a 1:0..1 relationship. One Instructor may have an
address. The relationship is mapped as a nullable foreign key that points to the Address table.
public
public
public
public
public
public
}
class Address {
string AdressId { get; set; }
string PostalPlace { get; set; }
int ZipCode { get; set; }
string StreetAddress { get; set; }
virtual Instructor Instructor { get; set; }
To add an optional foreign key using fluent
API, called InstructorAddressId in the Instructor
table, the following syntax is used:
modelBuilder.Entity<Instructor>()
.HasOptional<Address>(i => i.Address)
.WithOptionalDependent(a => a.Instructor)
.Map(a => a.MapKey("InstructorAddressId"));
21.07.2015
public class Instructor {
public Instructor()
{
this.Courses = new List<Course>();
}
// Primary key
public int InstructorID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public System.DateTime HireDate { get; set; }
// Navigation properties
public virtual Address Address { get; set; }
public virtual ICollection<Course> Courses { get; private set
; }
}
18
EF Code First m:n relationship
Also known as many-to-many relationships
Example below, with Tags and Posts, defining a m:n relationship.
public class Post
{
public int Id { get; set; }
public string Title { get; set; }
public DateTime DateCreated { get; set; }
public DateTime LastUpdate { get; set; }
public string Content { get; set; }
public int FKBlogId { get; set; }
public Blog Blog { get; set; }
public ICollection<Tag> Tags { get; set; }
}
public class Tag
{
public int TagId { get; set; }
public string Name { get; set; }
public ICollection<Post> Posts { get; set; }
}
Actually, EF Code First will automatically create a table to express this relationship called TagPosts.
EF Code First will create a table with the primary key(s) from both tables in the relationship and call
each column in this table for: tablename + «_» + primary key column name. The resulting table is
shown above in the screen capture.
21.07.2015
19
EF Code First m:n relationships contd.
To create link tables in m:n relationships between two tables, it is possible to define navigation
properties in the two entities that point to eachother (and is of type ICollection)
It is also possible with the fluent API to have a more specific naming of the link table. Example
below:

modelBuilder.Entity<Post>().HasMany(p => p.Tags).WithMany(t => t.Posts)
.Map(
m =>
{
m.ToTable("PostTags");
m.MapLeftKey("PostId");
m.MapRightKey("TagId");
});
In the example code above, the name of the link table is called PostTags and the Map method is used, specifying the key names of this
table. Make note that the parameters in the MapLeftKey is the primary key names of the left entity (Post) and in the MapRightKey is the
primary key names of the right entity (Tags). It is possible to have several primary key columns and these two methods takes a params
string array.
21.07.2015
20
Entity splitting
It is sometimes desired to split an entity into two or more tables, known as entity splitting
In the example below, the entity Department is split into two tables Department and
DepartmentDetails.
modelBuilder.Entity<Department>()
.Map(m => {
m.Properties(d => new { d.DepartmentID, d.Name });
m.ToTable("Department");
})
.Map(m =>
{
m.Properties(d => new { d.DepartmentID, d.Name, d.Administrator, d.StartDate });
m.ToTable("DepartmentDetails");
});
Another strategy is of course to create POCO classes for each table as usual. This can also
be considered a cleaner, more consistent strategy.
21.07.2015
21
Specifying identity for numeric primary
key
Numeric primary keys (integer based) of tables will automatically be created with identity
increment set to (1,1) (seed and increment) in EF Code First
To specifically set database generation options the following example shows how this can be
specified in the fluent API:
modelBuilder.Entity<Department>().Property(d => d.DepartmentID)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
In addition, it is possible to set the HasDataBaseGeneratedOption to Computed or None.
21.07.2015
22
Specifying a multi-column primary key
It is possible to create a multi-column primary key for a table by using the fluent API.
The following example shows how to create a two-column primary key in the Department
table:
modelBuilder.Entity<Department>()
.HasKey(d => new { d.DepartmentID, d.Name });
21.07.2015
23
Creating indexes with EF Code First
Creating indexes with EF Code First can be done with different technique.
One technique is to add an empty migration and manually add in the code for creating or dropping the index in the Up and Down method
of the migration.
Example below. Creating a non-unique index on the ZipCode column in the Address table / entity can be done by first running: AddMigration CreateAddressZipCodeIndex (arbitrary name):
namespace DepartmentDemo.Migrations {
using System; using System.Data.Entity.Migrations;
public partial class CreateAddressZipCodeIndex : DbMigration
{
public override void Up(){ CreateIndex("Address", "ZipCode", false, "IX_Address_ZipCode");
}
public override void Down(){ DropIndex("Address", "IX_Address_ZipCode"); } }
}
The code above shows how to create and drop an index. The first argument is the table name of which the index belongs,
and the next argument is either a string or an array of strings of the column(s) to create the index on. The next argument
specifies if the index is unique or not and the last parameter is the name of the index.
The Down method drops the index and the Up method creates the index. Both methods must be implemented so that
migrations up and down is possible.
21.07.2015
24
EF Code First vs Entity Model-based:
Pros and Cons
The following pros and cons are author based and should be considered as suggestive guidelines and
nothing more.
Pros
EF Code First lacks a model file and therefore scales better when for larger, real-world scenarios.
A large model (.edmx file) becomes unwieldy and hard to control when the amount of tables the
model describes grow. Also changing branches with different migrations is also hard to control, since
this is done manually. The model file also contains a lot of metadata. The POCO classes of EF Code
First are however easier to scale.
Since EF Code First is code based, the programmer is put more in the front seat of controlling the
database.
The definition of the database is much clearer than in the model based approach. Instead of
generating classes from a model, the developer writes entity classes manually and can also control
how migrations are done between versions of the database, as development progresses.
21.07.2015
25
EF Code First vs Entity Model-based:
Pros and cons contd.
Cons
A steeper learning curve is associated with EF Code First than model based approaches
In the model based approach, the developer will use for example SQL Management Studio and design
the database in a database diagram, generate change scripts, commit the change script, open up the
model file and update the model file towards the database. In EF Code First, the programmer must
manually create POCO classes for the entities. Relationships between the tables must be in most cases
be expressed using the fluent API of EF Code First. In other words, EF Code First means that the
programmers must also learn how to create entity tables and other aspects of a database manually.
Sometimes, moving between migrations becomes challenging because of conflicting changes. Also
the programmer’s intent is not always matched.
Requirements for a database changes over time. Sometimes, applying changes or migrations to a
database becomes hard, because there are conflicting changes between the existing database
structure and the desired structure. Also, the intent of the programmer is sometimes not expressed
correctly in EF Code First because of misinterpretations. Care must be taken so that the desired change
matches the actual change to the database. It is recommended to turn off automatic migrations being
applied and generate database change scripts so this process can be controlled.
21.07.2015
26
Summary
EF Code First is a modern approach to creating a data layer object relational mapping between
data layer classes and a physical database. EF Code First supports migrations (versions) of the
database and moving up or down between these. Care should be taken to avoid data loss,
therefore chronologically moving up to the next migration is suggested. EF Code First can also
generate change scripts and in code it is possible to create entities and specify relations
between entities / tables.
Check out these web pages for more information about EF Code First:
Data Developer Center > Learn > Entity Framework > Get Started >
Code First to a New Database http://msdn.microsoft.com/en-us/data/jj193542.aspx
EF Code First End To Video (75 mins):
https://www.youtube.com/watch?v=l3WzYZsN0gU
21.07.2015
27