02 - EF Core (1)

Object Relational Mapper (ORM)

What does it do?

Converts data between incompatible type systems using OOP languges. Creates virtual object database that can be used from within programming language.

Several different ORM implementations exist in .Net world

NHibernate
Entity Framework (EF in short)

Not so heavyweight variants - Micro ORMs

Dapper
PetaPoCo/Massive
Simple.Data

Danger

All ORM to DataBase (SQL, NoSql, ...) frameworks are NO EXCUSE TO NOT LEARN PROPER SQL!!!

Underneath ORM abstractions SQL is generated. To understand quality of your ORM operations – you need to understand the generated SQL. And what was not generated into SQL and thus performed in-memory.

Entity framework (EF)

Two implementations/versions exists:

Entity Framework 6 – EF6
- Tried and tested data access technology with many years of features and stabilization.
- Only available on .NET Framework on windows
Entity Framework Core – EF Core
- EF Core is a lightweight, extensible, and cross-platform version of Entity Framework.
- Works on top of .NET (multiplatform)

Database First/Code First

Two possibilities from viewpoint of database:

New database (so called greenfield)
Existing database

Two possibilities from viewpoint of design:

Design first
Code first

EF6 supports all 4 possibilities.

EF Core – always code first. You can scaffold your entities from existing database (and database from design) – but result is the same as if you would have started from scratch.

Fluent Interface / Fluent Api

Fluent interface (as first coined by Eric Evans and Martin Fowler) is a method for designing object-oriented APIs based extensively on method chaining with the goal of making the readability of the source code close to that of ordinary written prose, essentially creating a domain-specific language within the interface.

var translations = new Dictionary<string, string>
{
    {"cat", "chat"}, {"dog", "chien"}, {"fish", "poisson"}, {"bird", "oiseau"}
};
IEnumerable<string> query = translations
    .Where (t => t.Key.Contains("a"))
    .OrderBy (t => t.Value.Length)
    .Select (t => t.Value.ToUpper());

The Model

Data access is performed using a model. A model is made up of entity classes and a derived context that represents a session with the database, allowing you to query and save data.

public class Person {
    public int PersonId { get; set; }
    public int Name { get; set; }
}

public class AppDbContext : DbContext {
    public DbSet<Person> Persons { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder){
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb......");
    }
}

Configuring

Two possibilities:

Attributes on top of property/entity
- Usable also by other frameworks - MVC validation, UI generation, ...
Fluent API in DbContext OnModelCreating

class MyContext : DbContext{

    public DbSet<Person> Persons { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>()
            .HasKey(c => c.SocialSecurityCode);
    }
}

Primary Key (PK)

Mandatory in EF!

By convention, a property named Id or <type name>Id will be configured as the key of an entity

Data Annotations
[Key]

Fluent Api:

modelBuilder.Entity<Person>().HasKey(c => c.SocialSecurityCode);

Primary Key - Composite

PK can also be constructed out of several fields.

Data annotations – not possible!

Fluent Api:

modelBuilder.Entity<Person>().HasKey(c => new {
    c.SocialSecurityCode,
    c.Email }
);

Alternate Keys

An alternate key serves as an alternate unique identifier for each entity instance in addition to the primary key. Alternate keys can be used as the target of a relationship.
If you just want to enforce uniqueness of a column then you want a unique index rather than an alternate key.

Data Annotations – not possible!

Fluent Api:

modelBuilder.Entity<Person>()
    .HasAlternateKey(c => new { c.Email, c.FirstName });

Generated values

No value generation

You always have to supply a valid value to be saved to database.

Value generated on add

Depending on the database provider being used, values may be generated client side by EF or in the database. If the value is generated by the database, then EF may assign a temporary value when you add the entity to the context. This temporary value will then be replaced by the database generated value during SaveChanges().

Value generated on add or update

Value generated on add or update means that a new value is generated every time the record is saved (insert or update).

Conventions

Non-composite primary keys of type short, int, long, or Guid will be setup to have values generated on add. All other properties will be setup with no value generation.

// Data annotations

[DatabaseGenerated(DatabaseGeneratedOption.None)]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]

// Fluent Api

modelBuilder.Entity<Person>()
    .Property(b => b.SocialSecurityCode).ValueGeneratedNever();
