.Net RIA Services and Multi-table Joins

Why are we here?

On the off chance that you are developing a real-world application based on a normalized database that you’d like to develop using Silverlight 3 and .Net RIA Services, I have some good news. After thrashing about for several days trying to get a multiple table join to come back down the wire, I think I have it figured out, and so I wanted to share what I’d learned. This is not going to be one of those seriously abstract articles where the author has come up with something you’re not quite sure where you’d use it. This is (hopefully) going to be about ‘pay your bills’ code. Besides, I’m just not that advanced a developer ;-). So if, like me, you’re slogging along trying to use this fantastic new technology in the real world, perhaps you should read on.

The Model

We have a normalized database model consisting of Clients, Client tasks (called Dockets), Partners, Employees, Appointments, and so on. To support reliable data entry, we eliminate a lot of repetitive typing by using lookup tables to hold relatively static data (like postal codes, states, categories, etc.). Below is a snippet of the Entity Framework model that is relevant to this discussion.



Figure 1. Entity Framework Model fragment

Getting Data

The recently released Beta 1 of .Net RIA Services is pretty powerful right out of the box. It allows you to use a new syntax to include foreign key relations returning IQueryable or IEnumerable objects right to the client. For example, if we wanted to get Clients and their Contacts, we could write this method in the server-side Domain Data Service class:

public IQueryable<Clients> GetClientsAndContacts()
    return this.Context.Clients.Include("ClientContacts").OrderBy(c => c.ClientName);

Where this appears to break down is when you have a chain of relations, since the navigation properties not directly associated with the primary object to be fetched are not visible to it, and thus, it is no longer an object of the primary type to be fetched. From the walkthroughs and available documentation, you would think that you could simply chain relationships ad infinitum until your server begged for mercy (usually on its knees). But you can’t. From the model fragment above, you can see that Clients has no direct navigation property called ZipCodes. If, in fact, RIA Services will eventually be united under the umbrella of ADO.Net Data Services (Astoria), I am less confident than I used to be that what I am about to show you will work in that context.

So how do we get, for example, a complete mailing label for each client?

Extending .Net RIA Services Classes

From my participation in the Silverlight.net forums, I learned about extending the base methods that the Visual Studio 2008 Silverlight 3 Domain Service item template provides. So, if we want a new class for our hypothetical mailing label, it might look like this:

public partial class MailingLabel 
    public string ClientName { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string City { get; set; }
    public string StateAbbrev { get; set; }
    public string ZipCode { get; set; }
    public string ZipPlus4 { get; set; }

We would write this class alongside the methods generated for us from the Domain Service item template that we pointed at an Entity Framework model.

It might also be a good idea (although not strictly necessary in this case) to write an equivalent metadata class for our new MailingLabel class, in case we decide later that we need databinding for it (say, to an ItemsControl for a report – hint, hint). Based on the established pattern, that would look like this: 

   // The MetadataTypeAttribute identifies MailingLabelMetadata as the class
    // that carries additional metadata for the MalingLabel class.
    public partial class MailingLabel
#pragma warning disable 649 // temporarily disable compiler warnings about unassigned fields
        // This class allows you to attach custom attributes to properties
        // of the MailingLabel class.
        // For example, the following marks the Xyz property as a
        // required field and specifies the format for valid values:
        //    [Required]
        //    [RegularExpression(“[A-Z][A-Za-z0-9]*”)]
        //    [StringLength(32)]
        //    public string Xyz;
        internal sealed class MailingLabelMetadata
            // Metadata classes are not meant to be instantiated.
            private MailingLabelMetadata() {}
            [Bindable(true, BindingDirection.OneTime)]
            public string ClientName;
            public string Address1;
            public string Address2;
            public string City;
            public string StateAbbrev;
            public string ZipCode;
            public string ZipPlus4;
            public EntityState EntityState;
#pragma warning restore 649 // re-enable compiler warnings about unassigned fields

You can add [Include] , [Exclude],  and validation attributes to this class as indicated in the comments to your heart’s content.

To revisit the constraint that got us here, even though we’d love to be able to do something like this:

public IQueryable<Clients> GetMailingLabels()
    return this.Context.Clients
.Where(c => c.Label == true)
.OrderBy(c => c.ClientName); }

