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

RIA services and combobox lookups

update: Here is another version using Async ctp, that will let us wait untill all the data for the related comboboxes is loaded.

Here is a quick sample on how to setup comboboxes for lookups whether in datagrid or dataform
I am using Chinook database which can be downloaded from here. I am using Employee and Customer tables only.
This sample uses for employee look up to populate the Support Rep ID column in the customer table

In Metadata class for the Customer, we add an attribute [Include] for the employee property
 public partial class Customer
  {
  
    internal sealed class CustomerMetadata
    {

      // Metadata classes are not meant to be instantiated.
      private CustomerMetadata()
      {
      }

     …

      [Include]
      public Employee Employee { get; set; }

     …
    }
  }

and in the DomainService, we include the Employee like below to fetech the related employee record for the customer

 [EnableClientAccess()]
  public class ChinookDomainService : LinqToEntitiesDomainService<ChinookEntities>
  {

    …

    public IQueryable<Customer> GetCustomers()
    {
      return this.ObjectContext.Customers.Include(“Employee”);
    }

    …
}

In XAML, we Add a DomainDataSource in the resources
<UserControl.Resources>
     <my1:DomainDataSource AutoLoad=”False” x:Name=”salesRepList” QueryName=”GetEmployeesQuery” LoadedData=”salesRepList_LoadedData”/>     
    </UserControl.Resources>

The DataGrid is defined like this

 <my:DataGrid x:Name=”datagrid1″ Grid.Row=”0″  AutoGenerateColumns=”False”>
            <my:DataGrid.Columns>
             <my:DataGridTextColumn Header=”FirstName”  Binding=”{Binding FirstName}”/>
             <my:DataGridTextColumn Header=”LastName”  Binding=”{Binding LastName}”/>
                <my:DataGridTextColumn Header=”City”  Binding=”{Binding City}”/>
                <my:DataGridTextColumn Header=”Country”  Binding=”{Binding Country}”/>
             <my:DataGridTextColumn Header=”Employee”  Binding=”{Binding Employee.FirstName}”/>
              
             <my:DataGridTemplateColumn Header=”Support Rep”>
              <my:DataGridTemplateColumn.CellTemplate>
               <DataTemplate>
                <TextBlock Text=”{Binding Employee.FirstName}”/>
               </DataTemplate>
              </my:DataGridTemplateColumn.CellTemplate>
              <my:DataGridTemplateColumn.CellEditingTemplate>
               <DataTemplate>
                <ComboBox ItemsSource=”{Binding Data, Source={StaticResource salesRepList}}”  SelectedValuePath=”EmployeeId”
                 DisplayMemberPath=”FirstName” SelectedValue=”{Binding SupportRepId, Mode=TwoWay}” />
               </DataTemplate>
              </my:DataGridTemplateColumn.CellEditingTemplate>
                   
             </my:DataGridTemplateColumn>
             <my:DataGridTemplateColumn Header=”Support Rep”>
              <my:DataGridTemplateColumn.CellTemplate>
               <DataTemplate>
                <TextBlock Text=”{Binding Employee.FirstName}”/>
               </DataTemplate>
              </my:DataGridTemplateColumn.CellTemplate>
              <my:DataGridTemplateColumn.CellEditingTemplate>
               <DataTemplate>
                <ComboBox ItemsSource=”{Binding Data, Source={StaticResource salesRepList}}”  SelectedItem=”{Binding Employee, Mode=TwoWay}”
                 DisplayMemberPath=”FirstName”  />

               </DataTemplate>
              </my:DataGridTemplateColumn.CellEditingTemplate>

             </my:DataGridTemplateColumn>
            </my:DataGrid.Columns>
        </my:DataGrid>

there are 2 Template columns defined. 1st column shows a way to specify the Id value (Foreign  Key) and the 2nd shows how to set the selectedItem

The codebehind looks like this

public partial class MainPage : UserControl
  {
    ChinookDomainContext ctx = new ChinookDomainContext();   
    public MainPage()
    {
      InitializeComponent();
      DomainDataSource ds = this.Resources[“salesRepList”] as DomainDataSource;
      ds.DomainContext = ctx;
      ds.Load();
    }

    private void salesRepList_LoadedData(object sender, LoadedDataEventArgs e)
    {
      ctx.Load(ctx.GetCustomersQuery(), (lo) => {datagrid1.ItemsSource = lo.Entities;}, false);
    }
  
    private void Button_Click(object sender, RoutedEventArgs e)
    {
      ctx.SubmitChanges();
    }  
  }

you can download the sample from here