ADO.Net Entity Designer in VS 2010 - Stored Procedure Return Type Shape Sensing

ADO.Net Entity Designer in VS 2010 - Stored Procedure Return Type Shape Sensing

  • Comments 12

The Entity Framework in .Net Framework 4.0 contains a bunch of new functionality mentioned here and here.

This blog post will talk about some of the details of a lesser known feature in the the Entity Designer in VS 2010 which enhances user productivity by enabling discovery of the return type of stored procedures from within the Function Import dialog box. Further it allows the Return Type to be defined as a Complex Type which can be created or updated from within the same dialog box when the stored procedure changes without requiring an Update Model from Database operation to be run.

The corresponding Help documentation for the related features are as follows:

Let’s look at the feature with an example and some screenshots.

Let’s start by creating a new ADO.Net Entity Data Model file (an EDMX file) which connects to the Northwind database and pulls in a few of the stored procedures from the database as shown below.

clip_image001

Once the wizard completes, you will see that the sproc entries are present in the Store section in the model browser (which corresponds to the SSDL section of the file) as shown below. You can also see that buried under the EntityContainer node there is a “Function Imports” node which is currently empty.

clip_image002

Next, let’s go ahead and create a new Function Import, either via the Add context menu on the canvas (as shown below) or the Function Imports node in the Model Browser.

clip_image003

This will bring up the Add Function Import dialog box in which you can select the Stored Procedure for which you want to create a Function Import. Notice that when you select a sproc the “Get Column Information” button is activated.

clip_image004

Clicking on that button results in a call to get the shape of the stored procedure and this is then displayed in the grid below including information on what is the EDM Type and Db Type for each column, along with additional information such as Nullable, MaxLength etc.

clip_image005

Clicking on the “Create New Complex Type” button does what you would expect by creating a new ComplexType with the shape matching the sproc selected. You can also selected an existing Entity, a Scalar or None instead of the complex type option. Go ahead and save this function import at this point.

clip_image006

Now let’s go ahead and update the selected sproc to return an additional column so that the shape is different from what was originally sensed.

clip_image007

Now when you open the existing Function Import, you can do another “Get Column Information” to sense the new shape off the stored procedure which has been updated.

clip_image008

Which brings in the new column that the sproc is now returning. Viola!

clip_image009

But wait, there is more! At this point, you have the option to either create a new complex type matching this new shape, or conveniently update the existing complex type which makes managing changes to sprocs so much easier on the Entity Framework side. You don’t need to do an Update Model from Database operation and refresh the sprocs and then manually update the corresponding complex types to the new shapes returned etc. It can all be done conveniently from within the Function Import dialog box.

clip_image010