modelBuilder.Entity<Person>()
    .Property(b => b.InsertedAtDateTime).ValueGeneratedOnAdd();
modelBuilder.Entity<Blog>()
    .Property(b => b.LastUpdatedAtDateTime).ValueGeneratedOnAddOrUpdate();

How the value is generated for added and updated entities will depend on the database provider being used. Database providers may automatically setup value generation for some property types, while others will require you to manually setup how the value is generated.

For example, when using SQL Server, byte[] properties that are set as generated on add or update and marked as concurrency tokens, will be setup with the rowversion data type - so that values will be generated in the database. However, if you specify that a DateTime property is generated on add or update, then you must setup a way for the values to be generated (look into default values).

Required and Optional

Required and Optional Properties

A property whose CLR type cannot contain null cannot be configured as optional. The property will always be considered required by Entity Framework.
Optional (nullable)
- string, int?, byte[], …
Required
- int, decimal, bool, …

Data Annotations:

[Required]
Think about non-nullable vs nullable reference types

Fluent API:

modelBuilder.Entity<Person>()
    .Property(p => p.SocialSecurityCode)
    .IsRequired();

Maximum Length

Configuring a maximum length provides a hint to the data store about the appropriate data type to use for a given property. Maximum length only applies to array data types, such as string and byte[].

Entity Framework does not do any validation of maximum length before passing data to the provider. It is up to the provider or data store to validate if appropriate.

By convention, it is left up to the database provider to choose an appropriate data type for properties.

Microsoft SQL Server will use nvarchar(max) for string properties (or nvarchar(450) if the column is used as a key).

Data Annotations:

[MaxLength(32)]

Fluent API:

modelBuilder.Entity<Person>()
    .Property(p => p.FirstName)
    .HasMaxLength(32);

Shadow Properties

Shadow properties are properties that are not defined in your .NET entity class but are defined for that entity type in the EF Core model. The value and state of these properties is maintained purely in the Change Tracker.

Usually bad practice!

Shadow properties are useful when there is data in the database that should not be exposed on the mapped entity types. They are most often used for foreign key properties, where the relationship between two entities is represented by a foreign key value in the database, but the relationship is managed on the entity types using navigation properties between the entity types.

Accessing shadow properties – through ChangeTracker API

context.Entry(person)
    .Property("LastUpdated").CurrentValue = DateTime.Now;

var persons = context.Persons
    .OrderBy(p => EF.Property<DateTime>(p, "LastUpdated"));

Convention:

Created by model builder when relationship is discovered, but no FK is found.
<navigation property name><principal key property name> or <principal key property name>

Data Annotations – NO

Fluent API:

modelBuilder.Entity<Person>()
    .Property<DateTime>("LastUpdated");

NB! If the name supplied to the Property method matches the name of an existing property (a shadow property or one defined on the entity class), then the code will configure that existing property rather than introducing a new shadow property.

Including and Excluding Types

Convention:

By convention, types that are exposed in DbSet<> properties on your context are included in your model. In addition, types that are mentioned in the OnModelCreating method are also included. Finally, any types that are found by recursively exploring the navigation properties of discovered types are also included in the model.

Data Annotations:

[NotMapped] on top of Model class

Fluent API:

modelBuilder.Ignore<PersonMetadata>();

Including and Excluding Properties

Convention:

By convention, public properties with a getter and a setter will be included in the model.

Data Annotations:

[NotMapped] on top of property

Fluent API:

modelBuilder.Entity<Person>()
    .Ignore(p => p.LoadedFromDatabaseDateTime);

Relationships

One-to-many, one-to-one, many-to-many

Terms used:

Dependent entity – child of the relationship. FK is here.
Principal entity – parent of the relationship. PK is here.
Foreign key – property in dependent entity, stores PK value of principal
Principal key – property, that uniquely identifies principal entity. PK.
Navigation property – contains reference(s) to related entity(s).
- Collection navigation property – navigation property, can contain many
- Reference navigation property – can only contain single
- Inverse navigation property – refers to navigation property on the other end of relationship

Convention

Relationship will be created when there is a navigation property discovered on a type. A property is considered a navigation property if the type it points to can not be mapped as a scalar type by the current database provider (collections, classes).

