Stored procedure in entity framework 4.3 + mvc 4

Question

My application is using ASP.NET MVC 4 and using stored procedure in EF 4.3

I know, in EF if I am passing parent entity only then also I can add child entity with just in one line of code.

But I am confused how can I insert data in parent-child tables using single stored procedure?

What I want to is my parent's table is having primary key and which I want to insert in child table as foreign key. So how can I do this in one stored procedure using EF 4.3

Answer 1

You don't say what your database is or why you want to use EF4.3 in an mvc4 app or why you want to add entities using a stored procedure rather than just let EF do it for you taking care of the foreign key relationship as it does. You haven't said whether you're using a code first approach or using the entity model design tools

The reason why I bring this up is because ordinarily EF can handle creating parent child entities and will return the entities with populated key values by default and it's easier to code than using stored procedures. If you have to use stored procedures than EF6 has better support for stored procedures then EF4 did so if you must use stored procedures then can you upgrade to EF6?

If you are using code first with EF4 then your basic workflow would be to create a wrapper method for your parent entity and child entity each wrapper method accepts an entity as a parameter the wrapper for the parent entity then builds a database command from the entity with parameters populated with values from the passed in entity it then executes the command and the stored procedure command should return the key value of the new parent entity.

You can then update each of the child entities with the foreign key value and then pass each child entity to the respective wrapper method - which in turn needs to execute the relevant stored procedure which will also return the new primary key value for the child

You can then either just update your passed in entity with the new key values or use the key to get EF to fetch the new entity with the key - the latter is preferable if your stored procedure is doing more than just populating db fields ie calculating values etc.

I don't know what you would do or even if you can do this in the designer - I know that the designer in ef4 has better support for stored procedures than code first. But I would expect that if you're hoping to just pass the object graph to a stored procedures imported into the designer then this isn't going to work ( I don't know that you can't I would be surprised if you could) because you need to be able to pass in a collection of parameters for the child entities which in turn could have child entities.