Entity Framework code first learning diary (10)-compatible with legacy databases

Before writing this diary, you must apologize. Because I have been busy with new projects recently, I haven't updated it for more than a month.

This diary will detail how to map classes in the performance field to existing databases. The current economic situation is not very good. Many companies have canceled the budget for developing new systems. In this case, the common practice is to make some modifications to the original systems for integration. If you want to improve the functions of the original system, you must reuse the original database structure and make some minor improvements. In this case, how can we use code first? We can create classes in the domain according to the business logic of the original system and the new business logic in the CR (change request), and then map these classes to the tables in the original database using the fluent API, or modify the data structure of the original system. I believe that through the previous diaries, we have learned how to customize database ing in various aspects. Although this method gives us the opportunity to review our business logic, many unchanged parts of the original system also need to create classes and map them to the original data table according to the code first method, it brings a lot of unnecessary work to our development. To improve our development efficiency and reduce the unnecessary workload when using code first to upgrade the system, Microsoft invented Entity Framework power tools. this tool can use reverse engineering to generate the ing configuration of classes and databases that represent domain in code first based on the original database. There are many introductions on this tool in the blog garden. You can search for this tool in the blog garden. Although this tool is good, it can only help you reduce your workload and cannot completely replace your work. For example, if there is a table in the original database, you can analyze the business logic and find that the fields in the table should belong to two classes, but you cannot change the existing database structure, in this case, you need to configure the database ing yourself. Or the two tables in the original database belong to the same class in your new business logic, and configure database ing based on the specific business logic. So today I will mainly introduce how to solve the above two problems.

1. How to map two classes to the same database table without modifying the database structure.

2. How to map a class to two database tables without modifying the database structure.

 

1. How to map two classes to the same database table without modifying the database structure.

We have already introduced a method to map two classes into a table: complextype. Today we will introduce another method.

Assume that our original database has a customer table with the following structure:

We used code first when upgrading and transforming the existing system. Please note that this table is marked with a red box. In terms of business logic, it is not a part of customer information, but the customer's bank account information. Based on the actual situation in the business logic, we have created two classes: customer and bankaccount.

Customer class:

public class Customer{      public string IDCardNumber { get; set; }      public string CustomerName { get; set; }      public string Gender { get; set; }      public Address Address { get; set; }      public string PhoneNumber { get; set; }      public BankAccount Account { get; set; }}

Bankaccount class:

public class BankAccount    {        public string AccountNumber { get; set; }        public DateTime CreatedDate { get; set; }        public string BankName { get; set; }        public string AccountName { get; set; }    }

If we need code first to map the two classes to the same table, the two must meet the following conditions:

1. The two classes must share the same primary key.

2. The relationship between the two classes must be mapped to a one-to-one relationship between the tables.

To meet these two conditions, we first need to modify the bankaccount class so that the bankaccount class uses the same primary key as the customer class.

public class BankAccount    {        public string IDCardNumber { get; set; }        public string AccountNumber { get; set; }        public DateTime CreatedDate { get; set; }        public string BankName { get; set; }        public string AccountName { get; set; }    }

We must also manually map the one-to-one relationship between the two classes in the customer database configuration class.

HasRequired(c => c.Account).WithRequiredDependent();

After the preceding two conditions are met, we must manually map the two conditions to the same table using the totable method.

public class CustomerEntityConfiguration:EntityTypeConfiguration<Customer>    {        public CustomerEntityConfiguration()        {            ToTable("Customers");            HasKey(c => c.IDCardNumber).Property(c => c.IDCardNumber).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None).HasMaxLength(50);            Property(c => c.IDCardNumber).HasMaxLength(20);            Property(c => c.CustomerName).IsRequired().HasMaxLength(50);            Property(c => c.Gender).IsRequired().HasMaxLength(1);            Property(c => c.PhoneNumber).HasMaxLength(20);            HasRequired(c => c.Account).WithRequiredDependent();        }    }

 

