Ashish Gupta

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

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 :-

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

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

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:-

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

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

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:-