Entity Framework code first learning diary (7)-many-to-many relationship

In the last diary, we discussed in detail how Entity Framework code first establishes a one-to-many relationship between tables. In this diary, we will introduce in detail the default behavior of the Entity Framework code first to establish many-to-many relationships, and how to change the default behavior through the fluent API.

This diary mainly introduces the following content:

1. Under what circumstances will Entity Framework code first establish multi-to-many relationships between tables and the default configuration for establishing multi-to-many relationships.

2. How to Use the fluent API to change the default configuration when the Entity Framework code first establishes many-to-many relationships.

 

1. Under what circumstances will Entity Framework code first establish multi-to-many relationships between tables and the default configuration for establishing multi-to-many relationships.

Assuming that our business field has changed again, the customer asked us to record the discount information for each product. Based on our conversations with customers, we can obtain the following business logic:

All discount promotion records related to each product type must be retained. Each discount promotion has a fixed start time, end time, product list, and discount rate.

We rewrote our productcatalog class based on changes in this business field:

 

public class ProductCatalog    {        public int ProductCatalogId { get; set; }        public string CatalogName { get; set; }        public string Manufactory { get; set; }        public decimal ListPrice { get; set; }        public decimal NetPrice { get; set; }        public List<Product> ProductInStock { get; set; }        public List<SalesPromotion> SalesPromotionHistory { get; set; }        public ProductCatalog()        {            SalesPromotionHistory = new List<SalesPromotion>();            ProductInStock = new List<Product>();        }        public Product GetProductInStock()        {            if (ProductInStock.Count <= 0)            {                throw new Exception("No product in stock");            }            Product product = ProductInStock[0];            ProductInStock.RemoveAt(0);            return product;        }        public void PurchaseProduct(List<Product> products)        {            ProductInStock.AddRange(products);        }        public void PurchaseProduct(List<Product> products,decimal newListPrice,decimal newNetPrice)        {            ProductInStock.AddRange(products);            ListPrice = newListPrice;            NetPrice = newNetPrice;        }        public decimal GetPromotionPrice()        {            if (SalesPromotionHistory.Count <= 0)            {                return ListPrice;            }            decimal listPrice = 0;            foreach (var promotion in SalesPromotionHistory)            {                if (promotion.StartDate <= DateTime.Now && promotion.EndDate >= DateTime.Now)                {                    listPrice = ListPrice * promotion.SalesDiscount;                }            }            return listPrice;        }    }

 

We also defined our salespromotion Class Based on the business field.

public class SalesPromotion    {        public int SalesPromotionId { get; set; }        public DateTime StartDate { get; set; }        public DateTime EndDate { get; set; }        public decimal SalesDiscount { get; set; }        public List<ProductCatalog> PromotionProductCatalog { get; set; }        public SalesPromotion()        {            PromotionProductCatalog = new List<ProductCatalog>();        }        public void AddProductCatalogToPromotion(ProductCatalog catalog)        {            catalog.SalesPromotionHistory.Add(this);            PromotionProductCatalog.Add(catalog);        }    }

 

We can write a unit test method to test what kind of data table relationship will be created based on the definition of the Entity Framework code first class.

[TestMethod]        public void CanAddSalesPromotion()        {            OrderSystemContext unitOfWork = new OrderSystemContext();            ProductRepository repository = new ProductRepository(unitOfWork);            ProductCatalog catalog = repository.GetProductCatalogById(1);            SalesPromotion promotion = new SalesPromotion { StartDate = DateTime.Parse("2013-1-18"), EndDate = DateTime.Parse("2013-1-25"), SalesDiscount = 0.75M };            promotion.AddProductCatalogToPromotion(catalog);            unitOfWork.CommitChanges();        }

 

Let's open the database and take a look at the database structure mapped from the class definition of Entity Framework code first?

 

We can see that our salespromotion and productcatalog classes both contain the instance set of the other class. In this case, by default, Entity Framework code first maps the reference relationships between classes to the many-to-many relationships between database tables. Entity Framework will create a multi-to-many connection table. The table name is the names of two associated tables, and then add the plural form to you according to its own rules. The two fields in the Table reference both the Foreign keys of the two associated tables and serve as the joint primary keys of the new connected tables.

However, you may find that the name of the connection table and the name of the fields in the connection table are defined by Entity Framework code first according to your own rules. We can modify them.

 

 

2. How to Use the fluent API to change the default configuration when the Entity Framework code first establishes many-to-many relationships

We can configure the name of the connection table and the names of the two foreign key columns in the connection table through the has and with methods introduced in the previous diary.

public class SalesPromotionEntityConfiguration:EntityTypeConfiguration<SalesPromotion>    {        public SalesPromotionEntityConfiguration()        {            Property(p => p.SalesDiscount).HasPrecision(18, 4);            HasMany(p => p.PromotionProductCatalog)                .WithMany(ca => ca.SalesPromotionHistory)                .Map(r =>                    {                        r.ToTable("ProductCatalogSalesPromotion");                        r.MapLeftKey("PromotionId");                        r.MapRightKey("CatalogId");                    }            );        }    }

 

Because we configure the database structure generated by many-to-many relationships, we need to use hasmany withmany. Then, we can use the map method to specify the name of the connection table for multiple-to-multiple connections and the names of the two joint primary keys (that is, the foreign keys) in the connection table.

We re-execute our unit test program. We can find that the names of the newly created connection tables and columns are the names we set.

 

The next diary is the last article of the Entity Framework code first ing relationship between data tables: one-to-one relationship.