Tuesday, March 31, 2009

Silverlight 3.0, Astoria, LINQ to SQL (1): Setting Up Astoria & LINQ to SQL

Silverlight 3.0 that just came out during MIX09 has a new control called DataForm. In essence, this control binds to an object/collection and from it you can view, edit, add, delete, page, etc. It looks like having a MS Access display your records through its Form.

Mike Taulty (one of the developers in the Silverlight team) made an excellent video showcasing the features and functionality of the DataForm control in Silverlight. You can watch the video here. There is a good blog post recap by Vikram Pendse.

I am not going to repeat what Mike has shown in the video. But what I am going to show through this post is how to build a DataForm that does CRUD (create, read, update, delete) to a LINQ to SQL data layer via ADO.NET Data Service. This first post in the series is to show how to build/prepare the solution, projects, and some ground work in LINQ to SQL and connecting it to ADO.NET Data Service.

What do you need to follow along? Several things:

For the database, I am going to use Northwind. If you do not have Northwind, you can get it from here.

OK, let's begin.
1. Create a Class Library project for our LINQ to SQL project. Why not just create the LINQ to SQL inside a web project for our data service? Because making it in its own project allow us to reuse it. For instance if I want to build a web Dynamic Data project connecting to the same DB, I can just reference this project in my Dynamic Data project. I called project as "Northwind.DalLinq". Add references to "System.Data.Services" and "System.Data.Services.Client".

2. Create a Silverlight project. I named mine as "Northwind.Silverlight".

When you do this, it will then ask you whether you want to host the Silverlight application inside a new website. This website will host not only our Silverlight application, but also the ADO.NET Data Service that talks to our LINQ to SQL. So, I selected "Web Application Project" and hit OK. You can choose "Web Site" or "MVC Web Project" if you want to - it does not really matter.

Add references to "System.ComponentModel", "System.ComponentModel.DataAnnotation", "System.Windows.Controls.Data", and "System.Windows.Controls.Data.DataForm" in the "Northwind.Silverlight" project. Add references to "Northwind.DalLinq" to the "Northwind.Silverlight.Web" project.

So at this point, your solution should look like this:

3. Create a LINQ to SQL class in "Northwind.DalLinq" project and create all table mappings into the dbml, as well as the stored proc. We are going to use them all in this post, but for simplicity, let's just add all. If you feel you know what you're doing, feel free to be more selective.

4. Create an ADO.NET Data Service item in the "Northwind.Silverlight.Web". Right click on the "Northwind.Silverlight.Web" project, "Add New Item", select "ADO.NET Data Service". I name mine as "NorthwindDataService".

Open up "NorthwindDataService.cs" (the codebehind for the ADO.NET Data Service item we just added) and enable it to bind to our Northwind data context. We do this by filling the generic for the DataService type in the class declaration line.
    public class NorthwindDataService : DataService<Northwind.DalLinq.NorthwindDBDataContext>
Then we also need to indicate which table mapping are going to be available to be accessed. We do this by specifying it in this line:
config.SetEntitySetAccessRule("*", EntitySetRights.All);
Right now that will allow ALL to be accessible. This is OK for our exercise, but in reality we want to allow with caution. So your code should look like this:
namespace Northwind.Silverlight.Web
public class NorthwindDataService : DataService<Northwind.DalLinq.NorthwindDBDataContext>
// This method is called only once to initialize service-wide policies.
public static void InitializeService(IDataServiceConfiguration config)
// TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
// Examples:
config.SetEntitySetAccessRule("*", EntitySetRights.All);
// config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);

5. Make sure that your web application is running on a static port, so our Service Reference will be able to find it later. To do this, right click on the "Northwind.Silverlight.Web" project and click "Properties", go to "Web" tab. In here, we want to make sure our port is static by setting it to "Specific Port" instead of the default "Auto-assign Port". You can use any port number that is available.

Alright, right now we already setup our basic LINQ to SQL mapping from our DB, setting up our Silverlight project along with the web application to host it, and lastly we created a new ADO.NET Data Service item in our web application project.

So let's try it! Set "Northwind.Silverlight.Web" project as the StartUp and set "NorthwindDataService.svc" as the Start Page and hit F5!

Most likely you will get this error:


6. Well, no need to panic ... you can set the error to be verbose and it will give you more information. The error is basically caused by ADO.NET confusion about what is the field that holds the primary key of the table mappings that we did in the LINQ to SQL. If you use Entity Framework, it will do it out of the box, but not LINQ to SQL apparently, so we need to decorate the classes with DataServiceKey attribute. Since the dbml generated by LINQ to SQL contains partial classes of the mappings, we can just simply add our own partial class that match with the generated part.

Add a class to our "Northwind.DalLinq" project and call it "NorthwindDB.Custom.cs". So your solution should look like this:

In that class, we declare our partial classes from our DB mappings decorated with the "DataServiceKey" attribute. Here are several examples on how to do them:
namespace Northwind.DalLinq
public partial class Category { }

public partial class Territory { }

[DataServiceKey(new string[] { "CustomerID", "CustomerTypeID" })]
public partial class CustomerCustomerDemo { }

// TODO: do the rest of the classes
Now, if we try hitting F5 again, we then should get a different result:

So now we have our ADO.NET Data Service ready and in my next post I will show you how to hook it up to the DataForm control to display our data.


Vikram Pendse said...

Very nice article !

Robert said...

Hi there & thanks for the article! I can't seem to get past the server error. I have added the Custom.cs class with the DataServiceKey entries as instructed but I keep getting the server error. I'm a bit of a Silverlight beginner so not sure if I'm missing something. I believe I followed your instructions to the letter. Any help you can offer would be much appreciated. Thanks, Rob

Joe said...

Robert, what error are you getting? Make sure ALL of your primary keys are tagged with DataServiceKey