Entity Framework Notes for Professionals

34

Chapter 8: Mapping relationship with Entity Framework Code First: One-to-one and

variations

........................................................................................................................................................................

36

Section 8.1: Mapping one-to-zero or one

Section 8.2: Mapping one-to-one

Section 8.3: Mapping one or zero-to-one or zero

..................................................................................................................

..............................................................................................................................

....................................................................................................

40

Chapter 9: Mapping relationship with Entity Framework Code First: One-to-many and

Many-to-many

.............................................................................................................................................................

41

............................................................................................................................

.................................................................................

..............................................................................................................

........................................................................................................................................

Section 9.1: Mapping one-to-many

Section 9.2: Mapping one-to-many: against the convention

Section 9.3: Mapping zero or one-to-many

Section 9.4: Many-to-many

Section 9.5: Many-to-many: customizing the join table

Section 9.6: Many-to-many: custom join entity

Chapter 10: Database first model generation

Section 10.1: Generating model from database

Section 10.2: Adding data annotations to the generated model

..........................................................................................

.......................................................................................................

.............................................................................................

.......................................................................................................

...........................................................................

50

Chapter 11: Complex Types

....................................................................................................................................

52

Section 11.1: Code First Complex Types

.....................................................................................................................

52

Chapter 12: Database Initialisers

........................................................................................................................

53

Section 12.1: CreateDatabaseIfNotExists

Section 12.2: DropCreateDatabaseIfModelChanges

Section 12.3: DropCreateDatabaseAlways

Section 12.4: Custom database initializer

Section 12.5: MigrateDatabaseToLatestVersion

...................................................................................................................

53

...............................................................................................

53

...............................................................................................................

..................................................................................................................

......................................................................................................

Chapter 13: Tracking vs. No-Tracking

..............................................................................................................

55

Section 13.1: No-tracking queries

Section 13.2: Tracking queries

Section 13.3: Tracking and projections

...............................................................................................................................

....................................................................................................................................

......................................................................................................................

55

Chapter 14: Transactions

.......................................................................................................................................

57

Section 14.1: Database.BeginTransaction()

...............................................................................................................

57

Chapter 15: Managing entity state

....................................................................................................................

58

Section 15.1: Setting state Added of a single entity

Section 15.2: Setting state Added of an object graph

..................................................................................................

58

.............................................................................................

58

Chapter 16: Loading related entities

................................................................................................................

60

Section 16.1: Eager loading

Section 16.2: Explicit loading

Section 16.3: Lazy loading

Section 16.4: Projection Queries

Chapter 17: Model Restraints

.........................................................................................................................................

.......................................................................................................................................

...........................................................................................................................................

.................................................................................................................................

................................................................................................................................

63

Section 17.1: One-to-many relationships

...................................................................................................................

63

Chapter 18: Entity Framework with PostgreSQL

........................................................................................

65

Section 18.1: Pre-Steps needed in order to use Entity Framework 6.1.3 with PostgresSql using

Npgsqlddexprovider

...........................................................................................................................................

65

Chapter 19: Entity Framework with SQLite

....................................................................................................

66

Section 19.1: Setting up a project to use Entity Framework with an SQLite provider

...........................................

66

Chapter 20: .t4 templates in entity framework

Section 20.1: Dynamically adding Interfaces to model

.........................................................................................

69

...........................................................................................

69

36

39

41

42

43

44

45

46

49

49

53

53

54

55

55

60

60

61

61

Section 20.2: Adding XML Documentation to Entity Classes

..................................................................................

69

Chapter 21: Advanced mapping scenarios: entity splitting, table splitting

..................................

71

Section 21.1: Entity splitting

Section 21.2: Table splitting

.........................................................................................................................................

71

........................................................................................................................................

72

Chapter 22: Best Practices For Entity Framework (Simple & Professional)

.................................

73

Section 22.1: 1- Entity Framework @ Data layer (Basics)

