Inheritance with EF Code First: Part 1 – Table per Hierarchy (TPH)

2023-12-14 04:10

本文主要是介绍Inheritance with EF Code First: Part 1 – Table per Hierarchy (TPH),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

以下三篇文章是Entity Framework Code-First系列中第七回:Entity Framework Code-First(7):Inheritance Strategy 提到的三篇。这三篇文章写的时间有点久远,还是在2010年,提到EF应该在4.1版本之前,使用的还是ObjectContext而不是现在的DbContext,内容供参考

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Inheritance with EF Code First: Part 1 – Table per Hierarchy (TPH)

A simple strategy for mapping classes to database tables might be “one table for every entity persistent class.” This approach sounds simple enough and, indeed, works well until we encounter inheritance. Inheritance is such a visible structural mismatch between the object-oriented and relational worlds because object-oriented systems model both “is a” and “has a” relationships. SQL-based models provide only "has a" relationships between entities; SQL database management systems don’t support type inheritance—and even when it’s available, it’s usually proprietary or incomplete. 

There are three different approaches to representing an inheritance hierarchy:
  • Table per Hierarchy (TPH): Enable polymorphism by denormalizing the SQL schema, and utilize a type discriminator column that holds type information.
  • Table per Type (TPT): Represent "is a" (inheritance) relationships as "has a" (foreign key) relationships.
  • Table per Concrete class (TPC): Discard polymorphism and inheritance relationships completely from the SQL schema.
I will explain each of these strategies in a series of posts and this one is dedicated to TPH. In this series we'll deeply dig into each of these strategies and will learn about "why" to choose them as well as "how" to implement them. Hopefully it will give you a better idea about which strategy to choose in a particular scenario.

Inheritance Mapping with Entity Framework Code First

All of the inheritance mapping strategies that we discuss in this series will be implemented by EF Code First CTP5. The CTP5 build of the new EF Code First library has been released by ADO.NET team earlier this month. EF Code-First enables a pretty powerful code-centric development workflow for working with data. I’m a big fan of the EF Code First approach, and I’m pretty excited about a lot of productivity and power that it brings. When it comes to inheritance mapping, not only Code First fully supports all the strategies but also gives you ultimate flexibility to work with domain models that involves inheritance. The fluent API for inheritance mapping in CTP5 has been improved a lot and now it's more intuitive and concise in compare to CTP4.

A Note For Those Who Follow Other Entity Framework Approaches

If you are following EF's "Database First" or "Model First" approaches, I still recommend to read this series since although the implementation is Code First specific but the explanations around each of the strategies is perfectly applied to all approaches be it Code First or others.

A Note For Those Who are New to Entity Framework and Code-First

If you choose to learn EF you've chosen well. If you choose to learn EF with Code First you've done even better. To get started, you can find a great walkthrough by Scott Guthrie here and another one by ADO.NET team here. In this post, I assume you already setup your machine to do Code First development and also that you are familiar with Code First fundamentals and basic concepts. You might also want to check out my other posts on EF Code First like Complex Types and Shared Primary Key Associations.

A Top Down Development Scenario

These posts take a top-down approach; it assumes that you’re starting with a domain model and trying to derive a new SQL schema. Therefore, we start with an existing domain model, implement it in C# and then let Code First create the database schema for us. However, the mapping strategies described are just as relevant if you’re working bottom up, starting with existing database tables. I’ll show some tricks along the way that help you dealing with nonperfect table layouts.

The Domain Model

In our domain model, we have a BillingDetail base class which is abstract (note the italic font on the UML class diagram below). We do allow various billing types and represent them as subclasses of BillingDetail class. As for now, we support CreditCard and BankAccount:

Implement the Object Model with Code First

