When you build an EDM from an existing database in Entity Framework, you can get Views & Stored Procedures in addition to tables. You’ll find that if you select Stored procedures, you will also get your functions.
The stored procedures are only defined in the storage layer. The return type is not determined therefore there is no Entity created or the mapping that would be between the two.
The Entity Framework documentaiton describes how to build Insert, Update and Deleted sprocs but not how to build sprocs that return data.
I got some help on the forums which showed building a stored proc in the EDM from scratch, but I wanted to take one that had been put into the model and complete it. I’ve written a short article that walks you through step by step that will go up on www.DevSource.com shortly, but in the meantime, I wanted to show how I finished up the CustOrderHist procedure. The article goes into more detail and explains the what’s and why’s. I will put a link to it in this post when it goes online.
What you will see created by the wizard is this function in the storage model section of the EDMX.
<Function Name=”CustOrderHist” Aggregate=”false” BuiltIn=”false” NiladicFunction=”false” IsComposable=”false” ParameterTypeSemantics=”AllowImplicitConversion” Schema=”dbo”>
<Parameter Name=”CustomerID” Type=”nchar” Mode=”in” />
</Function>
You’ll need to create an entity, entityset and FunctionImport in the conceptual model. In the storage model, you’ll need a matching entity and entity set and then you need to map the FunctionImport of the Conceptual model to teh Function in the storage as well as map the entitysets.
Here are what the rest of the code chunks look like.
I created the Entity using the designer and that automatically created the EntitySet.
<EntityType Name=”CustOrderHistResult” >
<Key >
<PropertyRef Name=”ProductName” />
</Key>
<Property Name=”ProductName” Type=”String” Nullable=”false” MaxLength=”40″ />
<Property Name=”Total” Type=”Int32″ Nullable=”false”/>
</EntityType>
<EntitySet Name=”CustOrderHistResultSet” EntityType=”NWModel.CustOrderHistResult” />
The FunctionImport in the Conceptual layer which has pointers to the entity and entity set.
<FunctionImport Name=”CustOrderHist” EntitySet=”CustOrderHistResultSet”ReturnType=”Collection(Self.CustOrderHistResult >
<Parameter Name=”CustomerID” Type=”String” Mode=”in” />
</FunctionImport>
In the storage layer, add the matching entity and entity set; they look like this. Remember to put them in the correct sections!
<EntityType Name=”CustOrderHistResult” >
<Key >
<PropertyRef Name=”ProductName” />
</Key>
<Property Name=”ProductName” Type=”nvarchar” Nullable=”false” MaxLength=”40″ />
<Property Name=”Total” Type=”int” Nullable=”false”/>
</EntityType>
<EntitySet Name=”CustOrderHistResultSet” EntityType=”NWModel.Store.CustOrderHistResult”/>
And lastly the mappings:
The first one I did manually.
<FunctionImportMapping FunctionImportName=”CustOrderHist” FunctionName=”NWModel.Store.CustOrderHist”/>
The second I actually created by using the designer. Here is the xml that was generated
<EntitySetMapping Name=”CustOrderHistResultSet” ><EntityTypeMapping TypeName=”IsTypeOf(NWModel.CustOrderHistResult)” >
<MappingFragment StoreEntitySet=”CustOrderHistResultSet” >
<ScalarProperty Name=”ProductName” ColumnName=”ProductName” />
<ScalarProperty Name=”Total” ColumnName=”Total” />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
With all of this in place, I can now use or query the stored procs as I would any of the other entities in my model
Dim query = From CustOrders In nw.CustOrderHist(“ROMEY”) _
Where CustOrders.Total > 10 Select CustOrders Order By CustOrders.ProductName
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!
Really useful, thanks !