Jan
3
2011
Updated 2/4/2011 - The blog code showed a technique using the IncludeAttribute that turned out not to work. A new technique is now shown.
A common question on the forums has been how to use project into POCO objects which are derived from one or more server side entity objects while still being able to query client side against the fields that are not being projected. These discussions usually start with people trying to use the Invoke to load data or discovering that the Query methods do not allow complex objects to be passed in. I have come up with a solution that makes the projected POCO completely IQueryable without having to play games with the Invoke passing parameters to the Query method as XML.
First, I am going to start with the database and Model from John Papa’s MVVM example at Firestarter. We have three entities that look like this:
For performance reasons, we want to create a new POCO object named MemberCheckout which is a projection of these three tables. MemberCheckout has to be a strongly typed object as WCF RIA Services does not support anonymous typed.
public class MemberCheckout
{
[Key]
public int CheckoutID { get; set; }
public string MemberName { get; set; }
public DateTime CheckoutDate { get; set; }
public string Title { get; set; }
}
We would then add a new Query to the DomainService to load MemberCheckout (the Dummy method will be explained later):
public IQueryable<MemberCheckout> GetMemberCheckouts()
{
var query = from checkout in ObjectContext.Checkouts
orderby checkout.CheckoutDate descending
select (new MemberCheckout()
{
CheckoutID = checkout.CheckoutID,
Checkout = checkout,
MemberName = checkout.Member.MemberName,
CheckoutDate = checkout.CheckoutDate,
Title = checkout.Book.Title
});
return query;
}
public IQueryable<Checkout> GetCheckoutsDummy()
{
throw new NotSupportedException("Dummy loads cannot be called.")
}
The question is, how do we filter MemberCheckout on a value that is not in MemberCheckout itself? For example, what if I want to to query for MemberCheckout objects where a book was published on a particular day. The answer is, if we associate our MemberCheckout object back to the entities that it was projected from then we will be able to query against them. First, we need to modify the MemberCheckout POCO to have a link to the original model.
The original POCO already has the CheckoutID field as it was a natural key for the derived POCO. If the POCO does not already have a key that exists in the original model then you will need to add one. The new MemberCheckout will look like this:
public class MemberCheckout
{
[Key]
public int CheckoutID { get; set; }
[Association("MemberCheckout_Checkout", "CheckoutID", "CheckoutID")]
public Checkout Checkout { get; set; }
public string MemberName { get; set; }
public DateTime CheckoutDate { get; set; }
public string Title { get; set; }
}
Notice that the only real change was the addition of the Checkout field and an AssociationAttribute. I usually recommend having an Association set on both sides of an association but that recommendation does not apply in this case.
Next is the tricky part, we also need to make sure that the related tables that we want to query have been generated client side. If the DomainService you are using already has query methods for the related tables you want to query against then nothing more should need to be done and you will not need the GetCheckoutsDummy method. However, if you do not have query methods for the related tables then you will need to add dummy ones that do not actually work but will cause the entitiy to be generated client side. In the original version of this blog I included an alternate method using an Include. That does not work and the POCO object should not have an Include on the association attribute unless you want the associated object to be sent to the client. If the entity loaded through the Dummy method has IncludeAttributes on associations that reach the other entities that you want to query against then only the single Dummy query will be needed. Otherwise, you will need multiple dummies. With all of that setup complete, we can now write queries client side that query against the full model. For example, I executed this load client side:
var qry = from m in Context.GetMemberCheckoutsQuery()
where m.Checkout.Book.PublishDate == new DateTime(2009, 2, 3)
select m;
Context.Load(qry, false).Completed += new EventHandler(TestLoad_Completed);
The result was a single entity and when I checked what was actually executing on the SQL Server I found that the query had correctly flowed all of the way back to the SQL Server.