Section 22.2: 2- Entity Framework @ Business layer

Section 22.3: 3- Using Business layer @ Presentation layer (MVC)

Section 22.4: 4- Entity Framework @ Unit Test Layer

.......................................................................................

..............................................................................................

.......................................................................

79

............................................................................................

81

Chapter 23: Optimization Techniques in EF

...................................................................................................

85

..............................................................................................................................

85

Section 23.1: Using AsNoTracking

Section 23.2: Execute queries in the database when possible, not in memory

Section 23.3: Loading Only Required Data

Section 23.4: Execute multiple queries async and in parallel

Section 23.5: Working with stub entities

Section 23.6: Disable change tracking and proxy generation

...............................................................................................................

.................................................................................

...................................................................................................................

...............................................................................

....................................................

85

73

76

85

86

87

88

Credits

..............................................................................................................................................................................

89

You may also like

........................................................................................................................................................

90

About

Please feel free to share this PDF with anyone for free,

latest version of this book can be downloaded from:

https://goalkicker.com/EntityFrameworkBook

This Entity Framework Notes for Professionals book is compiled from Stack

Overflow Documentation, the content is written by the beautiful people at Stack

Overflow. Text content is released under Creative Commons BY-SA, see credits at

the end of this book whom contributed to the various chapters. Images may be

copyright of their respective owners unless otherwise specified

This is an unofficial free book created for educational purposes and is not

affiliated with official Entity Framework group(s) or company(s) nor Stack

Overflow. All trademarks and registered trademarks are the property of their

respective company owners

The information presented in this book is not guaranteed to be correct nor

accurate, use at your own risk

Please send feedback and corrections to [email protected]

GoalKicker.com – Entity Framework Notes for Professionals

1

Chapter 1: Getting started with Entity

Framework

Version

1.0

4.0

4.1

Release Date

2008-08-11

2010-04-12

2011-04-12

4.1 Update 1 2011-07-25

4.3.1

5.0

6.0

6.1

2012-02-29

2012-08-11

2013-10-17

2014-03-17

Core 1.0

2016-06-27

Release Notes: https://msdn.microsoft.com/en-ca/data/jj574253.aspx

Section 1.1: Installing the Entity Framework NuGet Package

In your Visual Studio open the Solution Explorer window then right click on your project then choose

Manage NuGet Packages from the menu:

In the window that opens type EntityFramework in the search box in the top right.

GoalKicker.com – Entity Framework Notes for Professionals

2

Or if you are using Visual Studio 2015 you'll see something like this:

Then click Install.

We can also install entity framework using the package manager console. To do you have first to open it using the

Tools menu -> NuGet Package Manager -> Package Manager Console then enter this:

GoalKicker.com – Entity Framework Notes for Professionals

3

Install-Package EntityFramework

This will install Entity Framework and automatically add a reference to the assembly in your project.

Section 1.2: Using Entity Framework from C# (Code First)

Code first allows you to create your entities (classes) without using a GUI designer or a .edmx file. It is named Code

first, because you can create your models first and Entity framework will create database according to mappings for

you automatically. Or you can also use this approach with existing database, which is called code first with existing

database For example, if you want a table to hold a list of planets:

public class Planet

{

public string Name { get; set; }

public decimal AverageDistanceFromSun { get; set; }

}

Now create your context which is the bridge between your entity classes and the database. Give it one or more

DbSet<> properties:

using System.Data.Entity;

public class PlanetContext : DbContext

{

public DbSet<Planet> Planets { get; set; }

}

We can use this by doing the following:

using(var context = new PlanetContext())

{

var jupiter = new Planet

{

GoalKicker.com – Entity Framework Notes for Professionals

4

Name = "Jupiter",

AverageDistanceFromSun = 778.5

};

context.Planets.Add(jupiter);

context.SaveChanges();

}

In this example we create a new Planet with the Name property with the value of "Jupiter" and the

