EF: SELECT with a SP
Imagine the following scenario. We have a SP which returns a SELECT and we want to integrate it on an EDM. For this, we will create an entity type. But we want to have to use the SP to have some instances of our entity type. This scenario isn't really covered by EF v1 and we must do some "strange things" to realize it.
For this post, we will use this SP:
CREATE PROCEDURE [dbo].[TOTO]
SELECT Id, 1 AS VALUE FROM Test
When we will get the SP on our EDM we will have this on the SSDL:
<Function Name="TOTO" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" />
Then we will create an entity type in the CSDL (which we can do with the designer):
<PropertyRef Name="Id" />
<Property Name="Id" Type="Int32" Nullable="false" />
<Property Name="Value" Type="Int32" Nullable="true" />
With the creation of the entity type, we will also have a new EntitySet:
<EntitySet Name="Totos" EntityType="TestModel.Toto" />
Then we can add the Function Import in the CSDL that will have a method on our ObjectContext. You just need to set the return type to Toto type.
Note that the entity type must have the same properties than the columns returned by the SP.
Then, as we don't want to have the EntitySet on our ObjectContext and as we can't delete it, we will set the property to private on our ObjectContext. Note that we can do this with the designer (go to Model Browser window, EntityContainer, EntitySets, Totos, go to the Properties window and change the Getter value).
But this isn't all. Indeed, we will have an error with this EDM because Toto entity type isn't mapped (even if we don't want to use it directly).
So, how to do?
I use a SSDL View. As the SQL command will never be excecuted, we can write anything (even if it isn't a correct query):
<EntitySet Name="Totos" EntityType="TestModel.Store.Toto" >
I can do anything here! :-)
<Property Name="Id" Type="int" Nullable="false"/>
<Property Name="Value" Type="int" />
Then we will map the entity type on the SSDL View and here we are.
Note that EF will cover better this scenario with the V2 as Daniel Simmons told me: "In v2 we’re allowing sprocs to return complex types which will be the easy way to return objects which don’t need to be mapped, etc."