06 - Entity Framework

Logging

Add simple logging, to see generated SQL statements on console.
Nuget install Microsoft.Extensions.Logging.Console
Define LoggerFactory

public class ApplicationDbContext : IdentityDbContext
{
    private static readonly ILoggerFactory MyLoggerFactory =
        LoggerFactory.Create(builder =>
        {
            builder
                .AddFilter("Microsoft", LogLevel.Debug)
                .AddFilter("System", LogLevel.Debug)
                .AddConsole();
        });

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder
            .UseLoggerFactory(MyLoggerFactory)
            .UseSqlServer(
                @"Server=(localdb)\mssqllocaldb;" + // server to use
                "Database=MyDatabase;" + // database to use or create
                "Trusted_Connection=True;" + // no credentials needed, local sql instance
                "MultipleActiveResultSets=true" // allow multiple parallel queries
            );
    }

    public DbSet<Contact> Contacts { get; set; }
    public DbSet<ContactType> ContactTypes { get; set; }
    public DbSet<Entry> Entries { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
}

Migrations

Console command line

dotnet ef migrations add InitialDbCreation
dotnet ef database update
dotnet ef database drop

From code

myDbContext.Database.Migrate();

Data access

Eager, Explicit, Lazy

Eager – load all data at once
Explicit – load data, when needed
Lazy – load data automatically when accessing navigation property, without doing specific db operations

Lazy - not recommended. 1+N query problem.

Eager

Eager – specify data to include in query results (SQL JOIN)

.Include(table => table.ListOfRelatedEntity)

Eager – include several relationships

.Include(table => table.ListOfRelatedEntity)
.Include(table => table.ListOfSomeOtherRelatedEntity)

Eager – include multiple levels

.Include(table => table.ListOfRelatedEntityA)
    .ThenInclude(RelatedEntityA => table.ListOfSomeOtherRelatedEntityBFromEntityA)

Primary Key

Use conventions!

<ClassName>Id or just Id

Annotations
[Key]

Fluent API

modelBuilder.Entity<ModelClass>()
    .HasKey(c => c.KeyProperty);

Composite key – only in Fluent

modelBuilder.Entity<ModelClass>()
    .HasKey(c => new { c.KeyProperty1, c.KeyProperty2 });

Required/Optional

If property is nullable – by convention not required

Annotations
[Required]

Fluent API

modelBuilder.Entity<ModelClass>()
    .Property(b => b.SomeProperty).IsRequired();

Maximum length

• By convention depends on the provider
• Annotation [MaxLength(<Length>)]
• Fluent API
  modelBuilder.Entity<ModelClass>().Property(b => b.SomeProperty).HasMaxLength(<Length>);

Indexes

• Index is created for every foreign key

• Annotations
  On top of entity class

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

• Fluent API
  • Single property, non-unique
    modelBuilder.Entity<ModelClass>().HasIndex(b => b.SomeProperty)
  • Unique
    modelBuilder.Entity<ModelClass>().HasIndex(b => b. SomeProperty) .IsUnique();
  • More than one property
    modelBuilder.Entity<ModelClass>().HasIndex( p => new { p. * SomeProperty1, p. SomeProperty2 });

Default value

Danger

Fluent API only!

modelBuilder.Entity<ModelClass>().Property(b => b.SomeNumber).HasDefaultValue(3);

modelBuilder.Entity<ModelClass>().Property(b => b.SqlProperty).HasDefaultValueSql("getdate()");

Exclude properties

• You can exclude types or properties from DB
• Annotations [NotMapped]
• Fluent API (in dbcontext.OnModelCreating)
  modelBuilder.Ignore<ModelClass>(); modelBuilder.Entity<ModelCalss>().Ignore(b => b.ModelProperty);
• Getter only properties are excluded automatically

Relationships

• If possible, use fully defined relationships.
  • Navigation property defined on both ends, FK defined in dependent entity
  • If there is only one relationship between two entities, EF will set up relationship automatically
• Some configuration is needed on other cases
• Data annotations
  • [ForeignKey]
  • [InverseProperty]

Foreign Key

[ForeignKey] – when FK is not discovered.

[ForeignKey(nameof(Post.BlogForeignKey))]

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogForeignKey { get; set; }

    [ForeignKey("BlogForeignKey")]
    public Blog Blog { get; set; }
}

Inverse Property

[InverseProperty] - Used, when there are more than one relationships defined between two entities

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.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasOne(p => p.Blog)
        .WithMany(b => b.Posts);
}

Cascade delete

Tip

Cascade delete is automatically configured.

The default EF Core convention is to use DeleteBehavior.Cascade for required and DeleteBehavior.ClientSetNull for optional relationships.

You can turn of cascade delete for all entities (in DbContext):

protected override void OnModelCreating(ModelBuilder modelBuilder){
    base.OnModelCreating(modelBuilder);

    foreach (var relationship in modelBuilder.Model
        .GetEntityTypes()
        .Where(e => !e.IsOwned())
        .SelectMany(e => e.GetForeignKeys()))
    {
        relationship.DeleteBehavior = DeleteBehavior.Restrict;
    }
}

Preexisting DB

Command prompt

dotnet ef dbcontext scaffold "server=alpha.akaver.com;database=databasename;user=user password=pass" 
MySql.Data.EntityFrameworkCore 
--data-annotations 
--context AppDbContext
--output-dir Models  
--context-dir  
--project DAL 
--startup-project ConsoleApp1