Using stored procedures (Linq-to-SQL, not EF) in WCF RIA - Silverlight 4

Question

For the love of heaven and earth I really wish someone could help me out with this issue. It seems everyone has something to say about EF but nothing about Linq-to-SQL.

I am trying to grab some data from my table via a stored procedure, believe me, that's all.

1. I added the Linq-to-SQL model (LAMP.dbml)
2. added the stored procedure (getAffectedParcel) from the server explorer. getAffectedParcel takes 2 strings as parameters
3. Build the application.
4. Added a domain service class (LAMPService)
5. Selected the (LAMPDataContext) as the data context class (normally I would tick generate metadata, but since I am not working with tables it's not enabled for ticking)

6. Added the following function to the LAMPService.cs:

   public IEnumerable < getAffectedParcelResult > GetTheAffectedParcels(String v, String vf)
   {
       return this.DataContext.getAffectedParcel(v, vf).AsEnumerable();
   }
   

7. Added the following code to a Silverlight page in an attempt to consume the stored procedure:

   LAMPContext db = new LAMPContext();
   
   try
   {
       var q = db.GetTheAffectedParcels("18606004005", "").Value;
   
       foreach (getAffectedParcelResult GAP in q)
       {
          MessageBox.Show(GAP.Owner);
       }
   }
   catch (Exception ex)
   {
       MessageBox.Show (ex.Message.ToString());
   }
   

8. Build and run application. An error occurs stating:

Object reference not set to an instance of an object.

I have tried ~1000,000 ways to see if this thing would work, but to no avail. Please don't tell me to use Entity Framework, I want to use Linq-to-SQL. Can someone (anyone) help me out here.

//houdini

Answer 1

Much thanks to Chui and Garry who practically kicked me in the right direction :) [thanks guys...ouch]

This is the procedure I finally undertook: -After adding the data model(LINQ2SQL) and the domain service, I created a partial class [as suggested by Chui] and included the following metadata info therein:

[MetadataTypeAttribute(typeof(getAffectedParcelResult.getAffectedParcelResultMetadata))]

public partial class getAffectedParcelResult
{
    internal sealed class getAffectedParcelResultMetadata
    {
        [Key]
        public string PENumber { get; set; }
    }
}

Then, Adjusted the Domain Service to include the following:

[Query]

    public IQueryable<getAffectedParcelResult> GetTheAffectedParcels(string v, string vf)
    {
        // IEnumerable<getAffectedParcelResult> ap = this.DataContext.getAffectedParcel(v, vf);

        return this.DataContext.getAffectedParcel(v, vf).AsQueryable();
    }

Then Build the app, afterwhich the getAffectedParcelResult store procedure appeared in the Data Sources panel. I wanted to access this via code however. Therefore, I accessed it in silverlight [.xaml page] via the following:

LAMPContext db = new LAMPContext();

            var q = db.GetTheAffectedParcelsQuery("18606004005", "");
            db.Load(q, (op) =>
                {
                    if (op.HasError)
                    {
                        MessageBox.Show(op.Error.Message);
                        op.MarkErrorAsHandled();
                    }
                    else
                    {
                        foreach (getAffectedParcelResult gap in op.Entities)
                        {
                            ownerTextBlock.Text = gap.Owner.ToString();
                        }
                    }

                },false);

This worked nicely. The thing is, my stored procedure returns a complex type so to speak. As of such, it was not possible to map it to any particular entity. Oh and by the way this article helped out as well: http://onmick.com/Home/tabid/154/articleType/ArticleView/articleId/2/Pulling-Data-from-Stored-Procedures-in-WCF-RIA-Services-for-Silverlight.aspx

Answer 2

Firstly, it seems like your DomainService code is written for Invoke() rather than Query(). You should use Query as it enables you to update data back to the server.

Solution: you should add a [Query] attribute to GetTheAffectedParcels on the domain service.

[Query]
public IQueryable<Parcel> 
   GetTheAffectedParcels(string ParcelNumber, string LotNumber)
{
   // etc.
}

Secondly, RIA Services needs to know which is the primary key on the Parcel class.

Solution: Apply a MetadataType attribute to the Parcel class, which allows you to add metadata to the Parcel class indirectly, since it is generated by Linq2Sql and you couldn't add annotations directly to the ParcelId - it'd get wiped away.

[MetadataType(typeof(ParcelMetadata)]
public partial class Parcel
{
}

public class ParcelMetadata
{
    [System.ComponentModel.DataAnnotations.Key]
    public int ParcelId {get; set; }
}

Thirdly, modify your client like this. Instead try this on the Silverlight client:

LAMPContext db = new LAMPContext();

try
{
    var q = db.GetTheAffectedParcelsQuery("18606004005", "");

    db.Load(q, (op) =>
    {
       if (op.HasError)
       {
            label1.Text = op.Error.Message;
            op.MarkErrorAsHandled();
       }
       else
       {
            foreach (var parcel in op.Entities)
            {
                // your code here
            }
       }
    }
}
catch (Exception ex)
{
    label1.Text = op.ex.Message;
}

Answer 3

Calling a stored procedure from the Silverlight client happens in the Async world. Let's consider an example from the AdventureWorks database...

Here's what the Domain Service method looks like. It is calling the EF on a stored procedure in the database called 'BillOfMaterials'.

public IQueryable<BillOfMaterial> GetBillOfMaterials()
{
    return this.ObjectContext.BillOfMaterials;
}

Back on the client side, here is the code for setting up the call...

public GetSp()
{
    InitializeComponent();
    DomainService1 ds1 = new DomainService1();
    var lo = ds1.Load(ds1.GetBillOfMaterialsQuery());
    lo.Completed += LoCompleted;
}

First, the Domain Service is created, and then it is used to load the results of the stored procedure. In this particular case, the result of this is an instance of 'LoadOperation'. These things are async, so the LoadOperation needs to have a callback for when it is finished. The callback code looks like this...

public ObservableCollection<BillOfMaterial> MyList { get; set; }
void LoCompleted(object sender, EventArgs e)
{
    LoadOperation lo = sender as LoadOperation;
    if(lo!=null)
    {
        MyList = new ObservableCollection<BillOfMaterial>();
        foreach(BillOfMaterial bi in lo.AllEntities)
        {
            MyList.Add(bi);
        }
        dataGrid1.ItemsSource = MyList;
    }
}

In this method, the 'sender' is dereferenced into the LoadOperation instance, and then all the goodies from the database can be accessed. In this trivial example, a list is built and passed to DataGrid as the ItemsSource. It's good for understanding, but you would probably do something else in practice.

That should solve your problem. :)

The best advice I can give on Silverlight and RIA is never do ANYTHING on your own until you have tried it in AdventureWorks. You will just waste your time and beat your head against the wall.