Detailed EntityFramework Core mapping relationships

Preface

Hello, get back to updating one or two blogs a week. In this section, we're going back to the EF Core basics to tell you how EF Core really maps. Say nothing, let's start.

One-Many Relationship

First of all, let's start with the simplest one-to-many relationship. We give two classes that need to be mapped, one is Blog and the other is Post, as follows:

public class Blog
{
    public int Id { get; set; }
    public int Count { get; set; }
    public string Name { get; set; }
    public string Url { get; set; }
    public IEnumerable<Post> Posts { get; set; }

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

    public virtual int BlogId { get; set; }
    public virtual Blog Blog { get; set; }
}

From the point of view of a Blog, there are several posts under a Blog, and one Post belongs to only one Blog. The configuration relationship is as follows:

 public class BlogMap : EntityMappingConfiguration<Blog>
{
    public override void Map(EntityTypeBuilder<Blog> b)
    {
        b.ToTable("Blog");
        b.HasKey(k => k.Id);

        b.Property(p => p.Count);
        b.Property(p => p.Url);
        b.Property(p => p.Name);
        b.HasMany(p => p.Posts)
            .WithOne(p => p.Blog)
            .HasForeignKey(p => p.BlogId);
    }
}

Post is as follows:

public class PostMap : EntityMappingConfiguration<Post>
{
    public override void Map(EntityTypeBuilder<Post> b)
    {
        b.ToTable("Post");
        b.HasKey(k => k.Id);
        b.Property(p => p.Title);
        b.Property(p => p.Content);
    }
}

At this point, we use SqlProfiler to monitor the generated SQL statements.The following:

CREATE TABLE [Blog] (
    [Id] int NOT NULL IDENTITY,
    [Count] int NOT NULL,
    [Name] nvarchar(max),
    [Url] nvarchar(max),
    CONSTRAINT [PK_Blog] PRIMARY KEY ([Id])
);
CREATE TABLE [Post] (
    [Id] int NOT NULL IDENTITY,
    [BlogId] int NOT NULL,
    [Content] nvarchar(max),
    [Title] nvarchar(max),
    CONSTRAINT [PK_Post] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Post_Blog_BlogId] FOREIGN KEY ([BlogId]) REFERENCES [Blog] ([Id]) ON DELETE CASCADE
);

At this point, we can clearly see that the foreign key BlogId is established for BlogId on the Post table, that is, the primary key Id on the corresponding Blog table, and the DELETE CASADE is identified for cascade deletion, that is, when the data on the Blog is deleted, the corresponding data on the Post table will be deleted accordingly.At the same time, when the SQL statement is generated, an index is also created for BlogId on Post, as follows:

CREATE INDEX [IX_Post_BlogId] ON [Post] ([BlogId]);

As you know, EF Core creates its index by default for a foreign key in a one-to-many relationship, where the index must be non-unique, non-clustered, and the clustered index is its primary key.We can see from the database as follows:

At this point, even if we do not configure the specified foreign key to be BlogId, there is nothing wrong with it, as follows:

b.HasMany(m => m.Posts).WithOne(o => o.Blog);

Because we have explicitly written out the BlogId above, but EF Core can still specify the BlogId as a foreign key for it. Now let's think, instead, if we delete the BlogId in Post, would it be good to do the same mapping, which has been verified to be possible as follows:


Don't jump to conclusions, let's look at one more scenario. Would it still work if we configure and remove BlogId from Post as follows?

b.HasMany(m => m.Posts);

It's also good to be clinically certified to correctly express what we want and automatically add the foreign key BlogId column, so here we can draw a conclusion for a one-to-many relationship:

One-to-many relationship conclusion

In a one-to-many relationship, we can explicitly specify a foreign key column by mapping, or we can not specify it, because EF Core internal will find if its foreign key column has been specified, then directly use the specified, if not, automatically generate a foreign key column, column name is the class name + Id where the foreign key column is located.At the same time, for a one-to-many relationship, we can configure mappings directly using the HasMany method without having to configure HasOne or WithOne anymore, all of which configure mappings from a positive perspective because they are easy to understand and vice versa.