As always, we start with the POCO classes. Note that in our DbContext, I only define one DbSet for the base class which is BillingDetail. Code First will find the other classes in the hierarchy based on Reachability Convention.
public abstract class BillingDetail 
{public int BillingDetailId { get; set; }public string Owner { get; set; }        public string Number { get; set; }
}public class BankAccount : BillingDetail
{public string BankName { get; set; }public string Swift { get; set; }
}public class CreditCard : BillingDetail
{public int CardType { get; set; }                public string ExpiryMonth { get; set; }public string ExpiryYear { get; set; }
}public class InheritanceMappingContext : DbContext
{public DbSet<BillingDetail> BillingDetails { get; set; }
}

 

This object model is all that is needed to enable inheritance with Code First. If you put this in your application you would be able to immediately start working with the database and do CRUD operations. Before going into details about how EF Code First maps this object model to the database, we need to learn about one of the core concepts of inheritance mapping: polymorphic and non-polymorphic queries.

Polymorphic Queries

LINQ to Entities and EntitySQL, as object-oriented query languages, both support polymorphic queries—that is, queries for instances of a class and all instances of its subclasses, respectively. For example, consider the following query:
IQueryable<BillingDetail> linqQuery = from b in context.BillingDetails select b;
List<BillingDetail> billingDetails = linqQuery.ToList();

 

Or the same query in EntitySQL:
string eSqlQuery = @"SELECT VAlUE b FROM BillingDetails AS b";
ObjectContext objectContext = ((IObjectContextAdapter)context).ObjectContext;
ObjectQuery<BillingDetail> objectQuery = objectContext.CreateQuery<BillingDetail>(eSqlQuery);
List<BillingDetail> billingDetails = objectQuery.ToList();

 

linqQuery and eSqlQuery are both polymorphic and return a list of objects of the type BillingDetail, which is an abstract class but the actual concrete objects in the list are of the subtypes of BillingDetail: CreditCard and BankAccount.

Non-polymorphic Queries

All LINQ to Entities and EntitySQL queries are polymorphic which return not only instances of the specific entity class to which it refers, but all subclasses of that class as well. On the other hand,Non-polymorphic queries are queries whose polymorphism is restricted and only returns instances of a particular subclass. In LINQ to Entities, this can be specified by using OfType<T>()Method. For example, the following query returns only instances of BankAccount:
IQueryable<BankAccount> query = from b in context.BillingDetails.OfType<BankAccount>() select b;
EntitySQL has OFTYPE operator that does the same thing:
string eSqlQuery = @"SELECT VAlUE b FROM OFTYPE(BillingDetails, Model.BankAccount) AS b";

 

In fact, the above query with OFTYPE operator is a short form of the following query expression that uses TREAT and IS OF operators:
string eSqlQuery = @"SELECT VAlUE TREAT(b as Model.BankAccount) FROM BillingDetails AS b WHERE b IS OF(Model.BankAccount)";

 

(Note that in the above query, Model.BankAccount is the fully qualified name for BankAccount class. You need to change "Model" with your own namespace name.)

Table per Hierarchy (TPH)

An entire class hierarchy can be mapped to a single table. This table includes columns for all properties of all classes in the hierarchy. The concrete subclass represented by a particular row is identified by the value of a type discriminator column. You don’t have to do anything special in Code First to enable TPH. It's the default inheritance mapping strategy:
This mapping strategy is a winner in terms of both performance and simplicity. It’s the best-performing way to represent polymorphism—both polymorphic and nonpolymorphic queries perform well—and it’s even easy to implement by hand. Ad-hoc reporting is possible without complex joins or unions. Schema evolution is straightforward.

Discriminator Column

As you can see in the DB schema above, Code First has to add a special column to distinguish between persistent classes: the discriminator. This isn’t a property of the persistent class in our object model; it’s used internally by EF Code First. By default, the column name is "Discriminator", and its type is string. The values defaults to the persistent class names —in this case, “BankAccount” or “CreditCard”. EF Code First automatically sets and retrieves the discriminator values.

TPH Requires Properties in SubClasses to be Nullable in the Database

