Entity Framework: Mapping sproc results to existing EF Entity

Question

I am trying to use EF with an existing DB. I brought in a Client table into my data model and let EF create a Client entity. I have a sproc, GetClientSearch, that only returns 5 out of the 15 columns from the Client table becuase that is all that is needed for that call.

Here's what I've done so far:

• Added the sproc to Function Imports and set the proc to map to the Client entity.

• When I execute the proc through the Context, I get "The data reader is incompatible with the specified 'GAINABSModel.Client'. A member of the type, 'MiddleInitial', does not have a corresponding column in the data reader with the same name." exception. (MiddleInitial is not one of the columns returned in the proc)

• I know that I can create a new entity that maps to the proc, but I don't want to do that for every proc I have to import into my model.

• Given that the DB is currently in use in production, changing stored procs to map to my current entities may not be an option.

Currently using EF 4 and VS 2010.

So, is there a way to map the results of the sproc to the Client entity, even though the columns returned are not 1:1 with the properties of the EF entity?

Answer 1

Yep, one of my many pain points in EF.

If you can't modify the SP's, your best bet might be to create "wrapper" SP's on top of the existing SP's.

In other words, EF-serving SP's that call into the existing ones, and return NULL for the columns you don't need, but are required for the entity.

Of course the better option would be to create the entities properly.

Another option is to use ObjectContext.Translate<T>, which basically performs a L-R between the SPROC results and the entity you supply.

If the result set doesn't contain the field, then the property on the object will be null.

Which is probably what you want.

Answer 2

Am running into the same Issues. Suppose i have UserEntity created out of the User Table and have 3 procedures.

1. AuthenticateUser - returns 4 columns from the user table after authentication
2. RetriveUser - Returns 10 columns from the user table
3. GetUserName - return UserID and UserName only for dropdown purpose.

If we create different entities for each of the different SP. It would result in bad design because of duplication.

I have no other way of using same entity for all these SP's.

Overall, i don't recommend entity framework atleast for legacy applications in production.(where you can not update your Sp's also.)