How to map a view derived from a table, including fk

[statler]

I have a table and a view based on the table. They share exactly the same config except for an additional aggregate column on the view. Let's call them Order and OrderExt.

I want to use my configuration for Order and OrderExt. Basically this:

public class OrderExt : Order {
        public string CopyToNameCSV {get;set;}
} 

I can do this and if I set the .HasBaseType(null) for the config of OrderExt, it is starting to look good.

Where I run into problems is the configuration of navigation properties. When I define them, obviously I don't want to double up the properties at the other end, but I cannot find a way to just define the relationship as one way. e.g. if I define it normally for Order it would look like this. But if I do it for OrderExt, I cannot use the same collection property, even though they are essentially the same.

builder.HasOne(a => a.Customer) .WithMany(b => b.Orders) .HasForeignKey(c => c.CustomerId); 

Any ideas how I can do this? Alternatively is there some way to hack the discriminator? This seems like a pretty common sort of a requirement.

Basically, I need to extend my table in a way I cannot do with ef, so I do it with a view. I need to worry the view in ef

[statler]

So it turns out this is far easier it first appears. It is really poorly documented though. In the end I stumbled upon the answer in the release notes for EF5 under the heading Flexible Entity Mapping https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew#flexible-entity-mapping

I couldn't find anywhere where this functionality is explicitly described - if it has been missed, maybe consider an update to the docs? This is actually a super useful thing to be able to do and I could find no other way of doing it.

In a nutshell, it is possible to map to both a table and a query. The table is used for data commits and the query is used for data retrieval. This is done by specifiying both ToTable and ToView for the entity.

What I am trying to achieve is to get an additional aggregated property when I query my entity. Using my example above, it would be something like:

public class OrderExt : Order
{
    public string CopyToNameCSV {get;set;}
}

I had to change my model away from inheritance. Instead of above I added a property to the base class (I autogen my models, so it is a partial) and added a config for the new column. The column isn't used for committing data.

public partial class Order 
{
    public string CopyToNameCSV {get;set;}
}

In my case though there was a complication. The whole point of my query was to provide additional aggregated data - a CSV summary of a child field.

Defining both ToTable("Order").ToView("OrderExt") worked great for the SELECT queries, but as soon as I inserted, an exception was thrown because EF tried to save the CopyToNameCSV property which doesn't exist on the table.

To resolve this I had to configure the mapping so that the property is ignored on insert (I did it for update as well to be safe). The following extension method makes it easy to do this with the fluent syntax

        public static PropertyBuilder<string> IsReadOnly(this PropertyBuilder<string> property)
        {
            property.Metadata.SetBeforeSaveBehavior(PropertySaveBehavior.Ignore);
            property.Metadata.SetAfterSaveBehavior(PropertySaveBehavior.Ignore);
            return property;
        }

The configuration then becomes:

modelBuilder.Entity<Order>.Property(x => x.CopyToNameCSV ).HasColumnName(@"CopyToNameCSV").IsReadOnly().HasColumnType("nvarchar(max)").IsRequired(false);

[AndriySvyryd]

I couldn't find anywhere where this functionality is explicitly described - if it has been missed, maybe consider an update to the docs? This is actually a super useful thing to be able to do and I could find no other way of doing it.

It's already mentioned here: https://learn.microsoft.com/ef/core/modeling/entity-types?tabs=data-annotations#view-mapping

Defining both ToTable("Order").ToView("OrderExt") worked great for the SELECT queries, but as soon as I inserted, an exception was thrown because EF tried to save the CopyToNameCSV property which doesn't exist on the table.

It would be handled by #33676