TPH has one major problem: Columns for properties declared by subclasses will be nullable in the database. For example, Code First created an (INT, NULL) column to map CardType property in CreditCard class. However, in a typical mapping scenario, Code First always creates an (INT, NOT NULL) column in the database for an int property in persistent class. But in this case, since BankAccount instance won’t have a CardType property, the CardType field must be NULL for that row so Code First creates an (INT, NULL) instead. If your subclasses each define several non-nullable properties, the loss of NOT NULL constraints may be a serious problem from the point of view of data integrity.

TPH Violates the Third Normal Form

Another important issue is normalization. We’ve created functional dependencies between nonkey columns, violating the third normal form. Basically, the value of Discriminator column determines the corresponding values of the columns that belong to the subclasses (e.g. BankName) but Discriminator is not part of the primary key for the table. As always, denormalization for performance can be misleading, because it sacrifices long-term stability, maintainability, and the integrity of data for immediate gains that may be also achieved by proper optimization of the SQL execution plans (in other words, ask your DBA).

Generated SQL Query

Let's take a look at the SQL statements that EF Code First sends to the database when we write queries in LINQ to Entities or EntitySQL. For example, the polymorphic query for BillingDetails that you saw, generates the following SQL statement:
SELECT 
[Extent1].[Discriminator] AS [Discriminator], 
[Extent1].[BillingDetailId] AS [BillingDetailId], 
[Extent1].[Owner] AS [Owner], 
[Extent1].[Number] AS [Number], 
[Extent1].[BankName] AS [BankName], 
[Extent1].[Swift] AS [Swift], 
[Extent1].[CardType] AS [CardType], 
[Extent1].[ExpiryMonth] AS [ExpiryMonth], 
[Extent1].[ExpiryYear] AS [ExpiryYear]
FROM [dbo].[BillingDetails] AS [Extent1]
WHERE [Extent1].[Discriminator] IN ('BankAccount','CreditCard')

 

Or the non-polymorphic query for the BankAccount subclass generates this SQL statement:
SELECT 
[Extent1].[BillingDetailId] AS [BillingDetailId], 
[Extent1].[Owner] AS [Owner], 
[Extent1].[Number] AS [Number], 
[Extent1].[BankName] AS [BankName], 
[Extent1].[Swift] AS [Swift]
FROM [dbo].[BillingDetails] AS [Extent1]
WHERE [Extent1].[Discriminator] = 'BankAccount'

 

Note how Code First adds a restriction on the discriminator column and also how it only selects those columns that belong to BankAccount entity.

Change Discriminator Column Data Type and Values With Fluent API 

Sometimes, especially in legacy schemas, you need to override the conventions for the discriminator column so that Code First can work with the schema. The following fluent API code will change the discriminator column name to "BillingDetailType" and the values to "BA" and "CC" for BankAccount and CreditCard respectively:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{modelBuilder.Entity<BillingDetail>().Map<BankAccount>(m => m.Requires("BillingDetailType").HasValue("BA")).Map<CreditCard>(m => m.Requires("BillingDetailType").HasValue("CC"));
}

 

Also, changing the data type of discriminator column is interesting. In the above code, we passed strings to HasValue method but this method has been defined to accepts a type of object:
public void HasValue(object value);

 

Therefore, if for example we pass a value of type int to it then Code First not only use our desired values (i.e. 1 & 2) in the discriminator column but also changes the column type to be (INT, NOT NULL):
modelBuilder.Entity<BillingDetail>().Map<BankAccount>(m => m.Requires("BillingDetailType").HasValue(1)).Map<CreditCard>(m => m.Requires("BillingDetailType").HasValue(2));

 

Summary

In this post we learned about Table per Hierarchy as the default mapping strategy in Code First. The disadvantages of the TPH strategy may be too serious for your design—after all, denormalized schemas can become a major burden in the long run. Your DBA may not like it at all. In the next post, we will learn about Table per Type (TPT) strategy that doesn’t expose you to this problem. 

原文地址:http://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarchy-tph

转载于:https://www.cnblogs.com/purplefox2008/p/5644923.html

这篇关于Inheritance with EF Code First: Part 1 – Table per Hierarchy (TPH)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/491100

相关文章

