将两个不同的实体映射到同一个表？ [英] Map two different entities to the same table?

问题描述

我有一个表在我的数据库中有很多字段。大部分时间我需要所有的这些字段。有一种情况，但是，我只需要几个字段，我正在加载大量的行。

我想做的是添加在实体中手动，然后简单地将其映射到原始表，但删除我不需要的列。我设置了这一切，但我得到了相当自我解释的错误：

映射片段的问题
... EntitySets'FmvHistoryTrimmed'和
'FMVHistories'都映射到
表'FMVHistory'。他们的主键
可能会发生冲突。

有没有其他方法我应该这么做？再次，大部分时候所有的列都被使用，所以我不想修剪原始实体，并将额外字段放入一个复杂的类型。

解决方案

您不能将两个常规实体映射到同一个表中。您有几种选择：

1. 使用表拆分。


2. 使用带有投影到非实体类型的自定义查询（如提供@Aducci）


3. 使用QueryView


4. 使用数据库视图或直接定义查询

表拆分

表拆分允许您以1：1的关系将表映射到两个实体。第一个实体将只包含你需要的PK和子集的子集。第二个实体将包含所有其他字段和PK。两个实体将包含彼此的导航属性。现在如果你只需要一个字段的子集，你将会查询第一个实体。如果您需要所有字段，您将查询第一个实体并将navifation属性包含在第二个实体中。如果需要，您也可以懒惰加载第二个实体。

QueryView

QueryView 是直接在映射（MSL）中定义的ESQL查询，它是映射到新的只读实体类型。您可以使用QueryView将完整实体的投影定义为subentity。必须在EDMX中手动定义QueryView（设计器中不可用）。由于我知道QueryView在Code中不可用，但实际上与非实体类型的自定义投影相同。

定义查询

定义查询是自定义的直接在您的存储模型（SSDL）中定义的查询。通常在映射到数据库视图时使用DefiningQuery，但可以将其用于任何自定义SQL SELECT。您将将查询的结果映射到只读实体类型。必须在EDMX中手动定义DefiningQuery（在设计器中不可用）。它也不是直接可用的代码第一，但它实际上是一样的调用 SqlQuery 在 DbDatabase 。 DefiningQuery的问题是，一旦您在SSDL中手动定义它，就不能使用Update数据库中的更新模型，因为此操作将替换完整的SSDL并删除查询定义。

I have a table in my database with a lot of fields. Most of the time I need all those fields. There is one scenario, however, where I only need a few of the fields, and I am loading a ton of rows.

What I'd like to do is add in an Entity manually, and then simply map it to the original table, but delete the columns I don't need. I set this all up, but I get the rather self-explanatory error of:

Problem in mapping fragments ...EntitySets 'FmvHistoryTrimmed' and 'FMVHistories' are both mapped to table 'FMVHistory'. Their primary keys may collide.

Is there some other way I should go about this? Again, most of the time all of the columns are used, so I don't want to trim down the original entity and put the "extra" fields into a complex type.

解决方案

You can't map two regular entities into same table. You have several choices:

1. Use table splitting.
2. Use custom query with projection to non entity type (as @Aducci proposed)
3. Use QueryView
4. Use database view or directly DefiningQuery

Table splitting

Table splitting allows you to map a table into two entities in 1:1 relation. First entity will contain only PK and subset of fields which you need always. Second entity will contain all other fields and PK. Both entities will contain navigation property to each other. Now if you need only subset of fields you will query first entity. If you need all fields you will query first entity and include navifation property to second entity. You can also lazy load second entity if you need it.

QueryView

QueryView is ESQL query defined directly in your mapping (MSL) and it is mapped to new readonly entity type. You can use QueryView to define projection of your full entity into subentity. QueryView must be defined manually in EDMX (it is not available in designer). As I know QueryView is not available in Code first but it is actually the same as custom projection to non entity type.

DefiningQuery

DefiningQuery is custom query defined directly in your storage model (SSDL). DefiningQuery is usually used when mapping to database views but you can use it for any custom SQL SELECT. You will map the result of the query to readonly entity type. DefiningQuery must be defined manually in EDMX (it is not available in designer). It is also not directly avaliable in Code first but it is actually the same as calling SqlQuery on DbDatabase. The problem with DefiningQuery is that once you manually define it in SSDL you can't use Update model from database because this operation replaces complete SSDL and deletes your query definition.

这篇关于将两个不同的实体映射到同一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！