Problem using stored procedures returning a CLR type

Question

Hi,

 

I have a problem using stored procedures in the Entity Framework. I have a stored procedure that returns XML generated with the "For XML" syntax, so the procedure does not return an Entity, but a string. I'm new to this, so I hope someone can show me a step I am missing.

 

I have VS 2008 RTM, ADO.Net Entity Framework Version 1.0 (beta 3), and ADO.Net Entity Framework Tools Preview installed. I'm following the procedure outlined in Noam Ben-Ami's video, namely:

 

1) Add a new item of type ADO.Net Entity Data Model to the project, choose Generate from Database in the wizard, connect to my database, and select no entity tables, but only my stored procedure. My model designer workspace is empty.

2) I then select my stored procedure under the Model.Store in the Model Browser and select the "Create Function Import" menu item, give it a name and select "String" as the return type. Note I am selecting a CLR type, not an Entity. My imported function now appears under the Function Imports section under the EntityContainer.

3) I Build the project successfuly. When I look at the C# code behind file there are only entity constructors, no imported stored procedure. If I try to use it anyways, Intellisense is unaware of it and the compile complains about a missing extension method. There must be some step I'm missing here.

4) For the fun of it, if I add an Entity table and change the return type to this entity (rather than string), then the imported function does appear in the code behind file and I could compile (but of course it won't work right). In other words if the return type is an entity the stored procedure import works, but not if it is a CLR type.

 

Thanks for your help.

Answer 1

Hi Ken.

 

This is just how it works - and believe me I've harrassed poor Noam and the others about it!

 

You can easily map a READ stored proc to an existing Entity Type.

 

But if you want it to return something that doesn't match up with an entity type, you have to create the entity type and also create a "fake table" entity type (plus it's entity set) in the SSDL because every entity in CSDL must map to something in SSDL. Mapping to the function isn't enough.

 

Hopefully this will get easier with a later version of the tools. But for now, I have written a blog post with the steps for how to do this.

(watch for wrapping -- it's a long url!)

 

http://www.thedatafarm.com/blog/2007/12/19/
ImplementSELECTStoredProceduresThatReturnMiscellaneousDataInCTP2OfEFDesigner.aspx

 

hth

 

Julie

 

Answer 2

Unfortunately, due to time constraints, we do not do codegen for stored procedures that return primitive types such as String/Int32, etc.

 

For now, you can create your object context and get its connection. Then, use the connection's "CreateCommand" method, and create a command of type stored procedure whose name is the name of the function import. Then you can execute that command and get your string back. E.g. if your function is called "getXMLAsString" then in your DataContext's partial class you would write:

 

System.Data.Common.DbConnection connection = this.Connection;

System.Data.Common.DbCommand command = connection.CreateCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.CommandText = "getXMLAsString";

string result = command.ExecuteScalar() as string;

 

We will make this experience better in the future.

 

HTH,

  Noam

Answer 3

Oh duh, I was tihnking about types as in objects, not types as in types.

 

Answer 4

Thanks Noam,

 

My stored procedure takes an integer as an argument. When I went to msdn to try to figure out how to add a parameter to a System.Data.Common.DbCommand I got stuck. The documentation on this topic was sparse. As an alternative I did this to get it to work based on a Zlatko Michailov response found at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2504096&SiteID=1

 

using (EntityConnection conn = new EntityConnection("Name=MyConnectionName"))

{

conn.Open();

EntityCommand cmd = conn.CreateCommand();

cmd.CommandText = "MyEntityContainerName.MyImportedFunctionName";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("ProcId", MyIntParameterValue);

String s = (String) cmd.ExecuteScalar();

}

 

I'm guessing this is equivalent to your suggestion, but using a different namespace.

 

Thanks for your and Julie's help

 

Ken

Answer 5

Hey Ken,

 

 Yes, the two are precisely equivalent, I'm just casting to objects higher in the ADO.NET hierarchy: As you surmised, EntityCommand is a subtype of DbCommand.

 

 Glad this worked for you, sorry the documentation isn't quite there yet.

 

 Noam