EF6代码优先使用基本类TPC和每个数据库表的DbSet [英] EF6 Code First with Base Classes TPC and a DbSet per DB Table
问题描述
嗨 - 我希望有人可以帮助我，因为我正要放弃尝试使用Entity Framework来解决我的遗留应用程序中的一些问题。最近我确定了一个问题区域，并做了一些事件风暴，并提出了一个很好的想法，为我的新问题区域创建一个有限的
上下文，创建一些新的域类作为解决方案，一些新表，当然，一个反腐败层，以防止我遗留的代码破坏我的新模型。作为此解决方案的一部分，我使用EF进行数据访问。
我在另一个应用程序中使用了EF 4和4.1，并且几乎没有关于ADO.NET连接，存储过程，事务等的人们关注的问题，而是能够专注于该LOB应用程序的业务。但是，将
移动到这个巨大的Enterprise Legacy应用程序，我想也许对于这个单一的有界上下文，我可以尝试EF6来解决这个问题。到目前为止，我只发现了心痛和疼痛。没有什么能像它应该的那样起作用，因为我有一些需求，但是在我看到的Julie Lerman的任何一门课程中都没有覆盖
。她总是暗示要做这个或那个抽象，然后不回来展示做我需要的细节。不是她的错，当然，她无法预见到每个人的需求，我可能会错过
错过了一些东西，所以我希望有人可以提供帮助，然后我不得不放弃使用EF6并移动另一个解决这个问题的方法。
正如我所说，我想使用EF 6.1.3，我想使用Code First，因为它看起来比静态EDMX模型更清晰，但显然很复杂。我需要从数据库中反向设计POCO类。该解决方案有2个新表，但是有一小部分
的其他表我无法改变。更糟糕的是，我们无法更改数据库模式模式，它们被设置并在特定表的所有列上使用类似3字符前缀的内容。每个表都有一个唯一的3个字符前缀，系统中大约有800个表
。在这种情况下，我只处理其中的大约18个，只需要写回2！ EF应该能够正确处理这个问题！
所以这里的底线是我从我的类结构中生成错误的SQL，尝试执行TPC，类型为每个具体类。我希望有一个EntityBase对象，我所有的POCO类都是固有的，但是数据库不需要知道关于
的任何内容，并且只有我在模型中拥有的所有DbSet项的不同表。这很有用，直到我开始添加基类继承。一旦我为所有实体执行此操作，并尝试对它们执行简单选择，我就会得到如下SQL：
SELECT
  [Extent1]。[afy_key] AS [afy_key]，
  [Extent2]。[afy_code] AS [afy_code]，
  [Extent2]。[afy_start_date] AS [afy_start_date]，
  [Extent2]。[afy_end_date] AS [afy_end_date]，
  [Extent2]。[afy_closed_flag] AS [afy_closed_flag]，
  [Extent2]。[afy_close_date] AS [afy_close_date]，
  [Extent2]。[afy_close_user] AS [afy_close_user]，
  [Extent2]。[afy_atc_key] AS [afy_atc_key]，
  [Extent1]。[afy_add_date] AS [afy_add_date]，
  [Extent1]。[afy_add_user] AS [afy_add_user]，
  [Extent1]。[afy_change_date] AS [afy_change_date]，
  [Extent1]。[afy_change_user] AS [afy_change_user]，
  [Extent1]。[afy_delete_flag] AS [afy_delete_flag]，
  [Extent1]。[afy_entity_key] AS [afy_entity_key]
  FROM [dbo]。[ac_ar_fiscal_year] AS [Extent1]
  INNER JOIN [dbo]。[ac_ar_fiscal_year1] AS [Extent2] ON [Extent1]。[afy_key] = [Extent2]。[Key]
