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

Entity Framework 6 Recipes Chinese translation series (31), entityframework
The original intention of the translation and why I chose Entity Framework 6 Recipes. Please refer to Chapter 6-4 of this series to use TPH to model the self-reference relationship.

Problem

　　You have a self-referenced table that represents different types of objects associated with the database. You want to use TPH to model this table.

Solution

Suppose you have a table 6-5 that describes people. People usually have a hero in their hearts, which can inspire themselves most. We use a reference pointing to another row in the Person table to represent the hero in our hearts.

Figure 6-5 List of persons with different roles

 

In reality, everyone has a role. Some are firefighters, some are teachers, and some are retired. Of course, there may be more roles here. Each person's information indicates their roles. A fireman is stationed at the fire station and a teacher teaches at the school. Retired people usually have a hobby.

In our example, roles may include firefighter (f), teacher (t), or retired (r ). The role column uses a single character to specify the role of a person.

Follow these steps to create a model:

1. Create a context object Recipe4Context derived from DbContext;

2. Use the code listing 6-8 to create an abstract POCO entity Person;

Code List 6-8.Create an abstract POCO object class Person

[Table("Person", Schema = "Chapter6")]    public abstract class Person    {        [Key]        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]        public int PersonId { get; protected set; }        public string Name { get; set; }        public virtual Person Hero { get; set; }        public virtual ICollection<Person> Fans { get; set; }     }   

3. Add a DbSe <Person> type attribute to the context object Recipe4Context;

4. Use the code in code listing 6-9 to add specific POCO entity classes, Fierfighter, Teacher, and Retired;

Code List 6-9.Create a specific POCO entity class, Fierfighter, Teacher, and Retired

 public class Firefighter : Person    {        public string FireStation { get; set; }    }    public class Teacher : Person    {        public string School { get; set; }    }    public class Retired : Person    {        public string FullTimeHobby { get; set; }    }

5. Rewrite the OnModelCreating method in the context object Recipe4Context to configure the HeroId foreign key and type hierarchy. See Code List 6-10;

Code List 6-10.Override OnModelCreating Method

 1  protected override void OnModelCreating(DbModelBuilder modelBuilder) 2         { 3             base.OnModelCreating(modelBuilder); 4  5             modelBuilder.Entity<Person>() 6                         .HasMany(p => p.Fans) 7                         .WithOptional(p => p.Hero) 8                         .Map(m => m.MapKey("HeroId")); 9 10             modelBuilder.Entity<Person>()11                         .Map<Firefighter>(m => m.Requires("Role").HasValue("f"))12                         .Map<Teacher>(m => m.Requires("Role").HasValue("t"))13                         .Map<Retired>(m => m.Requires("Role").HasValue("r"));14         }

Principle

Code List 6-11 demonstrates getting and inserting Person entities from our model. We create an instance for each derived type and construct some heroic relationships. We have a teacher who is a hero in the hearts of firefighters, a retired employee, and a hero in the hearts of the teacher. When we set the fireman as the hero of a retired employee, we introduced a loop. At this time, the physical framework will generate a runtime exception (DbUpdatexception ), because it cannot determine the proper sequence to insert data into the database. In the code, we use the SaveChanges () method to bypass this problem before setting the hero relationship. Once the data is submitted to the database, the Entity Framework will bring the key values generated in the database back to the object graph, so that we will not pay any price for updating the relationship graph. Of course, the SaveChages () method must be called to save these updates.

 1  using (var context = new Recipe4Context()) 2             { 3                 var teacher = new Teacher 4                 { 5                     Name = "Susan Smith", 6                     School = "Custer Baker Middle School" 7                 }; 8                 var firefighter = new Firefighter 9                 {10                     Name = "Joel Clark",11                     FireStation = "Midtown"12                 };13                 var retired = new Retired14                 {15                     Name = "Joan Collins",16                     FullTimeHobby = "Scapbooking"17                 };18                 context.People.Add(teacher);19                 context.People.Add(firefighter);20                 context.People.Add(retired);21                 context.SaveChanges();22                 firefighter.Hero = teacher;23                 teacher.Hero = retired;24                 retired.Hero = firefighter;25                 context.SaveChanges();26             }27 28             using (var context = new Recipe4Context())29             {30                 foreach (var person in context.People)31                 {32                     if (person.Hero != null)33                         Console.WriteLine("\n{0}, Hero is: {1}", person.Name,34                                             person.Hero.Name);35                     else36                         Console.WriteLine("{0}", person.Name);37                     if (person is Firefighter)38                         Console.WriteLine("Firefighter at station {0}",39                                            ((Firefighter)person).FireStation);40                     else if (person is Teacher)41                         Console.WriteLine("Teacher at {0}", ((Teacher)person).School);42                     else if (person is Retired)43                         Console.WriteLine("Retired, hobby is {0}",44                                            ((Retired)person).FullTimeHobby);45                     Console.WriteLine("Fans:");46                     foreach (var fan in person.Fans)47                     {48                         Console.WriteLine("\t{0}", fan.Name);49                     }50                 }51             }

The output of code listing 6-11 is as follows:

Susan Smith, Hero is: Joan CollinsTeacher at Custer Baker Middle SchoolFans:        Joel ClarkJoel Clark, Hero is: Susan SmithFirefighter at station MidtownFans:        Joan CollinsJoan Collins, Hero is: Joel ClarkRetired, hobby is ScapbookingFans:        Susan Smith

 

