Applying where clause to childcollection in LightS

Question

How can I have a where clause in a LightSwitch query against a child collection?

In my example, every blog can have many comments and I want to write a query like this so that the admin see only the blog entries without any comments:

query = query.Where(q => q.comment.Count() == 0).SingleOrDefault()

I've included the SingleOrDefault() because I want the administrator to see only one item per query they can then add a comment and move to next item on the blog screen.

When trying the above query, I get the following compile time error:

Error 2

Cannot convert method group 'SingleOrDefault' to non-delegate type 'System.Linq.IQueryable'. Did you intend to invoke the method?

I thinks this is the right error because:

partial void Query1_PreprocessQuery(ref IQueryable<blog> query)
{
    query=query.Where(q => q.Evaluations.Count() == 0).SingleOrDefault;
}

Return IQueryable which is a collection not single one .

I've tried changing the signature to partial void Query1_PreprocessQuery(ref blog query) and now I get another compile error saying blog does not contain a definition for where.

So, how can I implement the required type of query in my LightSwitch HTMLClient app.

Answer 1

In order to organise this type of scalar query, you need to use the following type of LINQ expression in your query's pre-process method:

partial void BlogWithNoComment_PreprocessQuery(ref IQueryable<blog> query)
{
    query = query.Where(q => q.comment.Count() == 0).Take(1);
}

In addition, you'll need to ensure your query is designed to only return a single result by selecting the 'One' option in the query's 'Number of Results Returned' property as follows (the required property selection is highlighted in red):

This type of approach is covered in the following Microsoft blog in the Scalar Queries section:

Visual Studio LightSwitch Team Blog: How to Create Composed and Scalar Queries (Ravi Eda)

Even though the screen examples in this blog are based on the Silverlight client, the query techniques it covers apply equally to the HTML client.