RIA Services and Storedprocedures

If we have a storedprocedure that we want to use to retrieve data and it returns an entity that is already in our model, it is pretty easy. Create a function import and indicate the type of object it returns and add a method in the domainservice to get the data

I am using Northwind database. I created a simple procedure to get the customers by city

create procedure GetCustomersByCity @city varchar(100) as

select * from Customers where City=@city

Created a Import(GetCustomersByCity) and added a method in the DomainService

 public IQueryable<Customer> GetCustomersByCity(string city)
      return this.ObjectContext.GetCustomersByCity(city).AsQueryable<Customer>();

and called like this
ctx.Load<Customer>(ctx.GetCustomersByCityQuery(“London”), (lo) => { dataGrid1.ItemsSource = lo.Entities; }, false);

it works fine

I tried to do the same using a existing procedure “CustOrderHist”

create PROCEDURE [dbo].[CustOrderHist] @CustomerID nchar(5)
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

this doesnt return data that maps to any entity in our model. when I created a function import, I let it create a complexobject(CustOrderHistoryResult).

Then I added the following method similar to the one above in the DomainService

 public IQueryable<CustOrderHistoryResult> GetCustOrderHistory(string customerId)
      return this.ObjectContext.CustOrderHistory(customerId).AsQueryable<CustOrderHistoryResult>();

When I built the App, it gave me the following error

The entity ‘CustOrderHistoryResult’ in DomainService ‘NorthwindDomainService’ does not have a key defined.
Entities exposed by DomainService operations must have at least one public property marked with the KeyAttribute.

one way I got this to work is to add the following class in the Metadata.cs file of the DomainService, marking “ProductName” as the Key

  public partial class CustOrderHistoryResult

    internal sealed class CustOrderHistoryResultMetadata
      private CustOrderHistoryResultMetadata()

      public string ProductName { get; set; }
      public int Total { get; set; }

after this I was able to call and get data from the procedure

ctx.Load<CustOrderHistoryResult>(ctx.GetCustOrderHistoryQuery(“ALFKI”), (lo) => { dataGrid2.ItemsSource = lo.Entities; }, false);

Not sure if this is the right way, but it seems to work

you can download the code here


18 thoughts on “RIA Services and Storedprocedures

  1. Thanks a lot Lee, you are the man, MS is doing very poor job, this is the only content i could find to do this.
    great post, finally i could get it working.

  2. I’d just like to also add my thanks, this has helped me solve something that I’ve been working on for a long long time….

  3. Exactly what I was looking for.
    How about creating view of what data we want as part of outcome of a storedprocedure and output of the storedprocedure as filtered view

  4. This was exactly what I need as well…thanks. For some reason however, I had to create the metadata file from scratch since Visual Studio 2010 doesn’t do it automatically, which I think if it is a bug, or because I had to do it myself for the first time, is a big pain because I use stored procedures A LOT!

  5. Hi
    I think Im almost there. I managed to create the complex method but in the code behind

    ctx.Load(ctx.GetCustomersByCityQuery(“London”), (lo) => { dataGrid1.ItemsSource = lo.Entities; }, false);

    ctx.Load(ctx.xxqueryxx(“London”), (lo) => { dataGrid1.ItemsSource = lo.Entities; }, false);

    My method is not seen and it says Im missing a reference??

    Any Ideas, is there a using or xmlns im missing??


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s