6-5 Use TPH to model the self-reference relationship

Problem

　　You are using a self-referenced table to store hierarchical data. Given a record, obtain all records related to this record, which can be part of any depth in the hierarchy.

Solution

Suppose you have a Category Table 6-6.

Figure 6-6 Self-referenced Category table

 

Follow these steps to create a model:

1. Create a context object Recipe5Context derived from DbContext;

2. Create a POCO entity Category using code 6-12;

Code List 6-12.Create a POCO object class Category

 1  [Table("Category", Schema = "Chapter6")] 2     public class Category 3     { 4         [Key] 5         [DatabaseGenerated(DatabaseGeneratedOption.Identity)] 6         public int CategoryId { get; set; } 7         public string Name { get; set; } 8  9         public virtual Category ParentCategory { get; set; }10         public virtual ICollection<Category> SubCategories { get; set; }11     }

3. Add a DbSe <Category> attribute in the context object Recipe5Context;

4. Override the OnModelCreating method in the context object Recipe4Context, as shown in code listing 6-13. We have created the association ParentCategory and SubCategories, and configured the foreign key constraints.

Code List 6-13.Override OnModelCreating Method

1    protected override void OnModelCreating(DbModelBuilder modelBuilder)2         {3             base.OnModelCreating(modelBuilder);4 5             modelBuilder.Entity<Category>()6                         .HasOptional(c => c.ParentCategory)7                         .WithMany(c => c.SubCategories)8                         .Map(m => m.MapKey("ParentCategoryId"));9         }

In our model, the Category object has a navigation attribute Subcategories, which can be used to obtain the direct subdirectory set of the directory. Then, to access them, we need to use the Load () or Include () method to explicitly Load them. The Load () method requires an additional database interaction. The Include () method only provides a predefined in-depth access method.

We need to load the entire hierarchy to the object graph as effectively as possible. We use the table expression in the stored procedure.

To add a stored procedure to the model, follow these steps:

5. Create a stored procedure named GetSubCategories. It uses a table expression and returns all subdirectories for a directory ID recursively. The stored procedure is shown in Listing 6-14:

Code List 6-14.GetSubCategories: returns all subdirectories for a given directory ID.

create proc chapter6.GetSubCategories(@categoryid int)asbeginwith cats as(select c1.*from chapter6.Category c1where CategoryId = @categoryidunion allselect c2.*from cats join chapter6.Category c2 on cats.CategoryId = c2.ParentCategoryId)select * from cats where CategoryId != @categoryidend

6. Add a method to the context object Recipe5Context to accept Integer Parameters. It returns an ICollection <Category>. See Code List 6-15. Code First in entity model 6 does not support importing stored procedures into the designer. Therefore, in the method, we use the SqlQuery method in the DbContext attribute Database.

Code List 6-15.Implement the GetSubCateories method in the context object

1  public ICollection<Category> GetSubCategories(int categoryId)2         {3             return this.Database.SqlQuery<Category>("exec Chapter6.GetSubCategories @catId",4                                                     new SqlParameter("@catId", categoryId)).ToList();5         }

We use the GetSubCategoryes method defined in the context to instantiate an object graph that contains all directories and subdirectories. Code Listing 6-16 demonstrates the use of the GetSubCategories () method.

Code List 6-16.Use the GetSubCategories () method to obtain the entire hierarchy

 using (var context = new Recipe5Context())            {                var book = new Category { Name = "Books" };                var fiction = new Category { Name = "Fiction", ParentCategory = book };                var nonfiction = new Category { Name = "Non-Fiction", ParentCategory = book };                var novel = new Category { Name = "Novel", ParentCategory = fiction };                var history = new Category { Name = "History", ParentCategory = nonfiction };                context.Categories.Add(novel);                context.Categories.Add(history);                context.SaveChanges();            }            using (var context = new Recipe5Context())            {                var root = context.Categories.Where(o => o.Name == "Books").First();                Console.WriteLine("Parent category is {0}, subcategories are:", root.Name);                foreach (var sub in context.GetSubCategories(root.CategoryId))                {                    Console.WriteLine("\t{0}", sub.Name);                }            }

The output of code list 6-16 is as follows:

Parent category is Books, subcategories are:        Fiction        Non-Fiction        History        Novel    

Principle

The Entity Framework supports self-reference associations, as we can see in sections 6.2 and 6.3. In these two sections, we use the Load () method to directly Load object references and reference object sets. However, we have to be careful that every Load () will result in a database interaction before we can obtain the entity or entity set. For a large object graph, this will consume a lot of database resources.

In this section, we demonstrate a slightly different method. Compared with the Load () method to instantiate each object or object set, we put the work in the data storage layer by using a stored procedure, recursively enumerate all subdirectories and return this set. In the stored procedure, we use a table expression to implement recursive queries. In our example, we select to enumerate all subdirectories. Of course, you can modify the stored procedure and selectively enumerate the elements in the hierarchy.

To use this stored procedure, we add a method to call the stored procedure through DbContext. Database. SqlQuery <T> () in the context. We use SqlQuery <T> () instead of ExecuteSqlCommand (), because our stored procedure returns a result set.

 

 

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/