May 2010

Volume 25 Number 05

Data Points - LINQ Projection Queries and Alternatives in WCF Services

By Julie Lerman | May 2010

Julie LermanThe presenter at my local .NET user group was writing a LINQ query during his session last month when I asked him, “How did we ever live without LINQ?” “I have no idea,” he replied.

It’s true. Since it was introduced in Visual Studio 2008, LINQ has made such a difference in how we code in the Microsoft .NET Framework. In combination with the many new language features that were introduced in Visual Basic and C#, it’s a consistent problem solver for querying in-memory objects and data sources.

One of LINQ’s abilities that is both a blessing and an occasional source of frustration is that it can project randomly shaped data into anonymous types. When you simply need to grab a special view of your data, without having to declare a new class for this throwaway type, anonymous types are a great solution. LINQ projections and anonymous types have certainly spoiled us. So why do I say they can also be a source of frustration?

If you have ever used a LINQ projection in a method that needs to return data to another method—or worse, used a LINQ projection in a Windows Communication Foundation (WCF) service operation—you may understand.

Because anonymous types are throwaway types, they have no declaration and are understood only within the method where they’re created. If you write a query that returns a list of anonymous types, there’s no way to define a method ar-gument to say “I’m going to return a list of … ” because there’s no way to express “… of anonymous types.”

Here’s a LINQ to Entities query with a simple projection:

var custQuery = from c in context.Customers
                 select new {c.CustomerID, Name=c.LastName.Trim() + 
                 ", " + c.FirstName};

At run time, the custQuery variable will actually be an ObjectQuery<<>f__AnonymousType0<int,string>>.

The var (and the alternate use of Visual Basic Dim) allows us to get away with not having (or needing) a way to express this non-type.

If you want to return the results of that query from a method, the only reasonable solution is to create a class to represent the type being returned. Doing this, however, renders the beauty of the anonymous type moot. Now you have to write more code, define classes and (possibly) new projects to house the new classes, ensure the various assemblies using these classes have access to them and so on.

Until recently, data services provided an additional conundrum. In order to project data, you had to create a custom operation in a service, execute your own query and then return some type of pre-defined class that could be understood by the client.

When you’re working with services, there are many scenarios where you want to work with a particular view of data without paying the price of moving larger types across the wire.

It turns out, there are more options besides creating an extra type in your domain to satisfy this temporary need.

New Projection Capability in WCF Data Services

The Data Services Update for the .NET Framework 3.5 SP1 introduces a handful of powerful features for WCF Data Services, which are also part of the .NET Framework 4. Among these features is the ability to use projections in queries against the data services. I highly recommend checking out the WCF Data Services team blog post on all that’s new in this update at blogs.msdn.com/astoriateam/archive/2010/01/27/data-services-update-for-net-3-5-sp1-available-for-download.aspx.

The $select operator has been added to the data services URI syntax. It allows for property and even navigation property projection.

Here’s a simple example of a projection that gets a few scalar properties for a customer along with the SalesOrderHeaders navigation property:

https://localhost /DataService.svc/Customers(609)
  $select=CustomerID,LastName,FirstName,SalesOrderHeaders&$expand=
  SalesOrderHeaders

The expand operator forces the results to include not just a link to those orders, but the data for each order as well.

Figure 1 shows the results of this query. The expanded SalesOrderHeaders (which contains only a single order) is highlighted in yellow while the customer information is highlighted in green.

Figure 1 Results of a Data Services Query Projection Requesting Three Customer Properties and the Customer’s SalesOrder-Headers
Figure 1 Results of a Data Services Query Projection Requesting Three Customer Properties and the Customer’s SalesOrderHeaders

The LINQ to REST feature in the .NET Framework and Silverlight client APIs for WCF Data Services has been updated to allow projections as well:

var projectedCust = (from c in context.Customers
                    where c.CustomerID==609
                    select new {c.CustomerID, c.LastName})
                    .FirstOrDefault();

ProjectedCust is now an anonymous type I can use in my client application.

It’s also possible to project into known entity types, and in some cases, the DataContext can keep track of changes made by the client and these changes can be persisted back through the service’s SaveChanges method. Be aware that any missing properties will get populated with their defaults (or null if they’re nullable) and be persisted to the database.

Enabling Projected Strong Types from an EDM

If you’re using an Entity Framework Entity Data Model (EDM), there’s a convenient way to avoid being stuck with anonymous types when you need to pass them out of the method in which they were created.

The EDM has a mapping called QueryView. I’ve pointed many clients to this in the past, prior to data services projection support. Not only does it solve the problem nicely for data services, but for custom WCF Services and RIA Services as well.

What is a QueryView? It’s a special type of mapping in the Entity Framework metadata. Typically, you map properties of an entity to database tables or view columns as they’re described in the store model—Storage Schema Definition Language (SSDL)—of metadata, as shown in Figure 2.

Figure 2 Mapping Table Columns Directly to Entity Properties
Figure 2 Mapping Table Columns Directly to Entity Properties

A QueryView, however, lets you create a view over those SSDL table columns rather than map directly to them. There are many reasons to use a QueryView. Some examples include: to expose your entities as read-only, to filter entities in a way that conditional mapping does not allow or to provide different views of the data tables from the database.

It’s the last of these purposes that I will focus on as an alternative to the anonymous types you frequently find yourself projecting in your application. One example would be a pick list. Why return an entire customer type for a drop-down that needs only an ID and the customer’s name?

Building a QueryView

Before creating a QueryView, you need to create an entity in the model that represents the shape of the view you’re aiming for—for example, the CustomerNameAndID entity.

But you can’t map this entity directly to the Customer table in SSDL. Mapping both the Customer entity and the CustomerNameAndID entity to the table’s CustomerID column would create a conflict.

