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)
AS
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

[MetadataTypeAttribute(typeof(CustOrderHistoryResult.CustOrderHistoryResultMetadata))]
  public partial class CustOrderHistoryResult
  {

    internal sealed class CustOrderHistoryResultMetadata
    {
      private CustOrderHistoryResultMetadata()
      {

      }
      [Key]
      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

Advertisements

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
    Pooran

  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??

    regards
    mike

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s