public class BankAccountValueObjectConfiguration: EntityTypeConfiguration<BankAccount>    {        public BankAccountValueObjectConfiguration()        {            ToTable("Customers");            HasKey(ba => ba.IDCardNumber).Property(ba => ba.IDCardNumber).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None).HasMaxLength(20);            Property(ba => ba.AccountNumber).IsRequired().HasMaxLength(50);            Property(ba => ba.AccountName).IsRequired().HasMaxLength(100);            Property(ba => ba.BankName).IsRequired().HasMaxLength(100);        }    }

 

Let's modify the unit test method we used to test whether code first maps these two classes to the same table:

 [TestMethod]        public void CanAddCustomerWithBankAccount()        {            OrderSystemContext unitOfWork = new OrderSystemContext();            CustomerRepository repository = new CustomerRepository(unitOfWork);            Customer newCustomer = new Customer() { IDCardNumber = "120104198106072518", CustomerName = "Alex", Gender = "M", PhoneNumber = "test" };            Address customerAddress = new Address { Country = "China", Province = "Tianjin", City = "Tianjin", StreetAddress = "Crown Plaza", ZipCode = "300308" };            BankAccount account = new BankAccount { IDCardNumber = "120104198106072518", AccountNumber = "2012001001", BankName = "ICBC", AccountName = "Alex", CreatedDate = DateTime.Parse("2012-1-21") };            newCustomer.Address = customerAddress;            newCustomer.Account = account;            repository.AddNewCustomer(newCustomer);            unitOfWork.CommitChanges();        }

 

After the test method is executed, open the database and check whether the database structure mapped by code first is the same as that of our original database.

By manually ing these two classes into the same table, our code can be reconstructed based on the business logic without modifying the original database structure.

 

2. How to map a class to two database tables without modifying the database structure

Assume that our original database has two tables: productcatalog and productcatalogphoto. The productcatalog table stores information about a product. When you add a new requirement, You need to display the photos and descriptions of the product. I don't know which of the following friends did not dare to change the productcatalog table. I directly added a productcatalogphoto table. Now, when we upgrade the system, we will face the following database structure:

However, as you can see, creating two classes from the productcatalog and productcatalogphoto tables is definitely not in line with the business logic. We need to create the productcatalog class, which should include both the information of a certain type of products, photos of such products, and descriptions of photos.

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 byte[] Photo { get; set; }        public string PhotoDescription { get; set; }       .............    }

 

Without changing the original database structure, we need to manually configure the productcatalog class to map the productcatalog and productcatalogphoto tables.

public class ProductCatalogEntityConfiguration:EntityTypeConfiguration<ProductCatalog>    {        public ProductCatalogEntityConfiguration()        {            this.Property(c => c.CatalogName).HasMaxLength(200).IsRequired();            this.Property(c => c.Manufactory).HasMaxLength(200);            this.Property(c => c.ListPrice).HasPrecision(18, 4);            this.Property(c => c.NetPrice).HasPrecision(18, 4);            this.HasKey(c => c.ProductCatalogId);            Map(c =>            {                c.Properties(p => new {p.CatalogName,p.Manufactory,p.ListPrice,p.NetPrice});                c.ToTable("ProductCatalog");            });            Map(c =>            {                c.Properties(p => new {p.Photo,p.PhotoDescription});                c.ToTable("ProductCatalogPhoto");            });        }    }

By using the map method, we first select the attributes of the class, and then map the selected Attribute list to a database table.

We add a new unit test in our test program to test whether Entity Framework code first persists this class instance to two tables.

        [TestMethod]        public void CanAddNewProductCatalogWithPhoto()        {            OrderSystemContext unitOfWork = new OrderSystemContext();            ProductRepository repository = new ProductRepository(unitOfWork);            ProductCatalog catalog = new ProductCatalog() { CatalogName = "DELL Laptop E6400", Manufactory = "DELL", ListPrice = 6000, NetPrice = 5000, Photo = new byte[] { 0 }, PhotoDescription = "E6400" };            repository.AddNewProductCatalog(catalog);            unitOfWork.CommitChanges();        }

 

After executing this unit test program, we can query it in the database to see if we want the result:

As you can see, Entity Framework code first has persisted the productcatalog class instance to the productcatalog and productcatalogphoto tables.

Through this note, I think everyone knows how to make the legacy database compatible with code first. The following notes describe how to use views and stored procedures in Entity Framework code first.