How to create a relationship between table and view in the Entity Framework

Question

I have an Entity Framework model that was generated by the Visual Studio 2008 wizard based on a Sql Server 2008 database.

The model has a view that logically is joined in a many-to-many relationship with another table through a join table (enforced in the database by an insert/update trigger). Both tables and the view are part of the model, but because you cannot have a foreign key constraint on a view, it does not have the relationship between the view and the join table.

Is it possible to create a relationship in the Entity Framework model for this link between the join table and the view?

Thank you for any help.

Answer 1

Yes, you can do this, but the GUI designer won't be able to infer it for you.

The first thing the need to do is configure the view correctly. The designer cannot infer the primary key, so you will need to supply that information.

You can now right-click in the empty space in the designer and then choose to add an association. Define the association between your view and table, setting the cardinality correctly.

In EF 1, you will need to remove the FK fields from the client schema by selecting them in the designer and pressing delete. This is because, in EF 1, you cannot have the same field mapped to both an association and in a scalar property. In EF 4, you can keep the FK fields if you use FK associations, or you can use independent associations which behave like EF 1.