RIA Services - call a stored procedure

Question

I am using RIA Services with Silverlight and Entity Framework. I want to call a stored procedure and map the results to a datagrid. What is the best way to do this? The output of the stored procedure doesn't map to any table design.

I found the following article -

http://blogs.msdn.com/b/tom/archive/2009/05/07/silverlight-ria-calling-stored-procedures-that-don-t-return-tables.aspx

However, it doesn't work for me - I get an error saying that the result complex set does not have a primary key defined. I can't see how to define this in code.

Anyway, I'm open to any and all solutions.

Craig Schwarze Craig Schwarze · Accepted Answer · 2011-02-07T22:26:22

I found the following excellent step-by-step guide at this site -

http://betaforums.silverlight.net/forums/p/218383/521023.aspx

1) Add a ADO Entity Data Model to your Web project; Select generate from database option; Select your Database instance to connect to.

2) Choose your DB object to import to the Model. You can expand Table node to select any table you want to import to the Model. Expand Stored Procedure node to select your Stored Precedure as well. Click Finish to finish the import.

3) Right click the DB model designer to select Add/Function Import. Give the function a name (same name as your SP would be fine) and select the Stored Procedure you want to map. If your SP returns only one field, you can map the return result to a collection of scalars. If your SP returns more than one field, you could either map the return result to a collection or Entity (if all the field are from a single table) or a collection of Complex types.

If you want to use Complex type, you can click Get Column button to get all the columns for your SP. Then click Create new Complex type button to create this Complex type.

4) Add a Domain Service class to the Web project. Select the DataModel you just created as the DataContext of this Service. Select all the entitis you want expose to the client. The service functions should be generated for those entities.

5) You may not see the Complex type in the Entity list. You have to manully add a query function for your SP in your Service: Say your SP is called SP1, the Complex type you generated is called SP1_Result.

Add the following code in your Domain Service class:

public IQueryable<SP1_Result> SP1()
    {
        return this.ObjectContext.SP1().AsQueryable();            
    }

Now you can compile your project. You might get an error like this: "SP1_Result does not have a Key" (if you not on RIA service SP1 beta). If you do, you need to do the following in the service metadata file:

Added a SP1_Result metadata class and tagged the Key field:

[MetadataTypeAttribute(typeof(SP1_Result.SP1_ResultMetadata))]
public partial class SP1_Result
{
    internal sealed class SP1_ResultMetadata
    {
        [Key]
        public int MyId;  // Change MyId to the ID field of your SP_Result
    }
}

6) Compile your solution. Now you have SP1_Result exposed to the client. Check the generated file, you should see SP1_Result is generated as an Entity class. Now you can access DomainContext.SP1Query and DomainContext.SP1_Results in your Silverlight code. You can treat it as you do with any other Entity(the entity mapped to a table) class.

RIA Services - call a stored procedure

2 Answers