When you are building or updating a model from a database, included Stored Procedures find their way into the SSDL (Store Schema Description Layer) of the metadata but not into the conceptual model. In order to expose them in your model, you need to create something called a Function Import which imports that stored proc from your SSDL into your model.
In EF4, when you run the Function Import Wizard, you will see a new option: Complex.
Complex Types have been around since the beginning of Entity Framework however there was no designer support for them. Check this blog post (Complex Types in the EDM Designer in EF4 and a look at updating complex types in code) to see how the designer now supports Complex Types in VS2010.
The typical use of a complex type is as a property of an entity type. Here’s a Customer entity type which contains the complex type, Name. Name has 5 properties, FirstName, LastName, etc.
Yet the Function Import wizard is not aimed at mapping part of the customer property.
A complex type is a class that can be instantiated. Even if it’s not a property of another type.
What this all boils down to is that you can receive the results of a stored procedure, whatever its shape, in the form of a complex type.
In EF version 1, there is no way to do this. You can return a scalar or an entity but nothing else. If you wanted to use a stored proc that returned something else, you would either have to create an entity plus a “fake” SSDL store representation and a mapping; all of this work was manual in the XML and a big PIA. If this was a drag for one stored proc, imagine if you had a lot of stored procedures. This problem became a show stopper for many development teams. Another way around the problem was to use Views rather than stored procs. (Check this blog post about that pattern:Entity Framework and Read Stored Procedures - a new perspective)
Now we have an option – though I’m hoping to see this fleshed out some more in Beta 2. Please, oh please!
Here’s a stored proc that returns data with a random shape – not matching any of the entities in my model.
CREATE PROCEDURE dbo.GetNames AS SELECT CustomerID AS ID, RTRIM(LastName) + ', ' + FirstName AS Name FROM SalesLT.Customer
In the Model Browser, I can define a complex type, such as CustName below, that matches the schema of my stored procedure result
What’s really nice here compared to EFv1 is that I don’t need to create any other metadata to use this complex type.
Now I can map the stored procedure to that Complex Type using a Function Import.
As I showed in my recent post, Checking out one of the new stored procedure features in EF4, import functions that did not return entities in EFv1 were hard to work with. If they returned a Scalar you could only use EntityClient to access the function. Now with functions that return a Scalar or a Complex Type, the default code generator creates the function directly in the generated ObjectContext.
public ObjectResult<CustName> GetNames()
{
return base.ExecuteFunction<CustName>("GetNames");
}
That means I can very easily call this function now.
This code declares a List of my Complex Type, CustName, calls the new GetNames function and converts the resulting ObjectResult<CustName) to a List.
List<CustName> custs = context.GetNames().ToList();
This is a huge improvement, since I can now actually use existing stored procs that don’t line up with an entity. My hope (which I certainly raised with the EF Team is that if we have a lot of stored procs, we won’t have to do the manual steps of creating the complex type and the running the Function Import Wizard for each one.