One-One RelationShip (one-to-one relationship)

For a slightly more complex one-to-one relationship and many-to-many relationship, let's break through each one. For example, a product belongs to only one category, while a category has only one product, as follows:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }

    public Category Category { get; set; }
}
public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ProductId { get; set; }
    public Product Product { get; set; }
}

Now let's take a one-to-one relationship mapping from a product perspective:

public class ProductMap : EntityMappingConfiguration<Product>
{
    public override void Map(EntityTypeBuilder<Product> b)
    {
        b.ToTable("Product");
        b.HasKey(k => k.Id);

        b.HasOne(o => o.Category).WithOne(o => o.Product);
    }
 }

At this point, we have already made the following errors by initializing dotnet ef migrations add Initial:

The general meaning is that it is not clear who the Product s and Categories are dependencies, and it is not explicitly specified that caused the above error.This is not the case for a one-to-many relationship. It is not difficult to find out who is the principal after careful analysis, but for a one-to-one relationship, EF Core cannot determine the principal, so we must specify it manually.At this point, if we specify as follows, you will find that there is no lambda expression prompt:

 b.HasOne(o => o.Category)
                .WithOne(o => o.Product)
                .HasForeignKey(k=>k.)

Or because of principal relationships, we have to specify generic parameters before we can.As follows:

 b.HasOne(o => o.Category)
                .WithOne(o => o.Product)
                .HasForeignKey<Category>(k => k.ProductId);

The ProductId foreign key is created on the Category and the following unique non-clustered index is created on the ProductId:

CREATE UNIQUE INDEX [IX_Category_ProductId] ON [Category] ([ProductId]);

Many-Many RelationShip

Many-to-many relationships have been directly used in previous versions of EF Core, such as HasMany-WithMany, but there is no longer a corresponding method in EF Core. Consider whether many-to-many relationships can be obtained through one-to-many, for example, if a product belongs to more than one category, and a classification corresponds to more than one product, a typical many-to-many relationship.But our description can be mapped from a one-to-many relationship. Let's take a look at it:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }

    public IEnumerable<ProductCategory> ProductCategorys { get; set; }
}
public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }

    public int ProductId { get; set; }
    public IEnumerable<ProductCategory> ProductCategorys { get; set; }
}
public class ProductCategory
{
    public int ProductId { get; set; }
    public Product Product { get; set; }

    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

The third associated class, ProductCategory, is given above, which associates Product (Product Class) with Category (Category Class) to the ProductCategory class. Finally, we map through ProductCategory, as follows:

public class ProductCategoryMap : EntityMappingConfiguration<ProductCategory>
{
    public override void Map(EntityTypeBuilder<ProductCategory> b)
    {
        b.ToTable("ProductCategory");

        b.HasKey(k => k.Id);

        b.HasOne(p => p.Product)
            .WithMany(p => p.ProductCategorys)
            .HasForeignKey(k => k.ProductId);

        b.HasOne(p => p.Category)
            .WithMany(p => p.ProductCategorys)
            .HasForeignKey(k => k.CategoryId);
    }
}

Okay, so far, we've finished describing the three mapping relationships. Is that the end, far from it? Let's go back to the other attribute mappings.

Key Mapping

Foreign key mappings in key mappings have been discussed above, but let's talk about mappings for other types of keys.

Alternate Key/Optional Key Mapping

Alternate keys/alternative keys can configure unique identities for an entity class other than primary keys, such as a user's unique identity in a login. In addition to primary key identities, we can configure alternative keys for UserName at this time, for example, in a scenario where a user can only purchase one book.Configure a primary key and a user Id in the Book table (examples that, while inappropriate, describe scenarios where optional keys can be used well)

public class Book
{
    public int Id { get; set; }
    public string UserId { get; set; }
}

Here we configure user Id mapping by using optional keys

public class BookMap : EntityMappingConfiguration<Book>
{
    public override void Map(EntityTypeBuilder<Book> b)
    {
        b.ToTable("Book");
        b.HasKey(k => k.Id);
        b.HasAlternateKey(k => k.UserId);
    }
}

Finally, the following statements are monitored:
No, the unique constraint is configured for user Id:

CONSTRAINT [AK_Book_UserId] UNIQUE ([UserId])

So we conclude that with optional keys we can create unique constraints to uniquely identify rows other than the primary key.

Principal Key

If we want a foreign key to reference an attribute instead of a primary key, we can configure it through the principal key mapping, where it is actually automatically set as an optional key behind the configuration principal key mapping.Let's not talk more about this.

Okay, so far we've finished key mapping, and now we'll talk about attribute mapping:

Attribute Mapping

If we do not configure the string type in C#then the default setting in the database is NVARCHAR and the length is MAX and is nullable, as follows:
If we need to set its length to be non-empty, we need to configure it as follows:

b.Property(p => p.Name).IsRequired().HasMaxLength(50);

The maximum length is specified by the HaxMaxLength method and non-null by the IsRequired method.But now the question arises: database types have VARCHAR, CHAR, NCAHR types for string s, so how should we map them?For example, for VARCHAR types, we can map database column types in EF Core using the HasColumnType method. Assuming the VARCHAR type is 50 length and not empty, can we map as follows?

