Here's a simple example of how to map a stored procedure in an Entity Framework Data Model. I'm using the Northwind Customers table, and a simple sproc to fetch a list of Customers (I don't think the Entity Framework yet supports automatic code generation for sprocs that return scalar values).
Code:
CREATE PROCEDURE GetCustomerList
AS
SELECT
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
FROM
Customers
1. Add a new Entity Data Model as you normally would using the Entity Data Model Wizard. Target a copy of the Northwind database (including the stored procedure listed above). When prompted just select the Customers table and the GetCustomerList sproc from the respective tree nodes.
2. I now have a Northwind.edmx file that displays a Customers entity in the designer (I singularize generated entity names, so mine is now called Customer).
3. With the designer displayed, find the Model Browser tab. The tab displays 2 nodes: NorthwindModel and NorthwindModel.Store. NorthwindModel is giving you a graphical representation of the CSDL metadata file in your Entity Framework connection string. The CSDL describes the entity model. At present your entity model doesn't know about the GetCustomerList sproc. It hasn't been mapped yet.
NorthwindModel.Store is giving you a graphical representation of the SSDL metadata file in your Entity Framework connection string. The SSDL describes the database model.
(The MSL metadata file in your Entity Framework connection string describes the mapping between the CSDL and the SSDL).
4. So you need to map the GetCustomerList sproc in the data store to your entity model. The Entity Framework maps sprocs as Functions that you can invoke through an instance of your entity model's ObjectContext object.
In the Model Browser, open the NorthwindModel.Store node, then the Stored Procedure node. Right click on the GetCustomerList sproc and select Create Function Import. For Return type, select Customer.
5. If you now expand the EntityContainer: NorthwindEntities node under the NorthwindModel node, you will see that a Function Import has been added to your Entity Model. The following code has been added to your Entity Data Model designer.cs file:
Code:
public global::System.Data.Objects.ObjectResult<Customer> GetCustomerList()
{
return base.ExecuteFunction<Customer>("GetCustomerList");
}
6. Your stored procedure is now mapped to your Entity Model, and you can use it as follows:
Code:
using (NorthwindEntities db = new NorthwindEntities())
{
foreach (Customer c in db.GetCustomerList())
{
Console.WriteLine(c.CompanyName);
}
}
Of course, this whole process could have been accomplished more simply using LINQ to Entities. The following yields the same result:
Code:
using (NorthwindEntities db = new NorthwindEntities())
{
Customer[] customers =
(from c in db.CustomerSet
orderby c.CompanyName
select c).ToArray();
foreach (Customer c in customers)
{
Console.WriteLine(c.CompanyName);
}
}
It seems to make sense to me to limit stored procedure use in Entity Models to insert, update, and delete operations, and simply use LINQ To Entities for select operations.
Anyway, thanks for letting me review this stuff.
Bob
EDMX
on their post!