Map two different entities to the same table?

Question

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.

Answer 1

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.

Answer 2

I would create a View on the database containing only the data you need and add the View to your entity data model.

If you don't want to modify the database, you can create a Linq to entities or ESQL statement projecting to a POCO class with only the information you need.

public IQueryable<SimpleObject> GetView(DBContext context) {     return  (from obj in context.ComplexObjects             select new SimpleObject() { Property1 = obj.Property1,                                         Property1 = obj.Property2                                       });  }