Entity Framework 6 Recipes Chinese translation series (7), entityframework

Entity Framework 6 Recipes Chinese translation series (7), entityframework
2-6 split entities into multiple tables

Problem

You have two or more tables that share the same primary key. You want to map them to a separate entity.

Solution

　　Let's use the two tables shown in Figure 2-15 to demonstrate this situation.

Figure 2-15: two tables, Prodeuct and ProductWebInfo, with the same primary key

Follow these steps to create a separate entity for the two tables:

1. Create a context object EF6RecipesContext inherited to DbContext in your project;

2. Create a POCO object Product using code list 2-8;

Code List 2-8: Create a POCO object Product

1 public class Product {2         [Key]3         [DatabaseGenerated(DatabaseGeneratedOption.None)]4         public int SKU { get; set; }5         public string Description { get; set; }6         public decimal Price { get; set; }7         public string ImageURL { get; set; }8     }

3. Add the attribute Products of the DbSet <Product> type to EF6RecipesContext;

4. Use Code List 2-9 to rewrite the OnModelCreating () method in EF6RecipesContext;

Code List 2-9 override OnModelCreating () method

 1 public class EF6RecipesContext : DbContext { 2         public DbSet<Product> Products { get; set; } 3         public EF6RecipesContext() 4             : base("name=EF6CodeFirstRecipesContext") { 5         } 6         protected override void OnModelCreating(DbModelBuilder modelBuilder) { 7             base.OnModelCreating(modelBuilder); 8             modelBuilder.Entity<Product>() 9             .Map(m => {10                 m.Properties(p => new { p.SKU, p.Description, p.Price });11                 m.ToTable("Product", "Chapter2");12             })13             .Map(m => {14                 m.Properties(p => new { p.SKU, p.ImageURL });15                 m.ToTable("ProductWebInfo", "Chapter2");16             });17         }18     }

Principle

This situation is common in legacy systems. Each row in a table contains additional information that should belong to another table. This often happens as the database changes. No one is willing to break the existing code, but to solve the problem by adding some columns to a key table. The answer to this case is to create a new table to "Port" the additional columns.

It is usually called splitting an entity to two or more database tables. Each component can be considered as a logical entity. This process is called Vertical Split.

The disadvantage of Vertical Split is that when we obtain an object type instance, an extra join (join) is required for the split table to construct the object type. This extra join is shown in Listing 2-10:

Listing 2-10 Vertical Split requires additional Join connections

1 SELECT2 [Extent1].[SKU] AS [SKU],3 [Extent2].[Description] AS [Description],4 [Extent2].[Price] AS [Price],5 [Extent1].[ImageURL] AS [ImageURL]6 FROM [dbo].[ProductWebInfo] AS [Extent1]7 INNER JOIN [dbo].[Product] AS [Extent2] ON [Extent1].[SKU] = [Extent2].[SKU]

There are no special requirements for inserting and retrieving Product entities. Code List 2-11 demonstrates the Vertical Split Product entity type

 

 1  using (var context = new EF6RecipesContext()) { 2                 var product = new Product { 3                     SKU = 147, 4                     Description = "Expandable Hydration Pack", 5                     Price = 19.97M, 6                     ImageURL = "/pack147.jpg" 7                 }; 8                 context.Products.Add(product); 9                 product = new Product {10                     SKU = 178,11                     Description = "Rugged Ranger Duffel Bag",12                     Price = 39.97M,13                     ImageURL = "/pack178.jpg"14                 };15                 context.Products.Add(product);16                 product = new Product {17                     SKU = 186,18                     Description = "Range Field Pack",19                     Price = 98.97M,20                     ImageURL = "/noimage.jp"21                 };22                 context.Products.Add(product);23                 product = new Product {24                     SKU = 202,25                     Description = "Small Deployment Back Pack",26                     Price = 29.97M,27                     ImageURL = "/pack202.jpg"28                 };29                 context.Products.Add(product);30                 context.SaveChanges();31             }32             using (var context = new EF6RecipesContext()) {33                 foreach (var p in context.Products) {34                     Console.WriteLine("{0} {1} {2} {3}", p.SKU, p.Description,35                     p.Price.ToString("C"), p.ImageURL);36                 }37             }

The output of code list 2-11 is as follows:

147 Expandable Hydration Pack $19.97/pack147.jpg
178 Rugged Ranger duel Bag $39.97/pack178.jpg
186 Range Field Pack $98.97/noimage.jpg
202 Small Deployment Back Pack $29.97/pack202.jpg

 

2-7 split a table into multiple entities

Problem

You have a database table that contains frequently used characters and some infrequently used large fields. To improve performance, you must avoid loading these fields for each query. You need to split the table into two or more entities.

Solution