Relationships that are discovered by convention will always target the primary key of the principal entity. To target an alternate key, additional configuration must be performed using the Fluent API.

Fully Defined Relationships

The most common pattern for relationships is to have navigation properties defined on both ends of the relationship and a foreign key property defined in the dependent entity class.

If the dependent entity contains a property named
<primary key property name> or <navigation property name><primary key property name> or <principal entity name><primary key property name>
then it will be configured as the foreign key

If there are multiple navigation properties defined between two types (that is, more than one distinct pair of navigations that point to each other), then no relationships will be created by convention and you will need to manually configure them to identify how the navigation properties pair up.

No Foreign Key Property

While it is strongly recommended to have a foreign key property defined in the dependent entity class, it is not required. If no foreign key property is found, a shadow foreign key property will be introduced with the name <navigation property name><principal key property name>

Cascade Delete

By convention, cascade delete will be set to Cascade for required relationships and ClientSetNull for optional relationships.

Cascade means dependent entities are also deleted.

ClientSetNull means that dependent entities that are not loaded into memory will remain unchanged and must be manually deleted, or updated to point to a valid principal entity. For entities that are loaded into memory, EF Core will attempt to set the foreign key properties to null.

Data Annotations
[ForeignKey]

You can use the Data Annotations to configure which property should be used as the foreign key property for a given relationship
Typically used, when foreign key property is not discovered by convention
Annotation can be placed on either navigation property in the relationship. It does not need to go on the navigation property in the dependent entity class

Data Annotations
[InverseProperty]

Used when there is more than one pair of navigation properties between two entity types.

public class Post{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int AuthorUserId { get; set; }
    public User Author { get; set; }
    public int ContributorUserId { get; set; }
    public User Contributor { get; set; }
}

public class User{
    public string UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    [InverseProperty("Author")]
    public List<Post> AuthoredPosts { get; set; }
    [InverseProperty("Contributor")]
    public List<Post> ContributedToPosts { get; set; }
}

Fluent API:

To configure a relationship in the Fluent API, you start by identifying the navigation properties that make up the relationship.
HasOne or HasMany identifies the navigation property on the entity type you are beginning the configuration on.
You then chain a call to WithOne or WithMany to identify the inverse navigation.
HasOne/WithOne are used for reference navigation properties and HasMany/WithMany are used for collection navigation properties

modelBuilder.Entity<Contact>()
    .HasOne(p => p.Person)
    .WithMany(c => c.Contacts)

Single navigation property

If you only have one navigation property then there are parameterless overloads of WithOne and WithMany. This indicates that there is conceptually a reference or collection on the other end of the relationship, but there is no navigation property included in the entity class.

Fluent API:

modelBuilder.Entity<Person>()
    .HasMany(b => b.Contacts)
    .WithOne();

Foreign Key (can be composite)

Fluent API:

modelBuilder.Entity<Contact>()
    .HasOne(p => p.Person)
    .WithMany(c => c.Contacts)
    .HasForeignKey(p => p.PersonWeirdNamedForeignKey);

You can use the string overload of HasForeignKey(...) to configure a shadow property as a foreign key

Principal Key (can be composite)

If you want the foreign key to reference a property other than the primary key, you can use the Fluent API to configure the principal key property for the relationship. The property that you configure as the principal key will automatically be setup as an alternate key.

Fluent API:

modelBuilder.Entity<Contact>()
    .HasOne(p => p.Person)
    .WithMany(c => c.Contacts)
    .HasForeignKey(c => c.PersonSocialSecurityCode)
    .HasPrincipalKey(p => p.SocialSecurityCode);

Required and Optional Relationships

This controls whether the foreign key property is required or optional. This is most useful when you are using a shadow state foreign key.

Fluent API:

modelBuilder.Entity<Contact>()
    .HasOne(p => p.Person)
    .WithMany(c => c.Contacts)
    .IsRequired();

One-to-one – Fluent API

One to one relationships have a reference navigation property on both sides. They follow the same conventions as one-to-many relationships, but a unique index is introduced on the foreign key property to ensure only one dependent is related to each principal
EF will choose one of the entities to be the dependent based on its ability to detect a foreign key property. If the wrong entity is chosen as the dependent, you can use the Fluent API to correct this.
When configuring the relationship with the Fluent API, you use the HasOne and WithOne methods.