Instead, just as you can create a view of a table in your database, you can create a view of the SSDL  Customer directly in the metadata. A QueryView is literally an Entity SQL expression over the SSDL. It’s part of the mapping specification language (MSL) metadata of the model. There is no designer support to create the QueryView, so you’ll need to type it directly in the XML.

Because you’ll be mapping to the store schema of the table, it’s a good idea to see what that looks like. Figure 3 lists the SSDL description of the Customer database table, which looks similar to the Customer entity in the conceptual model’s metadata, except for the use of provider data types.

Figure 3 The SSDL Description of the Database Customer Table

<EntityType Name="Customer">
  <Key>
    <PropertyRef Name="CustomerID" />
  </Key>
  <Property Name="CustomerID" Type="int" Nullable="false"
            StoreGeneratedPattern="Identity" />
  <Property Name="Title" Type="nvarchar" MaxLength="8" />
  <Property Name="FirstName" Type="nvarchar" Nullable="false" 
            MaxLength="50" />
  <Property Name="MiddleName" Type="nvarchar" MaxLength="50" />
  <Property Name="LastName" Type="nvarchar" Nullable="false" 
            MaxLength="50" />
  <Property Name="Suffix" Type="nvarchar" MaxLength="10" />
  <Property Name="CompanyName" Type="nvarchar" MaxLength="128" />
  <Property Name="SalesPerson" Type="nvarchar" MaxLength="256" />
  <Property Name="EmailAddress" Type="nvarchar" MaxLength="50" />
  <Property Name="Phone" Type="nvarchar" MaxLength="25" />
  <Property Name="ModifiedDate" Type="datetime" Nullable="false" />
  <Property Name="TimeStamp" Type="timestamp" Nullable="false"
            StoreGeneratedPattern="Computed" />
</EntityType>

Another important element for the QueryView will be the store schema’s namespace, ModelStoreContainer. Now you have the pieces necessary to construct the QueryView expression. Here’s a QueryView that projects the three required fields from the SSDL into the CustomerNameAndID entity that I created in the model:

SELECT VALUE AWModel.CustomerNameAndID(c.CustomerID, c.FirstName, 
        c.LastName) FROM ModelStoreContainer.Customer as c

Translating the Entity SQL to English: “Query the Customer in the store schema, pull out these three columns and give them back to me as a CustomerNameAndID entity.” AWModel is the namespace of the conceptual model’s entity container. You’re required to use the strongly typed names of both the Conceptual Schema Definition Language (CSDL) and SSDL types that are referenced in the expression.

As long as the results of the projection (an integer, a string and a string) match the schema of the target entity, the mapping will succeed. I’ve tried to use functions and concatenation within the projection—for example, (c.CustomerID, c.FirstName + c.LastName)—but this fails with an error stating that FUNCTIONs are not allowed. So I’m forced to use the FirstName and LastName properties and let the client deal with concatenation.

Placing the QueryView into the Metadata

You must place the QueryView expression within the EntitySetMapping element for the entity that goes inside the EntityContainerMapping in the metadata. Figure 4 shows this QueryView (highlighted in yellow) in the raw XML of my EDMX file.

Figure 4 A QueryView in the Mappings Section

Figure 4 A QueryView in the Mappings Section

Now my CustomerNameAndID is part of my model and will be available to any consumer. And there is another advantage to the QueryView. Even though the goal of this QueryView is to create a read-only reference list, you can also update entities that are mapped using QueryViews. The context will track changes to CustomerNameAndID objects. Although Entity Framework is not able to auto-generate insert, update and delete commands for this entity, you can map stored procedures to it.

Reaping the Benefits of the QueryView

Now that you have the QueryView in the model, you don’t need to depend on projections or anonymous types to retrieve these views of your data. In WCF Data Services, CustomerNameAndIDs becomes a valid entity set to query against, as shown here:

List<CustomerNameAndID> custPickList = 
  context.CustomerNameAndIDs.ToList();

No messy projections. Better yet, you can create service operations in your custom WCF Services that are now able to return this strongly typed object without having to define new types in your application and project into them.

public List<CustomerNameAndID> GetCustomerPickList()
    {
      using (var context = new AWEntities())
      {
        return context.CustomerNameAndIDs.OrderBy(
          c => c.LastName).ToList();
      }
    }

Because of the limitation that prevents us from concatenating the first and last names in the QueryView, it’s up to the developers who consume the service to do this concatenation on their end.

WCF RIA Services can also benefit from the QueryView. You may want to expose a method for retrieving a restaurant pick list from your domain service. Rather than having to create an extra class in the domain service to represent the projected properties, this RestaurantPickList entity is backed by a QueryView in the model, which makes it easy to provide this data:

public IQueryable<RestaurantPickList> GetRestaurantPickList()
    {
      return context.RestaurantPickLists;
    }

QueryViews or Projections—We’ve Got You Covered

Having the ability to project views over your data types is a huge benefit in querying, and it’s a great addition to WCF Data Services. Even so, there are times when having access to these views, without having to project and without having to worry about sharing the result, will simplify some of your coding tasks.

One last note: With the introduction of foreign keys in the .NET Framework 4 version of Entity Framework, QueryView pick lists make even more sense because you can return read-only entities and simply use their properties to update foreign key properties in the entities you’re editing.  


Julie Lerman is a Microsoft MVP, .NET mentor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other Microsoft .NET topics at user groups and conferences around the world. Lerman blogs at thedatafarm.com/blog and is the author of the highly acclaimed book, “Programming Entity Framework” (O’Reilly Media, 2009). Follow her on Twitter: julielerman.

Thanks to the following technical expert for reviewing this article: Alex James