Assume that you have a 2-16 table that stores the photo information, as well as the thumbnail and full resolution of the photo.

Figure 2-16 in the Photograph table, there is a binary large object field that stores image data

Follow these steps to create an object that contains a reasonable cost and frequently used columns, and create an object that contains a high-resolution ranking that is costly and rarely used:

1. Create a context object EF6RecipesContext inherited from DbContext in your project;

2. Create a POCO object Photograph using code list 2-12;

　　　　　　Proxy List 2-12Create a POCO object Photograph

1   public class Photograph {2         [Key]3         [DatabaseGenerated(DatabaseGeneratedOption.Identity)]4         public int PhotoId { get; set; }5         public string Title { get; set; }6         public byte[] ThumbnailBits { get; set; }7         [ForeignKey("PhotoId")]8         public virtual PhotographFullImage PhotographFullImage { get; set; }9     }

3. Create a POCO object PhotographFullImage using code list 2-13;

　　　　　　Proxy List 2-13Create a POCO object PhotographFullImage

1     public class PhotographFullImage {2         [Key]3         public int PhotoId { get; set; }4         public byte[] HighResolutionBits { get; set; }5         [ForeignKey("PhotoId")]6         public virtual Photograph Photograph { get; set; }7     }

4. Add the DbSet <Photograph> attribute to the context object EF6RecipesContext;

5. Add another DbSet <PhotographFullImage> attribute to the context object EF6RecipesContext;

6. Use the OnModelCreating () method in the context object in code list 2-14;

　　　　　　Code List 2-14Override the OnModelCreating () method in the context object

1  protected override void OnModelCreating(DbModelBuilder modelBuilder) {2             base.OnModelCreating(modelBuilder);3             modelBuilder.Entity<Photograph>()4             .HasRequired(p => p.PhotographFullImage)5             .WithRequiredPrincipal(p => p.Photograph);6             modelBuilder.Entity<Photograph>().ToTable("Photograph", "Chapter2");7             modelBuilder.Entity<PhotographFullImage>().ToTable("Photograph", "Chapter2");8         }

Principle

The Entity Framework does not support loading a single object attribute with delay. To get the benefits of high-cost attributes of delayed loading, we use the feature of object framework to delay loading of correlated entities. We create a new object, PhotographFullImage, which includes the expensive storage of the complete image column, the association between a Photograph object and a PhotographFullImange object. In addition, we add a constraint similar to the database reference constraint in the concept layer, which tells the Entity Framework that a PhotographFullImage cannot be independent from Photograph.

Because of the existence of reference constraints, we have two things to note in the model: one is, before we create an instance of the PhotographFullImage object or call the SaveChages () method, the instance of photogrpb must exist in the context. The second is that if I delete a photograph, the associated photographFullImage will also be deleted, which is a bit like cascading deletion of reference constraints in the database.

Code List 2-15 shows how to insert and retrieve data from a model.

Code List 2-15Insert and delayed loading of expensive Fields

 

1 byte [] thumbBits = new byte [100]; 2 byte [] fullBits = new byte [2000]; 3 using (var context = new EF6RecipesContext ()) {4 var photo = new Photograph {5 Title = "My Dog", 6 ThumbnailBits = thumbBits 7}; 8 var fullImage = new PhotographFullImage {HighResolutionBits = fullBits}; 9 photo. photographFullImage = fullImage; 10 context. photographs. add (photo); 11 context. saveChanges (); 12} 13 using (var context = new EF6RecipesContext () {14 foreach (var photo in context. photographs) {15 Console. writeLine ("Photo: {0}, ThumbnailSize {1} bytes", 16 photo. title, photo. thumbnailBits. length); 17 18 // The Field 19 context storing the complete image is delayed. entry (photo ). reference (p => p. photographFullImage ). load (); 20 Console. writeLine ("Full Image Size: {0} bytes", 21 photo. photographFullImage. highResolutionBits. length); 22} 23}

The output of code list 2-15 is as follows:

Photo: My Dog, Thumbnail Size: 100 bytes
Full Image Size: 2000 bytes

 

In code list 2-15, create and initialize instance objects of the entities Photograph and PhotographFullmage, add them to the context object, and then call the SaveChanges () method to save.

In the query, we obtain each photograph in the database, print their information, and display the object PhotographFullImage that loads the relationship with it. Note: We have not disabled the default delayed loading option in the context, which is exactly what we need. We can choose not to load PhotographFullImage instances. If we get hundreds of thousands of photos, this will save us a lot of time and bandwidth.

This article ends now. If you are still reading this series, please recommend it for your support. Thank you ~

 

Entity Framework exchange QQ group: 458326058. You are welcome to join us.

Thank you for your continued attention, my blog address: http://www.cnblogs.com/VolcanoCloud/