Relationships – 1:0-1

FK in Principal entity

public class PrimaryOne
{
    public int Id { get; set; }
    [MaxLength(128)]
    [MinLength(1)]
    public string Value { get; set; } = default!;
    // 1:0-1, Fk in principal entity
    public int ChildOneId { get; set; }
    public ChildOne? ChildOne { get; set; }
}

public class ChildOne
{
    public int Id { get; set; }
    [MaxLength(128)]
    [MinLength(1)]
    public string Value { get; set; } = default!;
    public PrimaryOne? PrimaryOne { get; set; }
}

FK in Dependent entity

public class PrimaryTwo
{
    public int Id { get; set; }
    [MaxLength(128)]
    [MinLength(1)]
    public string Value { get; set; } = default!;
    // 1:0-1, Fk in dependent entity
    public ChildTwo? ChildTwo { get; set; }
}

public class ChildTwo
{
    public int Id { get; set; }
    [MaxLength(128)]
    [MinLength(1)]
    public string Value { get; set; } = default!;
    public int PrimaryTwoId { get; set; }
    public PrimaryTwo? PrimaryTwo { get; set; }
}

Many-to-many

Supported in EF6 (EF6 creates the necessary in-between-table)
Supported also in EF Core 6 - not really recommended
Use two one-to-many relations instead

Indexes

Convention

On every property that is used as foreign key (FK)

Data Annotations – Starting with EF Core 6

on top of model class

Single property, non-unique

1	`[Index(nameof(FirstName))]`

- Multiple properties, non-unique

1	`[Index(nameof(FirstName), nameof(LastName))]`

Unique

[Index(nameof(FirstName), IsUnique = true)]
[Index(nameof(FirstName), nameof(LastName), IsUnique = true)]

Fluent API:

Single property, default non-unique

modelBuilder.Entity<Person>() 
    .HasIndex(b => b.FirstName);

Single property, unique

modelBuilder.Entity<Person>() 
    .HasIndex(b => b.FirstName).IsUnique();

More than one property

modelBuilder.Entity<Person>() 
    .HasIndex(p => new { p.FirstName, p.LastName });

More than one property, unique

modelBuilder.Entity<Person>() 
    .HasIndex(p => new { p.FirstName, p.LastName }).IsUnique();

Table Mapping

Table mapping identifies which table data should be queried from and saved to in the database.

Convention

By convention, each entity will be set up to map to a table with the same name as the DbSet<TEntity> property that exposes the entity on the derived context. If no DbSet<TEntity>is included for the given entity, the class name is used.

Data Annotations – on top of class
[Table("blogs", Schema = "blogging")]

Fluent API:

modelBuilder.Entity<Blog>()
    .ToTable("blogs", schema: "blogging");

Column Mapping

Column mapping identifies which column data should be queried from and saved to in the database.

Convention

Each property will be set up to map to a column with the same name as the property.

Data Annotations – on top of property
[Column("person_id")]

Fluent API:

modelBuilder.Entity<Person>()
    .Property(b => b.PersonId)
    .HasColumnName("person_id");

Data types

Convention

By convention, the database provider selects a data type based on the CLR type of the property. It also takes into account other metadata, such as the configured Maximum Length, whether the property is part of a primary key, etc.

Data Annotations
[Column(TypeName = "varchar(200)")]
[Column(TypeName = "decimal(5, 2)")]

Fluent API:

modelBuilder.Entity<Person>(p => {
    p.Property(b => b.FirstName).HasColumnType("varchar(200)");
    p.Property(b => b.Score).HasColumnType("decimal(5, 2)");
});

Default values

Fluent API only:

modelBuilder.Entity<Person>()
    .Property(b => b.Popularity)
    .HasDefaultValue(10);

Using SQL fragment:

modelBuilder.Entity<Contact>()
    .Property(b => b.CreatedAtDateTime)
    .HasDefaultValueSql("getdate()");

Advanced topics

Concurrency Tokens
Inheritance
Value Conversions
Entity Type Constructors
Owned Entity Types
Query Types (Views)
Spatial Data Types (GIS)
Computed Columns
Sequences
Default Schema (MS SQL)
Naming: Indexes, Foreign Key Constraints, Alternate Keys, PKs
Keyless entities
Views
Raw SQL