Entity Framework – Using Select stored procedures for entities having same column names

I have this following model. Check the “ModifiedDate” which is common in both the entities.

image

And both tables in the database have ModifiedDate columns. Following is the stored procedure which gets all the contacts along with their addresses:-

CREATE PROCEDURE SelectAllContactsWithAddresses
AS
BEGIN
SELECT Contact.ContactId, Contact.FirstName, Contact.LastName, Contact.ModifiedDate,
Address.AddressId, Address.ContactId AS AddressContactId, Address.CountryRegion,
Address.StateProvince, Address.ModifiedDate
FROM Contact
INNER JOIN Address
ON Contact.ContactId = Address.ContactId
END

Right click on the model and add the new stored procedure to the model :-
image image

Right click on the model and add the stored ptocedure as a “Function Import”.

image

In the “Add Function Import” dialog, enter the function name and select the stored procedure name :-

 

 

In the dialog, If you click “Get Column Information”, It looks into the stored procedure you selected and shows the selected columns in the immediately below grid. Notice the “ModifiedDate1” column. Although, we didn’t return any column by that name in the stored procedure, the stored procedure returns an ambigous column name “ModifiedDate” which exists both  in Contact and Address tables.image

 

image

 

Make sure you click on “Create New Complex Type” to create a new complex type. The result set will be a complex type because It does not match up wo any of the entities in the model, rather it is made of columns from both Contact and Address tables.

 

Now the context has a method named SelectAllContactsWithAddresses() and when you execute a code like this,

foreach (var item in context.SelectAllContactsWithAddresses())
{
Console.WriteLine(item.FirstName.Trim() + ” ” + item.LastName.Trim());
Console.WriteLine(item.AddressContactId + ” ” + item.CountryRegion);
}

you get the following error :-

System.Data.EntityCommandExecutionException: The data reader is incompatible with the specified ‘AddressBookModel.SelectAllContactsWithAddresses_Result2’. A member of the type, ‘ModifiedDate1’, does not have a corresponding column in the data reader with the same name.
   at System.Data.Query.InternalTrees.ColumnMapFactory.GetMemberOrdinalFromReader(DbDataReader storeDataReader, EdmMember member, EdmType currentType, Dictionary`2 renameList)   at System.Data.Query.InternalTrees.ColumnMapFactory.GetColumnMapsForType(DbDataReader storeDataReader, EdmType edmType, Dictionary`2 renameList)   at System.Data.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndType(DbDataReader storeDataReader, EdmType edmType, EntitySet entitySet, Dictionary`2 renameList)   at System.Data.Query.InternalTrees.ColumnMapFactory.CreateFunctionImportStruc
turalTypeColumnMap(DbDataReader storeDataReader, FunctionImportMapping mapping,EntitySet entitySet, StructuralType baseStructuralType)
at System.Data.EntityClient.EntityCommandDefinition.FunctionColumnMapGenerator.System.Data.EntityClient.EntityCommandDefinition.IColumnMapGenerator.CreateCol
umnMap(DbDataReader reader)   at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption)   at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functio
nName, MergeOption mergeOption, ObjectParameter[] parameters)   at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functio
nName, ObjectParameter[] parameters)   at AddressBook.AddressBookEntities.SelectAllContactsWithAddresses() in E:\Ashish\Research\VS Solutions\EntityFramework\PresentationDemo\AddressBookConsole\AddressBook\AddressBookModel.Designer.cs:line 148
at AddressBookConsole.Program.ShowContacts() in E:\Ashish\Research\VS Solutions\EntityFramework\PresentationDemo\AddressBookConsole\AddressBookConsole\Progra
m.cs:line 50   at AddressBookConsole.Program.Main(String[] args) in E:\Ashish\Research\VS So
lutions\EntityFramework\PresentationDemo\AddressBookConsole\AddressBookConsole\Program.cs:line 15

 

To put simply, the columns from the stored procedure must match the properties in the entities in the model. So we modify our stored procedure in the following way:-

ALTER PROCEDURE SelectAllContactsWithAddresses
AS
BEGIN
SELECT Contact.ContactId, Contact.FirstName, Contact.LastName,
Contact.ModifiedDate AS ContactModifiedDate,
Address.AddressId, Address.ContactId AS AddressContactId, Address.CountryRegion,
Address.StateProvince, Address.ModifiedDate AS AddressModifiedDate
    FROM Contact
INNER JOIN Address
ON Contact.ContactId = Address.ContactId
END

Now we need to “Refresh” the model with the changed stored procedure:-

image

We make sure the function import shows correct columns now. by clicking on the “Get column information” button inthe properties page.
image

We nee to change the properties in the model as well to make it same as the changed columns returned:-
image

Now, we write code like this :-

foreach (var contactWithAddresses in context.SelectAllContactsWithAddresses())
{
Console.WriteLine(contactWithAddresses .FirstName.Trim() + ” ” + contactWithAddresses .LastName.Trim());
Console.WriteLine(contactWithAddresses .ContactModifiedDate + ” ” + contactWithAddresses .AddressModifiedDate);
}

and we get the correct results without any problem:-

image

Advertisements
  1. #1 by Joseph on October 27, 2011 - 10:47 am

    THANKKKSSSSSSSSS

  2. #2 by Mehmood on July 23, 2012 - 5:58 pm

    thanks A lot. words like charm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands

%d bloggers like this: