Entity Framework 4: Mapped Stored Procedure on Model with Additional Parameters

Question

We've started using Entity Framework 4 for data access and have come across an issue or perhaps lack of understanding.

Our current system is heavily reliant on Stored Procedures, these procedure contain some necessary business logic so we need to continue to use these when doing Select/Insert/Update/Delete.

The issue we are having is the following:

We've mapped a table to an entity, let's say for example this is a User entity and has the following properties - UserId, FirstName, LastName

Now in our sproc to insert a user we accept FirstName, LastName, CreatedById as parameters.

As our User Entity has no CreatedById we get an error indicating that no property of our Entity can be mapped to the "CreatedById" parameter.

Now one thing we've tried is to manually add a CreatedById scalar property to our Entity, but this results in the issue that there is no Field in our User table in the data source that maps to CreatedById. In general the additional property that we'd like to pass in is not something that is stored.

Now there is potential solution to this in that we can just map the procedures to Function Imports and not bother with using the .AddObject, .DeleteObject, .SaveChanges way of manipulating our objects but that doesn't feel like the way to go about it.

Answer 1

that's a good question. There are few options i can tell u. instead of mapping the entity to the table, map it a view and have the view return CreatedById and then your problem would be solved.

Second option is to create overloaded stored procedure that takes only FirstName, LastName and calls the actual stored procedure with a default value for CreatedById. You can create overloads at the database layer or create it in the model in the ssdl layer which supports inline stored procedure.

exec myproc @firstName,@LastName,null