AverageDistanceFromSun property with the value of 778.5

We can then add this Planet to the context by using the DbSet's Add() method and commit our changes to the

database by using the SaveChanges() method.

Or we can retrieve rows from the database:

using(var context = new PlanetContext())

{

var jupiter = context.Planets.Single(p => p.Name == "Jupiter");

Console.WriteLine($"Jupiter is {jupiter.AverageDistanceFromSun} million km from the sun.");

}

Section 1.3: What is Entity Framework?

Writing and managing ADO.Net code for data access is a tedious and monotonous job. Microsoft has provided an

O/RM framework called "Entity Framework" to automate database related activities for your application.

Entity framework is an Object/Relational Mapping (O/RM) framework. It is an enhancement to ADO.NET that gives

developers an automated mechanism for accessing & storing the data in the database.

What is O/RM?

ORM is a tool for storing data from domain objects to the relational database like MS SQL Server, in an automated

way, without much programming. O/RM includes three main parts:

1.

2.

3.

Domain class objects

Relational database objects

Mapping information on how domain objects map to relational database objects(e.x tables, views & stored

procedures)

ORM allows us to keep our database design separate from our domain class design. This makes the application

maintainable and extendable. It also automates standard CRUD operation (Create, Read, Update & Delete) so that

the developer doesn't need to write it manually.

GoalKicker.com – Entity Framework Notes for Professionals

5

Chapter 2: Code First Conventions

Section 2.1: Removing Conventions

You can remove any of the conventions defined in the System.Data.Entity.ModelConfiguration.Conventions

namespace, by overriding OnModelCreating method.

The following example removes PluralizingTableNameConvention.

public class EshopContext : DbContext

{

public DbSet<Product> Products { set; get; }

. . .

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

}

}

By default EF will create DB table with entity class name suffixed by 's'. In this example, Code First is configured to

ignore PluralizingTableName convention so, instead of dbo.Products table dbo.Product table will be created.

Section 2.2: Primary Key Convention

By default a property is a primary key if a property on a class is named “ID” (not case sensitive), or the class name

followed by "ID". If the type of the primary key property is numeric or GUID it will be configured as an identity

column. Simple Example:

public class Room

{

// Primary key

public int RoomId{ get; set; }

...

}

Section 2.3: Type Discovery

By default Code First includes in model

1.

2.

3.

Types defined as a DbSet property in context class.

Reference types included in entity types even if they are defined in different assembly.

Derived classes even if only the base class is defined as DbSet property

Here is an example, that we are only adding Company as DbSet<Company> in our context class:

public class Company

{

public int Id { set; get; }

public string Name { set; get; }

public virtual ICollection<Department> Departments { set; get; }

}

public class Department

{

public int Id { set; get; }

GoalKicker.com – Entity Framework Notes for Professionals

6

public string Name { set; get; }

public virtual ICollection<Person> Staff { set; get; }

}

[Table("Staff")]

public class Person

{

public int Id { set; get; }

public string Name { set; get; }

public decimal Salary { set; get; }

}

public class ProjectManager : Person

{

public string ProjectManagerProperty { set; get; }

}

public class Developer : Person

{

public string DeveloperProperty { set; get; }

}

public class Tester : Person

{

public string TesterProperty { set; get; }

}

public class ApplicationDbContext : DbContext

{

public DbSet<Company> Companies { set; get; }

}

We can see that all the classes are included in model

Section 2.4: DecimalPropertyConvention

By default Entity Framework maps decimal properties to decimal(18,2) columns in database tables.

GoalKicker.com – Entity Framework Notes for Professionals

7

public class Box

{

public int Id { set; get; }

public decimal Length { set; get; }

public decimal Width { set; get; }

public decimal Height { set; get; }

}

We can change the precision of decimal properties:

1.Use Fluent API:

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

modelBuilder.Entity<Box>().Property(b => b.Width).HasPrecision(20, 4);

}

