Use Foreign Key on SQL View in Entity Framework

Question

I'm relatively new to Entity Framework.

I created a database with two tables: Accounts and Assignments.

Accounts has an AccountId primary key which is used as a foreign key in the Properties table. I really like that the Entity Framework automatically picks up the foreign key relationship and allows me to access rows in Assignments as a property of each row from Accounts.

I went ahead and created a new View object that returns all the columns from Accounts along with some other information. However, when I get the data from the View in SQL using the Entity Framework, it is no longer automagically referencing the associated rows in the Assignments table.

How can I get the desired behavior using Views with Entity Framework

score 5 · Accepted Answer · edited May 23 '17 at 12:01

5

This can work in EF, but the EF designer can't infer your FK out of the view, since the DB doesn't tell it where the FKs on view columns are (since they're naturally on the underlying tables, not the view itself).

You'll need to manually edit your EDMX, either via the designer or in XML, to get these properties.

edited May 23 '17 at 12:01

Community

1
1

answered Aug 13 '12 at 18:16

Craig Stuntz

125,891
12
252
273

3

It's been more than 4 years, is this answer still the only way? I could not spot an alternative automated solution when searching on the web. – John L. Dec 29 '16 at 07:17

score 0 · Answer 2 · answered Oct 25 '19 at 11:59

The solution that worked for me was to include the appropriate data in the view so I didn't need to use the FK.

For example, include the PK's for the Assignment table in the view by adding the appropriate SQL to the view.

That way I could join to the view in LINQ without needing to refer to a generated property in the EDMX.

Use Foreign Key on SQL View in Entity Framework

2 Answers2