Entity Framework using views and stored procedures.

Question

I have the following basic table structure (that I'm not allowed to change) in my database:

Address
(PK) AddressId
Line1
...

Person
(PK) PersonId
(FK) AddressId
FirstName
...

Student
(PK) StudentId
(FK) PersonId
StudentNumber
...

I would like to combine Person and Student into a Student entity using the Table-per-Type approach. However, these tables don't conform to EFs restriction that the sub-table (Student) must have the PK and FK be the same column. Unlike in my db, which they are seperate columns.

My next idea was to create a view and create an entity off the view. I create a view called StudentView with a join and all the columns. I then imported this view into the EF model. All was fine until I tried to update. Since views can't update, I created Insert/Update/Delete stored procedures and bound them into my Student entity (I did the extra sp paramaters for all FK relationships).

However, now I'm getting the following error:

Error 2027: If an EntitySet or AssociationSet includes a function mapping, all related entity and AssociationSets in the EntityContainer must also define function mappings.

I haven't been able to find much info anywhere about this. It seems to me that it means that if one entity uses stored procedures, all entities do (or at least all entities that are referenced, which in my case equates to roughly all). However, other error messages I've received from EF have been deceiving.

Does anyone know how to get past this? Also, any suggestions on how to use Table-per-Type in my case (which I don't think is possible) would be helpful too.

Answer 1

I figured this out. It turns out it was because I had set some relationships to be 0..1:1. It seems when this happens, anything with that relationship type must use stored procedures too. Setting the relationships back to 0..1:* fixed my problem.

Answer 2

congralations! extremly helpfull article I think. Is this right? No, I made joke.