Entity Framework Core: Querying views, but manipulating tables

Michael Bisbjerg

• 5 min read
Entity Framework Core: Querying views, but manipulating tables

I had a use case for EF Core, where I wanted to use Entity Framework Core to query a database view while supporting updating the database through its classic add/update/delete methods. Specifically, I’d like to enrich my entities with extra info, such as looking up a value, much like a computed column – but since it’s in the database, I can perform filtering on these queried values. The proposed solutions may not scale at all, so reader discretion is advised.

Example problem

Imagine an entity, Entity, which has the properties (Id, DeviceId) and another entity, Friendly, which has the properties (DeviceId, FriendlyName). I want to be able to perform queries that allow the user to filter on FriendlyName, and I want to make this as seamless as possible in EF. Added to that, not all DeviceId's may have FriendlyNames, so it's an optional relationship.

This example could also extend into more complex relationships, like having a spatial coordinate and wanting a "location name" for this coordinate which requires looking up in another table containing polygons - so it may not always be exact value matching.

Things that didn't work

I've tried several things.

For starters, a Computed Column would've been awesome, but neither MySQL nor PostgreSQL allow a lot of stuff here. Typically, anything outside the current table (and row) is disallowed (functions, stored procedures, queries,  ..).

Create the entity, Entity with (Id, DeviceId, FriendlyName), but modify the table mapping such that FriendlyName is:

  • Not mapped: This makes EF not query the field at all
  • Mapped, but marked as Database Generated on Add and Update: This queries the field, and at the same time avoids insert / updating it - but it requires the property to exist in the table. This would be great with Computed Columns, but they are unfortunately quite limited in MySQL and PostgreSQL.

Another approach is using a view, where the view performs the necessary query to enrich the table with the extra property FriendlyName.

  • Use the Fluent API to map the entity to a Table and a View at the same time: This has the fun side effect that insert/add/update seem to happen on the table, but querying happens in the view. Combine this with the above and we can avoid attempting to write to the view-only FriendlyName. Unfortunately, in the case of adding, EF tries to fetch the generated value for FriendlyName, not from the view, but from the table. This obviously fails as the table does not have the property.

EF also supports some strategies to split entities up into multiple tables. This has the drawback that these relationships must exist (they're required), and that they must work as foreign key relationships (exact value matching). Other than that, this works as a form of automatic joining of tables to perform stuff, which is also acceptable for me.

Like splitting, there is also a strategy of "discriminating" multiple types into the same table. This can be done with for example an Entity and EnrichedEntity on top of a view. In this case, we map the two types to the same underlying view, and in our application, we add/update/delete the Entity type. At query time though, EF will create either Entity or EnrichedEntity types, based on the existence of the FriendlyName property. The drawback here is obviously the two types needed, and that queried entities cannot be served back to for example delete or update, as EF will attempt to manipulate the read-only FriendlyName field.

And finally, in EF 7, a new SplitToTable and SplitToView were introduced. These would've been amazing if they could mix. The SplitToXXX functions are an extremely convenient shorthand to ensure that a single entity can live in multiple tables or views. I would have liked to use it with a table for my entity and a view for my computed column(s). I would have liked EF to query both the table and the view, but only manipulate the table - unfortunately splits have to be across only tables or only views.

Things that might work, an auto-include relationship with no foreign key

We all know we can make entities related like the following pseudo code snippet. What this gives us, is an automatic join when querying Entity to also populate FriendlyEntity. 

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

// Join the two in model building
var entity = modelBuilder.Entity<Entity>();
entity.HasOne(s => s.FriendlyEntity)
      .WithMany()
      // Prevent setting the relationship to null at any point
      .OnDelete(DeleteBehavior.ClientNoAction)
      .HasForeignKey(s => s.DeviceId)
      .HasPrincipalKey(s => s.DeviceId)
      .IsRequired(false);
entity.Navigation(s => s.FriendlyName).AutoInclude();

While the relationship technically is optional, at least in my experience with Pomelo, the migrations generated will create a foreign key constraint. This means that I cannot add a new Entity that does not have a corresponding Friendly entity in the database. If we hack out that foreign key constraint from the migrations, this is a completely valid solution. It requires some maintenance though to keep the FK relationship out of migrations (EF will assume it exists and possible update it, delete it etc. in the future).

Things that might work, a view and stored procedures

If we create an entity that matches a view we want, we can then seamlessly query on that view and let the DB do all the heavy lifting of joining stuff up. EF will allow us to update the entities, as some views are updateable, but most likely my views won't be: they join tables. What I instead can do, is map the mutations to SP's, and simply use those to redirect add/update/delete to the backing table.

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

// Map to a view in model building
var entity = modelBuilder.Entity<Entity>();
entity.ToView("entity_view");

// Map to stored procedures
entity.InsertUsingStoredProcedure("entity_sp_insert", sp =>
        {
            sp.HasParameter(s => s.Id, s => s.IsInputOutput());
            sp.HasParameter(s => s.DeviceId);
            sp.HasParameter(s => s.FriendlyName); // Ignore this somehow
        });
entity.DeleteUsingStoredProcedure("entity_sp_delete", sp =>
        {
            sp.HasOriginalValueParameter(s => s.Id);
        });
entity.UpdateUsingStoredProcedure("entity_sp_update", sp =>
        {
            sp.HasOriginalValueParameter(s => s.Id);
            sp.HasParameter(s => s.DeviceId);
            sp.HasParameter(s => s.FriendlyName); // Ignore this somehow
        });

// Map to a table to avoid #28703, but avoid creating migrations
entity.ToTable("entity_table", x => x.ExcludeFromMigrations())

Due to a bug (missing feature?) in EF Core 7, the stored procedures cannot be used with a view, but only with a table. So if we also map the entity to a table, we bypass a validation rule which then ensures we use the SPs to add/update/delete entities but query the view.

Using this approach, we query a view, entity view, at query time, but call the three provided stored procedures on mutations. We of course need to create the view etc., but that's a task for the reader.

As a major bonus with views, we can do any kind of logic at query time (with caveats for heavy calculations of course) such as joining on special criteria or calling functions like the spatial features databases have.