Only "Width" Property is mapped to decimal(20, 4).

2.Replace the convention:

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

modelBuilder.Conventions.Remove<DecimalPropertyConvention>();

modelBuilder.Conventions.Add(new DecimalPropertyConvention(10, 4));

}

GoalKicker.com – Entity Framework Notes for Professionals

8

Every decimal property is mapped to decimal(10,4) columns.

Section 2.5: Relationship Convention

Code First infer the relationship between the two entities using navigation property. This navigation property can

be a simple reference type or collection type. For example, we defined Standard navigation property in Student

class and ICollection navigation property in Standard class. So, Code First automatically created one-to-many

relationship between Standards and Students DB table by inserting Standard_StandardId foreign key column in the

Students table.

public class Student

{

public int StudentID { get; set; }

public string StudentName { get; set; }

public DateTime DateOfBirth { get; set; }

//Navigation property

public Standard Standard { get; set; }

}

public class Standard

{

public int StandardId { get; set; }

public string StandardName { get; set; }

//Collection navigation property

public IList<Student> Students { get; set; }

}

The above entities created the following relationship using Standard_StandardId foreign key.

GoalKicker.com – Entity Framework Notes for Professionals

9

Section 2.6: Foreign Key Convention

If class A is in relationship with class B and class B has property with the same name and type as the primary key of

A, then EF automatically assumes that property is foreign key.

public class Department

{

public int DepartmentId { set; get; }

public string Name { set; get; }

public virtual ICollection<Person> Staff { set; get; }

}

public class Person

{

public int Id { set; get; }

public string Name { set; get; }

public decimal Salary { set; get; }

public int DepartmentId { set; get; }

public virtual Department Department { set; get; }

}

In this case DepartmentId is foreign key without explicit specification.

GoalKicker.com – Entity Framework Notes for Professionals

10

Chapter 3: Code First DataAnnotations

Section 3.1: [Column] attribute

public class Person

{

public int PersonID { get; set; }

[Column("NameOfPerson")]

public string PersonName { get; set; }

}

Tells Entity Framework to use a specific column name instead using the name of the property. You can also specify

the database data type and the order of the column in table:

[Column("NameOfPerson", TypeName = "varchar", Order = 1)]

public string PersonName { get; set; }

Section 3.2: [DatabaseGenerated] attribute

Specifies how the database generates values for the property. There are three possible values:

1.

2.

3.

None specifies that the values are not generated by the database.

Identity specifies that the column is an identity column, which is typically used for integer primary keys.

Computed specifies that the database generates the value for the column.

If the value is anything other than None, Entity Framework will not commit changes made to the property back to

the database.

By default (based on the StoreGeneratedIdentityKeyConvention) an integer key property will be treated as an

identity column. To override this convention and force it to be treated as a non-identity column you can use the

DatabaseGenerated attribute with a value of None.

using System.ComponentModel.DataAnnotations.Schema;

public class Foo

{

[Key]

public int Id { get; set; } // identity (auto-increment) column

}

public class Bar

{

[Key]

[DatabaseGenerated(DatabaseGeneratedOption.None)]

public int Id { get; set; } // non-identity column

}

The following SQL creates a table with a computed column:

CREATE TABLE [Person] (

Name varchar(100) PRIMARY KEY,

DateOfBirth Date NOT NULL,

Age AS DATEDIFF(year, DateOfBirth, GETDATE())

)

GoalKicker.com – Entity Framework Notes for Professionals

11

GO

To create an entity for representing the records in the above table, you would need to use the DatabaseGenerated

attribute with a value of Computed.

[Table("Person")]

public class Person

{

[Key, StringLength(100)]

public string Name { get; set; }

public DateTime DateOfBirth { get; set; }

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

public int Age { get; set; }

}

Section 3.3: [Required] attribute

When applied to a property of a domain class, the database will create a NOT NULL column.

using System.ComponentModel.DataAnnotations;