 b.Property(p => p.Name)
        .IsRequired()
        .HasColumnType("VARCHAR")
        .HasMaxLength(50);


Through the above migration errors, we modified the following to be correct:

b.Property(p => p.Name)
                .IsRequired()
                .HasColumnType("VARCHAR(50)");

Solve one, and then another, then how do we map the enumeration types, which correspond to TINYINT type in the database, and we set the following settings:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Type Type { get; set; }
    public IEnumerable<ProductCategory> ProductCategorys { get; set; }
}

public enum Type
{
    [Description("ordinary")]
    General = 0,
    [Description("Insurance")]
    Insurance = 1
}
public class ProductMap : EntityMappingConfiguration<Product>
{
    public override void Map(EntityTypeBuilder<Product> b)
    {
        b.ToTable("Product");
        b.HasKey(k => k.Id);

        b.Property(p => p.Type)
            .IsRequired()
            .HasColumnType("TINYINT");
    }
}

This generates the type we want:

CREATE TABLE [Product] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(max),
    [Type] TINYINT NOT NULL,
    CONSTRAINT [PK_Product] PRIMARY KEY ([Id])

[Note] Mapping it to an enumeration at this time is okay as demonstrated above, but when we get the data, the following error will occur when we convert TINYINT to an enumeration:

After all, TINYINT's corresponding byte class in C#will fail if it finally attempts to convert it to int, so remember to inherit the enumeration from byte when defining it, as follows:

public enum Type : byte
{
    [Description("ordinary")]
    General = 0,
    [Description("Insurance")]
    Insurance = 1
}

Once you've finished mapping as above, let's talk about default mapping.When we hit the default mapping we will find two, one HasDefaultValue and the other HasDefaultValueSql. Let's see how to use it:

We add the Count field to the Product class:

 public int Count { get; set; }
b.Property(p => p.Count).HasDefaultValue(0);


If it's an enumeration type, let's try the following:

 b.Property(p => p.Type)
                .IsRequired()
                .HasColumnType("TINYINT").HasDefaultValue(0);

The following errors will occur during migration:

That is, it is not possible to set the enumeration value to type int, so we should use HasDefaultValueSql to map:

  b.Property(p => p.Type)
                .IsRequired()
                .HasColumnType("TINYINT").HasDefaultValueSql("0");

To summarize the default mapping, use HasDefaultValue if the type in C#is identical to the database type, or HasDefaluValueSql if it is not.

[Note] There is a special case for field type mapping, and for date type DateTime, there is a corresponding type of datetime in the database, but if we do not specify the type manually, it will default to a more precise date type, datetime2(7).

We add a creation time column to the Product class as follows:

public DateTime CreatedTime { get; set; }

