Using Projections with Data Services in Visual Studio 2010

Using Projections with Data Services in Visual Studio 2010

  • Comments 5

Starting in Visual Studio 2008 Service Pack 1, you can create ADO.NET Data Services to easily expose data models via RESTful web services. So if you are building a remote CRUD data access layer then this is a technology that you're probably using or are looking into. I’ve written a lot about about data services in VS2008, my favorites are:

With Visual Studio 2010 and the .NET Framework 4 they’ve changed the name of this technology to WCF Data Services and have added some new features, one of which I want to talk about in this post called Query Projections. But first…

What’s a Query Projection?

If you’ve been writing LINQ queries you’re probably writing a lot of projections already. A projection can be used to limit the number of properties that are returned on a set of objects and/or to perform transformations on those properties. This is done using the Select clause. For example, say I have a list of customers that have 12 properties but I only want to return a couple of them in my result collection. I would write:

Dim result = From c In customerList Select c.CustomerID, c.ContactName

This creates a list of anonymous types that have only a CustomerID and ContactName property. We say that the query projected these properties from the Customer into the anonymous type. You can also project results into your own known types as well. For instance, say I have a class I’ve defined called MyCustomer with just the two properties defined:

Class MyCustomer
    Property CustomerID As Integer
    Property ContactName As String
End Class

I can write the query so that it will project the results into a collection of MyCustomer objects instead:

Dim result = From c In customerList
                Select New MyCustomer With
                       {.CustomerID = c.CustomerID,
                        .ContactName = c.ContactName}

Take a look here for some Visual Basic and C# query projection examples.

Query Projection Support in Data Services

When you write a LINQ query against a data service it is translated to an HTTP GET call. You can use LINQ to query a data service to perform restriction (Where), ordering (Order By) as well as other basic expressions, although not all LINQ syntax is supported. Unfortunately in Visual Studio 2008 SP1 you cannot use a projection (Select) on your queries to a data service, you’ll get a NotSupportedException. This means that all the properties are returned on the data entities you define in your model behind your data service. This can be a drag if you have entities with lots of properties or properties with heavy payloads like images or other binary data. All these must be sent down the wire regardless if you use them or not. Let me show you what I mean.

I have an Entity Framework model of the Northwind database that I’m exposing via a .NET 3.5 SP1 Data Service just like we built in this previous post. It has Categories and Products entities. The Category entity has a Picture property but I don’t need to use it in my client application. I’ve added the service reference to the client and you would think we could write the following:

Dim svc As New NorthwindService.NorthwindEntities(New Uri("http://.../NorthwindService.svc/"))

'Try to project just the properties we need:
Dim result = From c In svc.Categories Select c.CategoryID, c.CategoryName

For Each c In result 'NotSupportedException when query executes
    Console.WriteLine(c)
Next

However, we get a runtime error “Select is not supported”. Bummer! In order to project just the properties we need we have to execute the query and bring down the data locally and then project over that list. You can do it in one shot by adding a call to the ToList extension method like this:

Dim result = From c In svc.Categories.ToList Select c.CategoryID, c.CategoryName

Unfortunately if you look at the payload you see all the properties returned. So although the result collection is what we want, the way we got it was inefficient on the wire. In our example you can see the binary picture data is returned but never used:

image

What we really want is to see just the properties we requested in the payload. Good news is that this is now supported in Visual Studio 2010 and .NET Framework 4. There’s also an update you can install to get this support in .NET 3.5. Let’s explore this new feature by first creating a new WCF Data Service project in Visual Studio 2010.

Creating a WCF Data Service using Visual Studio 2010

I want to fist walk though how to create a WCF data service in Visual Studio 2010 since there are some subtle changes in the designers from VS2008. Create a new Project and select the Web node and then choose ASP.NET Empty Web Application. If you don’t see it, make sure your target is set to .NET Framework 4. This is a new handy project template to use especially if your creating data services.

image

Click OK and the project is created. It will only contain a web.config. Next add your data model like before. I’m going to use the Entity Framework so go to Project –> Add New Item, select the Data node and then choose ADO.NET Entity Data Model. Click Add and then you can create your data model, in my case I generated it from the Northwind database.

Next we need to add the WCF Data Service (formerly known as ADO.NET Data Service). Project –> Add New Item, select the Web node and then scroll down and choose WCF Data Service. This item template is renamed for both .NET 3.5 and 4.0 Framework targets so keep that in mind when trying to find it:

image

Now you can set up your entity access. For this example I’ll allow read access to all my entities in the model:

Public Class NorthwindService
    ' TODO: replace [[class name]] with your data class name
    Inherits DataService(Of NorthwindEntities)

    ' This method is called only once to initialize service-wide policies.
    Public Shared Sub InitializeService(ByVal config As DataServiceConfiguration)
        ' TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
        ' Examples:
        config.SetEntitySetAccessRule("*", EntitySetRights.AllRead)
        ' config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All)
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2
    End Sub

End Class

Finally, we’ll add a simple client console application to the solution to test the service. File –> Add –> New Project and then select Console application. Right click on the client console application and select "Add Service Reference" in the solution explorer. When the dialog opens click the Discover button and it should find the data service. Name the service reference then click OK. This will automatically generate the client-side proxy and the necessary entities to work with our data service.

Using Projections with WCF Data Services

Now that we have a .NET 4.0 WCF Data Service set up we can write a query in our client that specifies the projection:

Dim svc As New NorthwindEntities(New Uri("http://.../NorthwindService.svc/"))

'Projections now supported in WCF Data Services 
Dim result = From c In svc.Categories Select c.CategoryID, c.CategoryName

For Each c In result 'No errors 
    Console.WriteLine(c)
Next

What this translates to is a GET against our data service that specifies a select parameter that wasn’t supported before. If you open your favorite browser to the WCF Data Service we just created, you can specify a select clause in the query string:

http://…/NorthwindService.svc/Categories?$select=CategoryID,CategoryName

Now if you take a look at the payload we can see that only the CategoryID and CategoryName properties are returned from the service, conserving space on the wire:

image

For more information on using projections in data service queries please see the MSDN Library and the WCF Data Services Team Blog.

Enjoy!

Leave a Comment
  • Please add 8 and 6 and type the answer here:
  • Post
  • I tried to use Data Services, I had to stop using it after a while. Too many operations were not supported; it really only supported Where. It also could only throw a single type of fault, and any custom fault data needed to be placed inside the message string. It also had a bug where you needed to write peculiar lambda expressions to place primary keys inside your selection (where item => 1 == 1 and item.Id = CurrentItemId). Have any of these problems been addressed?

  • Hi Beth,

    I'm curious on how to position this projection capability in WCF-DS against the WCF RIA DTO concept.

    Tony W

  • Beth this is the news we'd been hoping for! WAY TO GO TEAM!!! THANK YOU!

  • Re my previous post (not working in C#)...I think I found out how - I was assuming it would be too similar to VB..

    It seems this is OK:

    var result = from c in oEntities.Customers select new { c.Id, c.Forename };

    wheraas I tried to use

    var result = from c in oEntities.Customers select c.Id, c.Forename;

    Great article though - thanks...I am struggling in my mind to allow 'code' to have access to my DB - my mindste is currently 'all interactions with my DB should be through Stored Procedures'...the thought of giving up access to tables to code sends shudders...

  • Beth,

    You totally rock!  I'm working on a demo for the Redmond WPF LOB Tour event 28-30 April and want to use oData.  

    This post got me up and running FAST.

    Cheers,

    Karl

Page 1 of 1 (5 items)