EntityFramework 6 + SQL View DML Triggers
Disclaimer: Don't do this. Mapping stored procedures will work out much better than this hack. I did this mostly to see if I could.
I have a number of tables for documentation on network devices.
One table, let's call it deviceDetail, has a number of commonly-shared fields
like associated IP(s), description, make/model, location, and what type of
device it is.
Based on the type of device — firewall, printer, etc — extended properties
are stored in category-specific tables,
with a foreign key to deviceDetail.
To make things simple in my ASP.NET MVC backend, I created category-specific
views which not only join everything together for easy querying,
but also have INSTEAD OF INSERT / UPDATE/ DELETE triggers that translate
writing to those views into writing to both deviceDetail and the category table.
This turned out to not be so simple, as EntityFramework 6 was not at all a
happy camper about this setup.
In order for EF to write to these views like tables, the simplest solution I
could find was to lie to EF and make it think they're tables.
To do this, open your .edmx file in a text editor and locate the model definition:
<EntitySet Name="MyView" store:Type="Views" store:Schema="MySchema" ...
Change the type, and remove 'store:' from Schema:
<EntitySet Name="MyView" store:Type="Tables" Schema="MySchema" ...
Remove the DefiningQuery field for your model, if present.
Either in the .edmx file or graphically in the Visual Studio designer:
- Make sure the Entity Key is set correctly. EF had 'helpfully' set all my non-null fields to be entity keys.
- Set any auto-incrementing fields to have
StoreGeneratedPattern="Identity"
For the latter point to work correctly, your INSERT trigger needs
OUTPUT INSERTED.primaryKey, INSERTED.foreignKey.