we simply can’t. In fact, this method will never return asynchronously. Don’t ask. I thought perhaps I was delving too deep into the hierarchy for Entity Framework to handle, and that it was simply timing out. Strictly speaking, it was not. Essentially, the threads that were spun off in an attempt to perform the fetch gave up and exited. To it’s credit .Net RIA Services, made at least 3-4 attempts, each time with a new thread.

Linq & C# 3.0 to the Rescue

So I fired up LinqPad, and started writing standard Linq against my database until I got what I wanted:

from c in db.Clients
   where c.Label == true
orderby c.ClientName join cc in db.ClientContacts.Distinct() on c.ClientID equals cc.ClientID join z in db.ZipCodes on cc.ZipCodeID equals z.ZipCodeID join s in db.States on z.StateID equals s.StateID select new { c.ClientName, cc.Address1, cc.Address2, z.City, s.StateAbbrev, z.ZipCode, cc.ZipPlus4 };

It almost looks like pure traditional SQL, and it ran really fast through over 500+ clients with multiple contacts, and of course 50 states, as well as almost 50,000 zip codes. And hats off to the recent Linq Deep Dive series in ASP.Net Pro magazine alerting me to the huge performance variation in Linq queries without proper ordering of the various clauses.

So the central question remains of how to get this data into the MailingLabel class so that it can be brought over the wire to my Silverlight client, when it’s really returning an Anonymous Type? It turns out that C# 3.0’s Class Initializer feature is just what I needed. After a lot of questions on the forum and experimentation, here is the solution:

public List<MailingLabel> GetMailingLabels()
    var query =
        (from c in Context.Clients
         orderby (c.ClientName)
         where c.Label == true
         join cc in Context.ClientContacts.Distinct()
         on c.ClientID equals cc.Clients.ClientID
         join z in Context.ZipCodes
         on cc.ZipCodes.ZipCodeID equals z.ZipCodeID
         join s in Context.States
         on z.States.StateID equals s.StateID
         select new MailingLabel 
{ ClientName=c.ClientName, Address1=cc.Address1, Address2=cc.Address2, City=z.City, StateAbbrev=s.StateAbbrev, ZipCode=z.ZipCode, ZipPlus4=cc.ZipPlus4 }).ToList(); return query; }

Notice that I had to insert navigation properties at several points in the relation chain (e.g., cc.ZipCodes.ZipCodeID equals z.ZipCodeID). I believe that’s an artifact of using EF, but don’t know for sure. You may also be wondering why I did not return an IQueryable or an IEnumerable. It turned out to be just too hard to force the compiler to agree with me, so after an hour of trying to coerce it, I gave in to the reliable List<T>. And I was able to coerce the Anonymous Type returned by the query into my MailingLabel class by using a C# 3.0 Initializer. Very handy, that.

The Payoff

Back on the client, we can now fire off a call to our context’s LoadMailingLabels() method, and in the Loaded event handler, we can do the wonderful things we’d like to do with any strongly typed object – iteration and databinding! The only down side to extending .Net RIA Service classes using EF like this is that if we ever need to make any schema changes, we will need to make a back-up of our modified files and break out our dusty diff tool to re-insert all of our modifications. They tell me that either Oleg or one of his T4 cohorts are looking at tooling for this problem, since IdeaBlade already supports non-destructive schema changes in their products.

I am just beginning to consider all the possibilities for projection, aggregates, and other reporting-required rowsets that a line of business application would normally require, and that I can now bring across the wire as strongly typed objects with this simple coding pattern. If I can write it in Linq, I can have it on the (Silverlight 3) client! Remember how hard remoting was? Stay tuned.

Hope this helps.

Bob Baker

P.S. I went to Orlando CodeCamp on March 28, 2009. Did you?

posted @ Saturday, April 18, 2009 1:33 AM



Comments have been closed on this topic.