I had a use case for EF Core where I wanted to query a database view while still supporting classic add/update/delete methods on the base table. Specifically, I wanted to enrich entities with computed values, like a lookup or label, but allow full CRUD support.

Example Problem

Suppose we have two entities:

  • Entity: (Id, DeviceId)
  • Friendly: (DeviceId, FriendlyName)

The goal is to filter Entity records by FriendlyName, even though it’s not part of the table directly.

Things That Didn’t Work

  • Computed Columns: MySQL and PostgreSQL restrict computed columns to functions on the same table.
  • Mapping FriendlyName into Entity: Either EF doesn’t query the column, or tries to fetch it from the table (not the view).
  • Mapping to Both a Table and a View: Works until EF tries to return values from insert, then fails as the base table lacks the extra field.
  • Entity Splitting/Table Splitting: Requires the relationship to always exist and be strict FK-based.
  • SplitToTable/SplitToView in EF7: Doesn’t support mixing views and tables in one entity.

Possible Solutions

Auto-Included Relationship with No Foreign Key

class Entity { string Id; string DeviceId; Friendly FriendlyEntity; }
class Friendly { string DeviceId; string FriendlyName; }

modelBuilder.Entity<Entity>()
    .HasOne(e => e.FriendlyEntity)
    .WithMany()
    .HasForeignKey(e => e.DeviceId)
    .HasPrincipalKey(f => f.DeviceId)
    .IsRequired(false)
    .OnDelete(DeleteBehavior.ClientNoAction);

entity.Navigation(e => e.FriendlyEntity).AutoInclude();

This works if you suppress the generated FK constraint in migrations. Otherwise, EF will enforce the FK and prevent inserts for unmatched values.

View + Stored Procedures

Create a view that joins Entity and Friendly, and map Entity to that view:

entity.ToView("entity_view");

Then configure insert/update/delete via stored procedures:

entity.InsertUsingStoredProcedure("entity_sp_insert", sp => {
    sp.HasParameter(e => e.Id, p => p.IsInputOutput());
    sp.HasParameter(e => e.DeviceId);
    // Skip FriendlyName
});

entity.ToTable("entity_table", t => t.ExcludeFromMigrations());

Note: EF Core bug #28703 prevents stored procedures from working with views directly, so map to the table but mark it excluded from migrations.

This lets EF query via the view and write via the procedures.

Conclusion

You can use views in EF Core while manipulating tables via stored procedures or relationship joins. Just be aware of the limitations and quirks in the provider and EF version.