Saving a Silverlight RIA (or WCF RIA) filtered result set

Silverlight RIA (now WCF RIA) services allows developers to create n-tier applications on top of ASP.Net. There are numerous videos and articles on this new technology. This article assumes you know all about RIA Services, DomainDataSources and how FilterDescriptors can be used with ControlParameters to query data from the server side.

I recently needed to save the filtered result set and didn’t find this to be straight forward. I did come up with the solution described here.

Let’s assume you have a simple Contacts database and the user is able to filter by FirstName, LastName etc. Now let’s assume you need to be able to save the filtered result-set (or alternatively bulk modify the filtered result-set). The challenge is to retrieve the Contact id’s with the client side filters applied.

If we were to implement a save function as follows:

IQueryable SaveContacts()
{
    var query = from contact in
                Context.Contacts select contact.Id

    // use the id's in query
}

This would return all the contacts in the database and not the filtered set. Thankfully, the DomainService class implements a virtual function where we can intercept the client side filter expression:

public virtual IEnumerable Query(QueryDescription queryDescription, out int
totalCount);

We however have to merge this expression with the Context.Contacts IQueryable. This didn’t prove to be straight forward as you have to know a little bit about Expressions. I overrode the function like so to save the query for later retrieval:

IQueryable savedQuery;

public override System.Collections.IEnumerable Query(QueryDescription queryDescription,
                                                      out int totalCount)
{
    if (queryDescription.Method.Name == "SaveContacts")
	savedQuery = queryDescription.Query as IQueryable;

    return base.Query(queryDescription, out totalCount);
}

We now have the query expression which contains the “Where” clause generated by the client side FilterDescriptors. We have to merge this query expression into the Context.Contacts query to get the filtered query. The following implementation of SaveContacts demonstrates how to accomplish this:

// The FindWhereMedhod recursively looks for the "Where" clause in the query
// expression
MethodCallExpression FindWhereMethod(MethodCallExpression expression)
{
    if (expression == null)
        return null;

    if (expression.Method.Name == "Where")
        return expression;

    foreach(Expression sub in expression.Arguments)
    {
        MethodCallExpression result = FindWhereMethod(sub as MethodCallExpression);
        if (result != null)
            return result;
    }
    return null;
}

public IQueryable SaveContacts()
{
    IQueryable q = GetContacts();

    MethodCallExpression method = savedQuery.Expression as MethodCallExpression;
    MethodCallExpression whereMethod = FindWhereMethod(method);

    IQueryable contactIds;

    if (whereMethod != null)
    {
        ParameterExpression pe = Expression.Parameter(typeof(Contact), "contact");
        UnaryExpression unaryExpr = whereMethod.Arguments[1] as UnaryExpression;
        MethodCallExpression whereCallExpression = Expression.Call( typeof(Queryable),
                                                                    "Where",
                                                                    new Type[] { q.ElementType },
                                                                    q.Expression, unaryExpr.Operand);
        IQueryable merged = q.Provider.CreateQuery(whereCallExpression) as IQueryable;

        contactIds = from c in merged
                    select c.Id;
    }
    else
    {
        contactIds = from c in q
                     select c.Id;
    }
    // You now have a query merged with the client side contact id's. You can now do
    // whatever you'd like with the contact id's including save them via SqlCommands

    DbCommand command = Context.GetCommand(contactIds);
    string connectionStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;

    using (TransactionScope scope = new TransactionScope())
    using (SqlConnection connection = new SqlConnection(connectionStr))
    {
        connection.Open();
        try
        {
            // There are other queries here that necessitate the transaction scope however,
            // these have been removed for simplicity

            string insertCmd = "insert into ContactResultSet (ContactId) select Id from ({0}) as subQuery";
            insertCmd = string.Format(insertCmd, command.CommandText);
            SqlCommand cmd = new SqlCommand(insertCmd, connection);

            // We have to copy over the parameters
            foreach (SqlParameter param in command.Parameters)
                cmd.Parameters.AddWithValue(param.ParameterName, param.Value);

            cmd.ExecuteNonQuery();
            scope.Complete();
        }
        finally
        {
            connection.Close();
        }
    }
}
Advertisements

2 thoughts on “Saving a Silverlight RIA (or WCF RIA) filtered result set

  1. Hi, i just wanted to say that your post is the solution to a problem that I raised on the SilverLight forums.

    Here’s the link to my post where I quote your post : http://forums.silverlight.net/t/236451.aspx/3/10#
    Here’s the link to the full thread : http://forums.silverlight.net/t/236451.aspx/

    The people on the forum all contributed what they could but Sipank’s post showing how to override the Query method on the server side set me on the path to the resolution.

    With this improved knowledge, i was able to do some googling with a far better targetted set of keywords which finally returned the link to this post.

    Regards,

    Michael

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s