实体框架 4 每个层次结构表 - 如何定义子级的导航属性?
我目前有一个实体框架 4.0 模型,其中包含每个类型的表 (TPT),但存在一些性能问题(大量 LOJ/CASE 语句),以及两个特定域区域之间的问题映射(多对多)许多)。
我决定尝试一下TPH。
我有一个名为“位置”的实体,它是抽象的,也是所有其他实体的基础。
然后我有“国家”、“城市”、“州”、“街道”等从位置派生。
“LocationType”是鉴别器。
该部分工作正常,但我在尝试定义派生类型的导航属性时遇到问题。
例如,“State”有一个“Country”,所以我应该能够执行此操作:
var state = _ctx.Locations.OfType<State>().Include("Country").First();
var countryForState = state.Country;
但这需要一个名为“Country”的导航属性“国家”派生实体。我该怎么做?当我从数据库生成模型时,我有一个表,其中所有 FK 都指向同一个表中的记录:
(注意:我在数据库中手动创建了这些 FK)。
但是 FK 作为导航放置在“位置”实体上,那么我如何将这些导航属性向下移动到派生实体呢?我无法复制+粘贴导航,也无法“创建新的导航属性”,因为它不允许我定义开始/结束角色。
我们该怎么做?
对于TPH,我们还不清楚我们是否可以模型优先,或者我们必须从数据库开始,修复模型然后重新生成数据库。我还没有在互联网上找到关于如何定义 TPH 儿童导航的好例子。
注意:我不想先进行代码。我当前的解决方案具有带有 EDMX 的 TPT 和纯 POCO,我希望不会影响域模型/存储库(如果可能),而只是更新 EF 模型/数据库。
编辑
仍然没有解决方案 - 但是我尝试先进行模型,然后进行添加 ->新关联,它实际上允许我向派生实体添加导航。但是当我尝试“从模型生成数据库”时,它仍然尝试为“Location_Street”、“Location_Country”等创建表。这几乎就像 TPH 无法首先完成模型一样。
编辑
这是我当前的模型:
我当前收到的验证错误:
错误 1 错误 3002:映射问题 从行开始的片段 359:潜在的运行时违规 表位置的键 (Locations.LocationId):列 (Locations.LocationId) 映射到 EntitySet NeighbourhoodZipCode 的 特性 (NeighbourhoodZipCode.Neighbourhood.LocationId) 在概念方面,但他们没有 形成EntitySet的关键属性 (NeighbourhoodZipCode.Neighbourhood.LocationId, NeighbourhoodZipCode.ZipCode.LocationId)。
只是想我会继续编辑这个问题,并编辑有关我目前所处位置的内容。我开始怀疑带有自引用 FK 的 TPH 是否可能。
编辑
所以我发现了上面的错误,那是因为我缺少邻里邮政编码多对多的连接表。
添加连接表(并将导航映射到该表)解决了上述错误。
但现在我收到此错误:
错误 3032:映射问题 从第 373 行开始的片段, 382:条件成员 'Locations.StateLocationId' 有 重复的条件值。
如果我看一下 CSDL,这里是“CountyState”的关联映射(一个州有很多县,一个县有 1 个州):
<AssociationSetMapping Name="CountyState" TypeName="Locations.CountyState" StoreEntitySet="Locations">
<EndProperty Name="State">
<ScalarProperty Name="LocationId" ColumnName="StateLocationId" />
</EndProperty>
<EndProperty Name="County">
<ScalarProperty Name="LocationId" ColumnName="LocationId" />
</EndProperty>
<Condition ColumnName="StateLocationId" IsNull="false" />
</AssociationSetMapping>
就是那个 Condition ColumnName="StateLocationId"
正在抱怨,因为 ZipCodeState
关联也存在这种情况。
但我不明白。所有实体的鉴别器都是唯一的(我进行了三次检查),我本以为这是一个有效的场景:
- 县有一个州,由 StateLocationId(位置表)表示
- ZipCode 有一个州,由 StateLocationId(位置表)表示)
这在 TPH 中无效吗?
I currently have a Entity Framework 4.0 model in place with Table Per Type (TPT), but there are a few performance issues (lots of LOJ's/CASE statements), as well as an issue mapping between two particular domain areas (many-to-many).
I've decided to try out TPH.
I have an entity called "Location" which is abstract, and the base for all other entities.
I then have "Country", "City", "State", "Street", etc which all derive from Location.
"LocationType" is the dicriminator.
That part is working fine, but i'm having issues trying to define navigational properties for the derived types.
For instance, a "State" has a single "Country", so i should be able to do this:
var state = _ctx.Locations.OfType<State>().Include("Country").First();
var countryForState = state.Country;
But this would require a navigational property called "Country" on the "State" derived entity. How do i do this? When i generate the model from the database, i have a single table with all the FK's pointing to records in the same table:
(NOTE: I created those FK's manually in the DB).
But the FK's are placed as nav's on the "Location" entity, so how do i move these navigational properties down to the derived entities? I can't copy+paste the navs across, and i can't "create new navigational property", because it won't let me define the start/end role.
How do we do this?
It's also not clear with TPH if we can do it model-first, or we HAVE to start with a DB, fix up the model then re-generate the DB. I am yet to find a good example on the internet about how to define navs on children with TPH.
NOTE: I do not want to do code-first. My current solution has TPT with the EDMX, and pure POCO's, i am hoping to not affect the domain model/repositories (if possible), and just update the EF Model/database.
EDIT
Still no solution - however im trying to do model-first, and doing Add -> New Association, which does in fact allow me to add a nav to the derived entities. But when i try and "Generate database from Model", it still tries to create tables for "Location_Street", "Location_Country" etc. It's almost like TPH cannot be done model first.
EDIT
Here is my current model:
The validation error i am currently getting:
Error 1 Error 3002: Problem in mapping
fragments starting at line
359:Potential runtime violation of
table Locations's keys
(Locations.LocationId): Columns
(Locations.LocationId) are mapped to
EntitySet NeighbourhoodZipCode's
properties
(NeighbourhoodZipCode.Neighbourhood.LocationId)
on the conceptual side but they do not
form the EntitySet's key properties
(NeighbourhoodZipCode.Neighbourhood.LocationId,
NeighbourhoodZipCode.ZipCode.LocationId).
Just thought i'd keep editing this question with edit's regarding where i am currently at. I'm beginning to wonder if TPH with self-referencing FK's is even possible.
EDIT
So i figured out the above error, that was because i was missing the join-table for the Neighbourhood-ZipCode many to many.
Adding the join table (and mapping the navs to that) solved the above error.
But now im getting this error:
Error 3032: Problem in mapping
fragments starting at lines 373,
382:Condition members
'Locations.StateLocationId' have
duplicate condition values.
If i have a look at the CSDL, here is the association mapping for "CountyState" (a State has many counties, a County has 1 state):
<AssociationSetMapping Name="CountyState" TypeName="Locations.CountyState" StoreEntitySet="Locations">
<EndProperty Name="State">
<ScalarProperty Name="LocationId" ColumnName="StateLocationId" />
</EndProperty>
<EndProperty Name="County">
<ScalarProperty Name="LocationId" ColumnName="LocationId" />
</EndProperty>
<Condition ColumnName="StateLocationId" IsNull="false" />
</AssociationSetMapping>
It's that Condition ColumnName="StateLocationId"
which is complaining, because ZipCodeState
association also this condition.
But i don't get it. The discriminators for all entities are unique (i have triple checked), and i would have thought this was a valid scenario:
- County has a single State, denoted by StateLocationId (Locations table)
- ZipCode has a single State, denoted by StateLocationId (Locations table)
Is that not valid in TPH?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
所以我解决了一些问题,但我碰壁了。
首先,当您在数据库端创建自引用 FK 时,当您尝试“从数据库更新模型”时,实体框架会将这些导航属性添加到主基本类型中,因为它没有明确的 TPH 意义 - 您需要在模型端执行此操作。
但是,您可以手动将导航属性添加到子类型。
写入此错误:
那是因为我有一个名为“Location_State”的 FK,我试图将其用于“ZipCode_State”关系和“City_State”关系 - 这不起作用(仍然不知道为什么)。
因此,为了解决这个问题,我必须添加额外的列和额外的 FK - 一个称为“ZipCode_State”,另一个称为“City_State” - 显然导航和物理 FK 之间必须是 1-1。
那是我的鉴别器场。在数据库方面,它不可为空。
我阅读了有关此问题的帖子,他们说您需要将关系从 0..* 更改为 1..* - 但我的关系已经是 1..*。
如果您查看上面我的“位置”实际数据库表,所有 FK 都是可以为空的(它们必须是)。因此我开始想知道我的关系是否应该是 0..*。
但由于 TPH,它们可以为空 - 并非所有“位置”都有“状态”。但如果该位置是“城市”,那么它必须有一个“州”。
这个问题进一步安慰了我的感觉: ADO EF - TPH 中派生类型之间映射关联时出错
我实际上正在尝试该解决方法(在我遇到它之前),并且该解决方法对我不起作用。我什至尝试将所有关系从 1..* 更改为 0..*,但仍然没有成功。
在这里浪费了太多时间,我已经回到TPT了。
归根结底,使用 TPH,我会得到一个大得离谱的表,其中有很多很多冗余的、可为空的列。 JOIN 方面,效率更高。但至少对于 TPT,我不需要具有可为空和自引用的 FK。
如果有人有解决这个问题的方法,请告诉我。但在那之前,我会坚持使用 TPT。
So i solved a few of my issues, but i hit a brick wall.
First of all, when you create self-referencing FK's in the database side, when you try and "Update Model from Database", Entity Framework will add these navigational properties to the main base type, as it has no explicit sense of TPH - you need to do this in the model side.
BUT, you can manually add the navigational properties to the child types.
WRT this error:
That was because i had an FK called "Location_State" which i was attempting to use for the "ZipCode_State" relationship, AND the "City_State" relationship - which does not work (still no idea why).
So to solve that, i had to add extra columns and extra FK's - one called "ZipCode_State", and another called "City_State" - obviously it has to be a 1-1 between navs and physical FK's.
That is my discriminator field. In the database side, it is not nullable.
I read threads about this issue, and they said you need to change the relationships from 0..* to 1..* - but my relationships already were 1..*.
If you look at my "Locations" actual database table above, all the FK's are nullable (they have to be). Therefore i started wondering if my relationships should be 0..*.
But they are nullable because of the TPH - not all "Locations" will have a "State". But if that Location is a "City", then it HAS to have a "State".
My feelings were further comforted by this SO question: ADO EF - Errors Mapping Associations between Derived Types in TPH
I was actually trying that workaround (before i even came across it), and the workaround does not work for me. I even tried changing all the relationships from 1..* to 0..*, and still no luck.
Wasting too much time here, I've gone back to TPT.
At the end of the day, with TPH i would have had a ridiculously large table, with lots and lots of redundant, nullable columns. JOIN-wise, it's more efficient. But at least with TPT i am not required to have nullable and self-referencing FK's.
If anyone has a solution to this problem, let me know. But until then, im sticking with TPT.