At this point we don't specify its mapping type, and we see that the type in the database is datetime2(7)

Of course, the above mappings are okay, but for most date types, they are mapped to datetime and given a default time of the current time, so you need to configure them manually at this time, as follows:

b.Property(p => p.CreatedTime)
                .HasColumnType("DATETIME")
                .HasDefaultValueSql("GETDATE()");

After discussing the default values, let's talk about the mapping of computed columns. In EF Core, the mapping of computed columns was ForSqlServerHasComputedColumnSql in the previous version and now HasComputedColumnSql.For example, here is the computed column:

 b.Property(p => p.Name)
                .IsRequired()
                .HasComputedColumnSql("((N'Cnblogs'+CONVERT([CHAR](8),[CreatedTime],(112)))+RIGHT(REPLICATE(N'0',(6))+CONVERT([NVARCHAR],[Id],(0)),(6)))");


There are also methods for column name customization (HasColumnName), whether primary keys are automatically generated (ValueGeneratedOnAdd), and row versions (IsRowVersion) and concurrent Tokens (IsConcurrencyToken).There is also a way to set an index, HasIndex

There is a question here about the default setting for string is NVARCHAR, which is unicode. I don't know why there is another IsUnicode method, which is not also set to NVARCHAR. What is the situation?Solve, when we set both IsUnicode method and column type to VARCHAR, NVARCHAR will still be generated. It can be seen that mapping to NVARCHAR has a higher priority than VARCHAR, as follows

 b.Property(p => p.Name)
                .IsRequired().IsUnicode()
                .HasColumnType("VARCHAR(21)")
                .HasComputedColumnSql("((N'Cnblogs'+CONVERT([CHAR](8),[CreatedTime],(112)))+RIGHT(REPLICATE(N'0',(6))+CONVERT([NVARCHAR],[Id],(0)),(6)))");

summary

This article provides a brief introduction to mapping in EF Core and some minor points of attention.

Entity Framework Core - Create and Configure Models - Overview

The Entity Framework uses a set of conventions to build models based on the shape of entity classes.Additional configurations can be specified to supplement and/or replace the agreed content.

This article describes configurations that can be applied to any data store-oriented model and to any relational database.Providers can also support configurations specific to specific data stores.

1. Configuring models using fluent API

You can replace the OnModelCreating method in a derived context and configure the model using the ModelBuilder API.This configuration method is the most efficient and allows you to specify a configuration without modifying the entity class.Fluent API configuration has the highest priority and will replace conventions and data annotations.

using Microsoft.EntityFrameworkCore;

namespace EFModeling.FluentAPI.Required
{
    class MyContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        #region Required
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>()
                .Property(b => b.Url)
                .IsRequired();
        }
        #endregion
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Url { get; set; }
    }
}

Grouping Configuration

To reduce the size of the OnModelCreating method, all configurations of entity types can be extracted into a single class that implements IEntityTypeConfiguration <TEntity>

public class BlogEntityTypeConfiguration : IEntityTypeConfiguration<Blog>
{
    public void Configure(EntityTypeBuilder<Blog> builder)
    {
        builder
            .Property(b => b.Url)
            .IsRequired();
    }
}

Then, simply call the Configure method from OnModelCreating:

new BlogEntityTypeConfiguration().Configure(modelBuilder.Entity<Blog>());

All configurations specified in the type that implements the IEntityTypeConfiguration can be applied in a given assembly:

modelBuilder.ApplyConfigurationsFromAssembly(typeof(BlogEntityTypeConfiguration).Assembly);

2. Configuring models using data annotations

Attributes, known as data annotations, can also be applied to classes and attributes.Data annotations replace conventions but are replaced by Fluent API configurations:

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;

namespace EFModeling.DataAnnotations.Required
{
    class MyContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
    }

    #region Required
    public class Blog
    {
        public int BlogId { get; set; }
        [Required]
        public string Url { get; set; }
    }
    #endregion
}

Tags: Database

Posted on Sat, 04 Sep 2021 12:31:27 -0400 by echo64