DOC

LINQ Projection Queries and Alternatives in WCF Services

By Earl Boyd,2014-05-11 18:08
7 views 0
LINQ Projection Queries and Alternatives in WCF Services

LINQ Projection Queries and

    Alternatives in WCF Services

    Julie Lerman

    Download the Sample Code

    The 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 methodor worse, used a LINQ projection in a Windows

    Communication Foundation (WCF) service operationyou 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:

http://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 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 Enabling Projected Strong Types from an EDM they’re nullable) and be persisted to the database. 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

    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 projectionfor 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

    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.

Report this document

For any questions or suggestions please email
cust-service@docsford.com