Map two different entities to the same table?

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 ro开发者_如何学编程ws.

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.

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
                                      }); 
}

There is a trick which I used to get multiple entities to map to one table.

First your entities must derive from a common type. This base type itself will not be part of your context.

Give the context a fake table name, which can be parsed to get the original table name. I used the table name format: $$$TableName$$$. It can have 3 or more $.

Then intercept the command and replace the command text.

The hierarchy:

    class MyEntityBase
    {
        // Common properties here
    }

    [Table("MyTable")]
    class MyEntityV1 : MyEntityBase
    {
    }

    [Table("$$$MyTable$$$")]
    class MyEntityV2 : MyEntityBase
    {
    }

The interceptor:

class EntityNameReplacerInterceptor: DbCommandInterceptor
{
        private static Regex regex = new Regex("([\\$]{3,})(.+)\\1", RegexOptions.Compiled);

        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            ManipulateCommand(command);
            return base.ReaderExecuting(command, eventData, result);
        }

        public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
        {
            ManipulateCommand(command);
            return base.NonQueryExecuting(command, eventData, result);
        }

        public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default)
        {
            ManipulateCommand(command);
            return base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
        }

        public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
        {
            ManipulateCommand(command);
            return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
        }

        public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
        {
            ManipulateCommand(command);
            return base.ScalarExecuting(command, eventData, result);
        }

        public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
        {
            ManipulateCommand(command);
            return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
        }

        private void ManipulateCommand(DbCommand result)
        {
              result.CommandText = regex.Replace(result.CommandText, "$2");
        }
}

Configuring The DbContext:

DbSet<MyEntityV1> V1s { get; set; }
DbSet<MyEntityV2> V2s { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.AddInterceptors(new EntityNameReplacerInterceptor());
}