public class Person

{

public int PersonID { get; set; }

[Required]

public string PersonName { get; set; }

}

The resulting column with the NOT NULL constraint:

Note: It can also be used with asp.net-mvc as a validation attribute.

Section 3.4: [MaxLength] and [MinLength] attributes

[MaxLength(int)] attribute can be applied to a string or array type property of a domain class. Entity Framework

will set the size of a column to the specified value.

using System.ComponentModel.DataAnnotations;

public class Person

{

public int PersonID { get; set; }

[MinLength(3), MaxLength(100)]

public string PersonName { get; set; }

}

The resulting column with the specified column length:

GoalKicker.com – Entity Framework Notes for Professionals

12

[MinLength(int)] attribute is a validation attribute, it does not affect the database structure. If we try to

insert/update a Person with PersonName with length less than 3 characters, this commit will fail. We’ll get a

DbUpdateConcurrencyException that we'll need to handle.

using (var db = new ApplicationDbContext())

{

db.Staff.Add(new Person() { PersonName = "ng" });

try

{

db.SaveChanges();

}

catch (DbEntityValidationException ex)

{

//ErrorMessage = "The field PersonName must be a string or array type with a minimum length

of '3'."

}

}

Both [MaxLength] and [MinLength] attributes can also be used with asp.net-mvc as a validation attribute.

Section 3.5: [InverseProperty(string)] attribute

using System.ComponentModel.DataAnnotations.Schema;

public class Department

{

...

public virtual ICollection<Employee> PrimaryEmployees { get; set; }

public virtual ICollection<Employee> SecondaryEmployees { get; set; }

}

public class Employee

{

...

[InverseProperty("PrimaryEmployees")]

public virtual Department PrimaryDepartment { get; set; }

[InverseProperty("SecondaryEmployees")]

public virtual Department SecondaryDepartment { get; set; }

}

InverseProperty can be used to identify two way relationships when multiple two way relationships exist between

two entities.

It tells Entity Framework which navigation properties it should match with properties on the other side.

Entity Framework doesn't know which navigation property map with which properties on the other side when

multiple bidirectional relationships exist between two entities.

It needs the name of the corresponding navigation property in the related class as its parameter.

GoalKicker.com – Entity Framework Notes for Professionals

13

This can also be used for entities that have a relationship to other entities of the same type, forming a recursive

relationship.

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

public class TreeNode

{

[Key]

public int ID { get; set; }

public int ParentID { get; set; }

...

[ForeignKey("ParentID")]

public TreeNode ParentNode { get; set; }

[InverseProperty("ParentNode")]

public virtual ICollection<TreeNode> ChildNodes { get; set; }

}

Note also the use of the ForeignKey attribute to specify the column that is used for the foreign key on the table. In

the first example, the two properties on the Employee class could have had the ForeignKey attribute applied to

define the column names.

Section 3.6: [ComplexType] attribute

using System.ComponentModel.DataAnnotations.Schema;

[ComplexType]

public class BlogDetails

{

public DateTime? DateCreated { get; set; }

[MaxLength(250)]

public string Description { get; set; }

}

public class Blog

{

...

public BlogDetails BlogDetail { get; set; }

}

Mark the class as complex type in Entity Framework.

Complex Types (Or Value Objects In Domain Driven Design) cannot be tracked on their own but they are tracked as

part of an entity. This is why BlogDetails in the example does not have a key property.

They can be useful when describing domain entities across multiple classes and layering those classes into a

GoalKicker.com – Entity Framework Notes for Professionals

14

complete entity.

Section 3.7: [ForeignKey(string)] attribute

Specifies custom foreign key name if a foreign key not following EF's convention is desired.

public class Person

{

public int IdAddress { get; set; }

[ForeignKey(nameof(IdAddress))]

public virtual Address HomeAddress { get; set; }

}

This can also be used when you have multiple relationships to the same entity type.

using S...