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.

Kauai

I've got a workaround, from Aducci, but I'll leave this unanswered if anyone has an actual EF way to accomplish this in the way I was originally looking for – Kauai

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.

Dennet

Here is a very simple tutorial from MSDN that walks through the entity splitting technique: msdn.microsoft.com/en-us/data/jj715646.aspx However, depending on your data model structure with Primary Keys you might have to reshape the model. – Space

Could table splitting be implemented in hibernate? – Idol

I tried to implement Table Splitting as described here. What happened is that EF insisted on renaming the shared fields to XXXX1. E.g., Link {int LinkId; int DocIdRight; int DocIdLeft} and LinkPartial {int LinkId; int DocIdRight;} ==> When updating a LinkPartial object EF tried to update both DocIdRight and DocIdRight1. – Sanhedrin

Ladislav, do you know by any chance if there is an update on this 5.5 years later? Any easier way for Code-first approach may be?.. Thanks. – Vermicelli

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

Monad

I definitely didn't want to create a view, but the custom linq expression should work. There's also a 1..0|1 relationship on there as well, but a simple linq outer join should pull that in too. I just thought it would be a bit more elegant to map this in the EF designer. Thank you for your answer! – Kauai

Answer 3

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

Adorne

Answer 4

To address this with Table-per-Hierarchy (TPH) inheritance mapping in Entity Framework, create an inheritance hierarchy in your model. This includes an entity with all fields and another with a subset of fields. This setup maintains the original table structure for most cases while accommodating scenarios with fewer fields needed.

For detailed implementation and examples of TPH, refer to Microsoft's official documentation on Entity Type Hierarchy Mapping, which offers comprehensive guidance on inheritance mapping strategies in Entity Framework.

Ratafia