请注意我已经告诉实体框架继续使用继承的成员，我也试过告诉它不要。我做的事情没有什么不同我实施继承后总能得到这个。
所以我遇到的问题是我做逆向工程，我得到了我的POCO然后我开始编写扩展方法，使用EF6 Fluent API（OnModelCreating）技术自动配置每个方法。那么让我们看看上下文
对象和我对单个对象的配置。
public partial class DeferralsContext ：DbContext
{
public DeferralsContext（）
：base（" name = DeferralsContext"）
{
Database.SetInitializer（new NullDatabaseInitializer< DeferralsContext>（）） ;
}
公共虚拟DbSet< ac_ar_fiscal_year> ac_ar_fiscal_year {get;组; }
protected override void OnModelCreating（DbModelBuilder modelBuilder）
{
#region Map Base Fields
modelBuilder.Entity< ac_ar_fiscal_year>（）
.Map（ m => {m.MapBaseColumns（" afy"，" ac_ar_fiscal_year"）;}）
.ApplyBasePropertyConstraints（）;
}
所以我为我映射的每个实体类型都有一个DbSet。但是等等，这不是TPC工作的方式你说的！好的，我发现了这一点，并决定在我的1个EntityBase对象只有一个DbSet的地方（是的，它是抽象的）。当我尝试
时，我必须更改我的查询以使用context.entities.OfType< realType>（lambda）。太棒了，我这样做，然后我仍然将每个表映射到正确的类型，如上所示。然后我得到一个错误，当DB初始化时，EntityBase没有Key参数。
我可以在属性中设置一个键，告诉它忽略该字段，我也可以在Fluent API中设置它并不重要，我总是得到那个错误。所以我回到了更接近工作的地方，我为所有类型设置了DbSet，并使用上面的方法映射每个
。这是我的2种扩展方法的代码，因此您可以看到我要为表格设置的内容：
public static void MapBaseColumns< TEntityType>（
此EntityMappingConfiguration< TEntityType>地图，字符串tablePrefix，字符串tableName，
string overrideKeyColumnName = null）
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP;其中TEntityType：EntityBase
&NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; if（map == null）
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;抛出新的ArgumentNullException（nameof（map），
new Exception（" EntityTypeConfiguration在映射基本属性时不能为null 。"））;
$
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; if（string.IsNullOrEmpty（tablePrefix））
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;抛出新的ArgumentOutOfRangeException（nameof（tablePrefix），
new Exception（" Table Prefix是映射基本属性所必需的） 。"））;
$
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; if（string.IsNullOrEmpty（tableName））
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;抛出新的ArgumentOutOfRangeException（nameof（tableName），
new Exception（" Table Name是映射基本属性所必需的） 。"））;
&NBSP; &NBSP; &NBSP; &NBSP;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; map.Property（table => table.Key）.HasColumnName（overrideKeyColumnName ?? $" {tablePrefix} _key"）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; map.Property（table => table.CreatedOn）.HasColumnName（$" {tablePrefix} _add_date"）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; map.Property（table => table.CreatedOn）.HasColumnName（$" {tablePrefix} _add_date"）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; map.Property（table => table.CreatedBy）.HasColumnName（$" {tablePrefix} _add_user"）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; map.Property（table => table.LastUpdatedOn）.HasColumnName（$" {tablePrefix} _change_date"）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; map.Property（table => table.LastUpdatedBy）.HasColumnName（$" {tablePrefix} _change_user"）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; map.Property（table => table.IsDeleted）.HasColumnName（$" {tablePrefix} _delete_flag"）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; map.Property（table => table.EntityKey）.HasColumnName（$" {tablePrefix} _entity_key"）; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; map.ToTable（tableName）;
&NBSP; &NBSP; &NBSP; }¥b $ b &NBSP; &NBSP; &NBSP; public static EntityTypeConfiguration< TEntityType> ApplyBasePropertyConstraints< TEntityType>（
此EntityTypeConfiguration< TEntityType> config，int columnOrderStart = 0）
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP;其中TEntityType：EntityBase
&NBSP; &NBSP; &NBSP; {
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; config.HasKey（e => e.Key）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; config.Property（e => e.Key）.HasDatabaseGeneratedOption（DatabaseGeneratedOption.None）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; config.Property（e => e.CreatedBy）
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .IsRequired（）
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .HasMaxLength（64）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; config.Property（e => e.LastUpdatedBy）
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; .HasMaxLength（64）;
&NBSP; &NBSP; &NBSP; &NBSP; &NBSP;返回配置;
&NBSP; &NBSP; &NBSP; }
所以我的情况有点独特，因为我无法更改表格中列的名称，但它们都有一组共同的字段，主要是审计，已删除和实体键字段。关键是常见的字段集从不具有相同的名称，它们只需遵循一个模式，即prefix_change_user等（参见上面的代码）。我不想反复设置它们中的每一个，而且，我不希望在与我的存储库中的数据库实际交互期间设置它们的数据。所以，我是
将它们全部映射到我的基类中的相同名称，因此它们的名称很简单，例如Key，ChangedBy，ChangedOn等。这样我可以稍后覆盖SaveChanges方法并设置任何实体的审核字段，因为它们都具有相同的名称。
这就是我的故事，我面临的问题开始阻止我使用EF6作为一种解决方案，因为我只是没时间找出"更好"的解决方案。办法。我认为这是一种更好的方法，但如果我不能快速克服这项技术，我必须回到我们原来的手动ADO.NET的东西，这是有效的，但我认为这是问题的一部分我的应用程序首先，至少它的实现方式。谁能帮我吗？不确定我是否b $ b忘记了一些相关的代码。要清楚，没有表，也不会有一个表，在我的数据库中名为EntityBase。我希望EF能够处理我的派生类，就像他们没有得到任何东西一样。看起来好像直到
创建了一些SQL来从中进行选择，然后它添加了傻联接回原始表。它基本上为基类创建一个Extent1，然后为本地类属性创建一个Extent2，然后尝试将Extent1连接到Extent2，并在Extent2上创建它
决定不认识到名为Key的属性被映射到某个实际列名比如xyz_key，它使用C＃属性的名称"Key"。而不是映射的列名称。这似乎已经破了，但我确定我在过去3或4晚连续阅读所有关于这些东西之后，我只是错过了
的东西。帮助!!!!
正如我所说，我想使用EF 6.1.3，我想使用Code First作为它看起来比静态EDMX模型更清晰，但显然也很复杂。
多年来我看到的是开发人员首先使用代码来解决复杂的数据库模式。
Hi - I'm hoping somebody can help me because I'm just about to give up trying to use Entity Framework to solve some problems in my Legacy App. Recently I identified a problem area and did some event storming and came up with a great idea to have a bounded context for my new problem area, create some new domain classes as a solution, some new tables, and of course, an Anti-Corruption Layer to keep my legacy code from corrupting my nice new model. As part of this solution, I was sold on using EF for data access. I used EF 4 and 4.1 on another application and did not have nearly as many issues surrounding people focusing on ADO.NET connections, stored procedures, transactions, etc. and instead was able to focus on the business of that LOB application. However, moving to this huge Enterprise Legacy application, I thought maybe for this single bounded context, I could try EF6 to solve the problem. I've only found heartache and pain so far. Nothing works as it's supposed to because I have a few needs that just aren't covered in any of Julie Lerman's courses that I can see. She always hints at making this or that abstraction and then does not come back and show the specifics needed to do what I need. Not her fault, for sure, she cannot anticipate the needs of everyone and I could've missed something too so I'm reaching out hoping somebody can help before I just have to scrap my use of EF6 and move on with another solution to this problem.
So as I said, I want to use EF 6.1.3, I want to use Code First as it seems cleaner than static EDMX models but just as complex apparently. I need to reverse engineer the POCO classes from the database. The solution has 2 new tables but there's a a handful of other tables I cannot change. What's worse, we cannot go changing the DB schema patterns, they are set and use things like a 3 char prefix on all the columns of a specific table. Each table has a unique 3 char prefix and there are about 800 or so tables in the system. I'm only dealing with about 18 of them for this context and only need to write back to 2!! EF should be able to handle this right!
So the bottom line here is I'm getting incorrect SQL generated from my class structure trying to do TPC, type-per-concrete-class. I want to have an EntityBase object that all my POCO classes inherent, but the database doesn't need to know anything about this and just has different tables for all of the DbSet items I have in the model. This works just great until I start adding the base class inheritance. Once I do that for all my entities, and try to perform a simple select on them, I get SQL like this:
SELECT[Extent1].[afy_key] AS [afy_key],
[Extent2].[afy_code] AS [afy_code],
[Extent2].[afy_start_date] AS [afy_start_date],
[Extent2].[afy_end_date] AS [afy_end_date],
[Extent2].[afy_closed_flag] AS [afy_closed_flag],
[Extent2].[afy_close_date] AS [afy_close_date],
[Extent2].[afy_close_user] AS [afy_close_user],
[Extent2].[afy_atc_key] AS [afy_atc_key],
[Extent1].[afy_add_date] AS [afy_add_date],
[Extent1].[afy_add_user] AS [afy_add_user],
[Extent1].[afy_change_date] AS [afy_change_date],
[Extent1].[afy_change_user] AS [afy_change_user],
[Extent1].[afy_delete_flag] AS [afy_delete_flag],
[Extent1].[afy_entity_key] AS [afy_entity_key]
FROM [dbo].[ac_ar_fiscal_year] AS [Extent1]
INNER JOIN [dbo].[ac_ar_fiscal_year1] AS [Extent2] ON [Extent1].[afy_key] = [Extent2].[Key]
Notice that I've told Entity Framework to go ahead and use inherited members and I've also tried telling it not to. It doesn't make a difference what I do I always get this once I implement inheritance.
So the problem I'm having is I do my reverse engineering, I get my POCO classes and then I start writing extension methods to automatically configure some things on each of them using the EF6 Fluent API (OnModelCreating) technique. So let's look at the context object and my configuration for a single object.
public partial class DeferralsContext : DbContext
{
public DeferralsContext()
: base("name=DeferralsContext")
{
Database.SetInitializer(new NullDatabaseInitializer<DeferralsContext>());
}
public virtual DbSet<ac_ar_fiscal_year> ac_ar_fiscal_year { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
#region Map Base Fields
modelBuilder.Entity<ac_ar_fiscal_year>()
.Map(m => { m.MapBaseColumns("afy", "ac_ar_fiscal_year"); })
.ApplyBasePropertyConstraints();
}So I have a DbSet for every Entity type I have mapped. But wait, that's not the way TPC works you say! Good point, I found this out and decided to do it where there is just a single DbSet for my 1 EntityBase object (yes, it is abstract). And when I tried that, I have to change my queries to use context.entities.OfType<realType>(lambda). So great, I do that and then I still map each table to the right type as shown above. Then I get an error that EntityBase doesn't have a Key parameter when the DB initializes. I can set a key in an Attribute, tell it to ignore that field, I can also set it in Fluent API and it doesn't matter, I always get that error. So I went back to where it was much closer to working where I have the DbSet for all my Types and I map each one using the method above. Here is my code for the 2 extension methods so you can see what I'm trying to set for the tables:
public static void MapBaseColumns<TEntityType>(this EntityMappingConfiguration<TEntityType> map, string tablePrefix, string tableName,
string overrideKeyColumnName = null)
where TEntityType : EntityBase
{
if (map == null)
throw new ArgumentNullException(nameof(map),
new Exception("EntityTypeConfiguration cannot be null when mapping base properties."));
if (string.IsNullOrEmpty(tablePrefix))
throw new ArgumentOutOfRangeException(nameof(tablePrefix),
new Exception("Table Prefix is required to map base properties."));
if (string.IsNullOrEmpty(tableName))
throw new ArgumentOutOfRangeException(nameof(tableName),
new Exception("Table Name is required to map base properties."));
map.Property(table => table.Key).HasColumnName(overrideKeyColumnName ?? $"{tablePrefix}_key");
map.Property(table => table.CreatedOn).HasColumnName($"{tablePrefix}_add_date");
map.Property(table => table.CreatedOn).HasColumnName($"{tablePrefix}_add_date");
map.Property(table => table.CreatedBy).HasColumnName($"{tablePrefix}_add_user");
map.Property(table => table.LastUpdatedOn).HasColumnName($"{tablePrefix}_change_date");
map.Property(table => table.LastUpdatedBy).HasColumnName($"{tablePrefix}_change_user");
map.Property(table => table.IsDeleted).HasColumnName($"{tablePrefix}_delete_flag");
map.Property(table => table.EntityKey).HasColumnName($"{tablePrefix}_entity_key");
map.ToTable(tableName);
}
public static EntityTypeConfiguration<TEntityType> ApplyBasePropertyConstraints<TEntityType>(
this EntityTypeConfiguration<TEntityType> config, int columnOrderStart = 0)
where TEntityType : EntityBase
{
config.HasKey(e => e.Key);
config.Property(e => e.Key).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
config.Property(e => e.CreatedBy)
.IsRequired()
.HasMaxLength(64);
config.Property(e => e.LastUpdatedBy)
.HasMaxLength(64);
return config;
}
So my situation is somewhat unique in that I cannot change the names of the columns on my tables but they all have a common set of fields, mostly audit, deleted, and entity key fields. The point is the common set of fields never have the same names, they just follow a pattern and that is prefix_change_user, etc (see above code). I do not want to setup stuff on each of them repeatedly, and furthermore, I don't want to have to set their data during actual interaction with the database in my repository. So, I'm mapping all of them to the same names in my base class so their names are easy things, such as Key, ChangedBy, ChangedOn, etc. This way I can override the SaveChanges method later and just set audit fields on any entity because they'll all have the same name.
So that's my story, and the problems I'm facing are starting to block me from being able to use EF6 as a solution because I'm just about out of time for figuring out a "better" way. I think it is a better way but if I cannot overcome this technology hurdle quick enough, I have to resort back to our old manual ADO.NET stuff, which works, but is something that I believe is part of the problem with my application in the first place, at least the way it's implemented. Can anyone help me out? Not sure if I forgot some relevant code or not. To be clear, there is not a table, nor will there ever be a table, named EntityBase in my DB. I would like EF to treat my derived classes just like they didn't derive anything. IT seems like it does that right up until it creates some SQL to select from them, then it adds that silly join back to the original table. It's basically creating an Extent1 for the base class, then an Extent2 for the local class properties and then trying to join Extent1 to Extent2 and on Extent2 it decides not to realize that the property named Key was mapped to some real column name like xyz_key, instead it uses the name of the C# property "Key" instead of the mapped column name. This seems like it's broken but I'm sure I'm just missing something after reading all about this stuff for the last 3 or 4 nights straight. help!!!!
So as I said, I want to use EF 6.1.3, I want to use Code First as it seems cleaner than static EDMX models but just as complex apparently.
From what I have seen over the years is developers get burnt using code first for complex database schemas.
这篇关于EF6代码优先使用基本类TPC和每个数据库表的DbSet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