If you hit any issues while working with the Entity Framework Runtime or the Designer, be sure to report it on the ADO.Net Entity Framework Forums on MSDN and someone is sure to help you out. Hope you enjoy working with the Entity Framework in Visual Studio 2010.

Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post
  • In my adventures, it seems that this functionality does nothing to handle non-trivial cases. I.e., where the sproc needs parameters to function properly. Maybe it's actually there, but I certainly haven't found it.

    I've ended up implementing a workaround where I actually changed the procedures to account for this -- in effect, it kind of detects that it's the designer making the call so that it can return a meaningful result set. This allows the tooling to create the correct Complex Type.

    It would be GREAT if a dialog popped up allowing me to specify params to feed into the sproc instead. Or does this feature already exist and I somehow missed it?? I'm running VS 2010 RTM and my EDMX is in a .NET 4.0 class library, and I certainly don't see this kind of thing anywhere.

    You guys have been creating such great functionality that sometimes this kind of thing is even *more* frustrating than if these features didn't exist at all...

  • Has there been any updates to how the result set from an import function is translated into a store expression?  I love the way you can map the stored proc's return type to an entity, however when wanting to join the results with another entity, even when casting to an IQueryable, there is an exception thrown that "LINQ to Entities does not recognize [FUNCTION] and this method cannot be translated into a store expression" and the joins all fail.  

    I read an article that let me know that I could cast the non-function import EntitySet as an IEnumerable and then the join works, however I've read that the client side execution of this method can be a performance hit since it supposedly pulls all the records from the entity set before applying any filters at all.

    Has there been any improvement to this with the 4.0 release?

  • @pj - That is true – we rely on SQL Server to tell us what the “shape” of the stored procedure is. If the shape depends on a input parameter then this doesn't work very well. In more complex cases, you can have the system create a starting point for you, and then use the tools to modify the resulting type to match your more complex needs.

    @Jon - Note from our PM on your question (since I frankly didn't get it completely - I'm new to the EF world!) - [Noam:] That’s right – this is by design: SQL server does not allow you to compose queries over stored procedure: That is exactly what functions in SQL server are for. You can compose LINQ queries over SQL functions, as described here:

    http://msdn.microsoft.com/en-us/library/dd456847.aspx

  • Is it possible to perform a compiled query using a stored proc?  I am getting the same error Jon mentioned and haven't found any documentation on combining the two. If not, can a compiled query be written for an sql function?

  • Hi Tim,

    Here is a detailed response from Diego Vega who is a PM on the EF Runtime team:

    "No, compiled queries and stored procedures are two different mechanisms you can use to access data using Entity Framework but you cannot combine them together in this way.

    What compiled queries provide is a way to efficiently execute the same LINQ to Entities query multiple times and with different instances of the ObjectContext without having to redo the translation of LINQ to the native SQL of the database server every time. But compiled queries are ultimately translated into SELECT statements in SQL.

    On the other side, Entity Framework provides the ability to map stored procedures to INSERT, UPDATE and DELETE operations on entities as well as to methods that return objects based on data from the database. But stored procedures are not “composable”, meaning that it is not possible to create an Entity Framework query that will take the output of a stored procedure and will perform additional operations on the server over those results. Therefore it is not possible to write LINQ to Entities queries that use stored procedures, and without LINQ to Entities queries it is not possible to obtain compiled queries.

    In general, when you get a runtime exception from a LINQ to Entities query that says that a method is not recognized, it means that you are using a method that may be ok in C# or VB but for which we don’t know what the corresponding translation would be when executed on the server. For compiled queries in particular, you are going to get the exception the first time you try to execute the query for the first time, and no when you call CompiledQuery.Compile(). This is a consequence of how CompiledQuery actually works.

    My recommendation would be that if you need to apply any additional operation over the data returned by a stored procedure, you write another stored procedure that returns the data exactly how you want it and then you invoke that new stored procedure directly using a function import in EF.

    We would like to add support for Table Valued Functions in a future version of EF. TVFs are for all intents and purposes like stored procedures that return what looks like a table, but are different from stored procedures in that it is possible to apply query operations on the server over their results. When we add support for TVFs, it will be possible to compose LINQ to Entities queries that invoke them and also to compile those queries using CompiledQuery.

    I hope this helps,

    Diego

    "

  • I'm new to EF4, but I would like to use EF4 to execute stored procedures against SQL Server 2005. I'm able to map sps using ADO.NET Entity Designer, but I'm unable to retrieve 'return value' that sps can return. For example, in of the sps, the code checks for some logic, and if it's incorrect, it just returns an error code. If it's correct, it then returns data from a table. Is there a way to map return value in EF4 using ADO.NET designer? Or maybe I'm just missing something.

    Thanks in advance!

  • Hi Eric,

    The designer will not lookup the return value of the sproc but instead just the shape of the sproc (what all columns does it return etc.). In the example you gave, you should be able to map that sproc to an Entity or ComplexType of the same shape but not have conditional logic in it based on an input value or something like that. You could also look into using Output parameters to indicate the error code etc.

    Nihit

  • I'd like to use EF4 with a Silverlight grid to bind to the results of a stored proc, but this stored proc returns some fixed columns and N number of dynamically created columns following the fixed columns which are based on the parameters passed in. Will this work using the Complex Type selection and the Function Import option? If not, how should I go about handling this?

  • H Ed,

    Here is the response I got to your query from the Srikanth Mandadi who is the EF dev lead:

    "Dynamic result sets are not supported. You can do dynamic queries using ESql but still the result types have to be static."

    Hope that helps.

    Thanks,

    Nihit

  • Another thing, is to be able to map sproc to an Entity which hasn't mapped to table/view. Currently we have to map each entity to a dummy view. We could use the Complex type, but we want to be able to view the Entities diagram, to see the relationship between them. So we understand that currently it's not possible in VS2010... is it will be possible in the future?

  • I have over a hundred stored procedures. Thus I have over a hundred complex type. I would love to click something that says update all complex types instead of having to click the button update for each one. I tried to even right a macro that would just click the update button, but the edit function import window doesn't appear to be accessible via macros. Any ideas?

  • @DTig - I would suggest posting to the EF forums since I am not working in this area currently.

Page 1 of 1 (12 items)