Level3 — PART 3 — 自然语言处理与文本分析

目录 自然语言处理概要 分词与词性标注 N-Gram 分词 分词及词性标注的难点 法则式分词法 全切分 FMM和BMM Bi-direction MM 优缺点 统计式分词法 N-Gram概率模型 HMM概率模型 词性标注(Part-of-Speech Tagging) HMM 文本挖掘概要 信息检索(Information Retrieval) 全文扫描 关键词

MySQL record 02 part

查看已建数据库的基本信息: show CREATE DATABASE mydb; 注意,是DATABASE 不是 DATABASEs, 命令成功执行后,回显的信息有: CREATE DATABASE mydb /*!40100 DEFAULT CHARACTER SET utf8mb3 / /!80016 DEFAULT ENCRYPTION=‘N’ / CREATE DATABASE myd

vue2实践:el-table实现由用户自己控制行数的动态表格

需求 项目中需要提供一个动态表单,如图: 当我点击添加时,便添加一行;点击右边的删除时,便删除这一行。 至少要有一行数据,但是没有上限。 思路 这种每一行的数据固定,但是不定行数的,很容易想到使用el-table来实现,它可以循环读取:data所绑定的数组,来生成行数据,不同的是: 1、table里面的每一个cell,需要放置一个input来支持用户编辑。 2、最后一列放置两个b

Debugging Lua Project created in Cocos Code IDE creates “Waiting for debugger to connect” in Win-7

转自 I Installed Cocos Code IDE and created a new Lua Project. When Debugging the Project(F11) the game window pops up and gives me the message waiting for debugger to connect and then freezes. Also a

LLVM入门2:如何基于自己的代码生成IR-LLVM IR code generation实例介绍

概述 本节将通过一个简单的例子来介绍如何生成llvm IR,以Kaleidoscope IR中的例子为例,我们基于LLVM接口构建一个简单的编译器,实现简单的语句解析并转化为LLVM IR,生成对应的LLVM IR部分,代码如下,文件名为toy.cpp,先给出代码,后面会详细介绍每一步分代码: #include "llvm/ADT/APFloat.h"#include "llvm/ADT/S

VS Code 调试go程序的相关配置说明

用 VS code 调试Go程序需要在.vscode/launch.json文件中增加如下配置:  // launch.json{// Use IntelliSense to learn about possible attributes.// Hover to view descriptions of existing attributes.// For more information,

通过Ajax请求后台数据,返回JSONArray(JsonObject),页面(Jquery)以table的形式展示

点击“会商人员情况表”,弹出层,显示一个表格,如下图: 利用Ajax和Jquery和JSONArray和JsonObject来实现: 代码如下: 在hspersons.html中: <!DOCTYPE html><html><head><meta charset="UTF-8"><title>会商人员情况表</title><script type="text/javasc

code: 400, msg: Required request body is missing 错误解决

引起这个错误的原因是,请求参数按照get方式给。 应该给json字符串才对 补充: 1. @RequestBody String resource 加@RequestBody必须给json字符串,否则会报错400,记如标题错误。 不加这个的进行请求的话,其实post和get就没有什么区别了。 2. List<String> indexCodes=(List<String>)json.

Vue3图片上传报错:Required part ‘file‘ is not present.

错误 "Required part 'file' is not present" 通常表明服务器期望在接收到的 multipart/form-data 请求中找到一个名为 file 的部分(即文件字段),但实际上没有找到。这可能是因为以下几个原因: 请求体构建不正确:在发送请求时,可能没有正确地将文件添加到 FormData 对象中,或者使用了错误的字段名。 前端代码错误:在前端代码中,可能

UVa 10820 Send a Table (Farey数列欧拉函数求和)

这里先说一下欧拉函数的求法 先说一下筛选素数的方法 void Get_Prime(){ /*筛选素数法*/for(int i = 0; i < N; i++) vis[i] = 1;vis[0] = vis[1] = 0;for(int i = 2; i * i < N; i++)if(vis[i]){for(int j = i * i; j < N; j += i)vis[j] =