Filtering items in the ThinqLinq aggregation feed by ThinqLinq

Filtering items in the ThinqLinq aggregation feed

When I first released ThinqLinq, the only filtering I applied was to only select the top 20 posts. I was recently asked if I could extend the implementation so that it the aggregation feed could be filtered based on the categories. Since ThinqLinq uses LINQ for the data interaction, it is relatively easy to add filtering to the existing query.

However, in this case, the filtering is not a simple Where clause on the underlying table. That is because the table structure uses a Many to Many relationship between the Categories and PostItems. Here's the object relationships as created by the LINQ to SQL designer:

Typically when querying a database in a many to many relationship, we start on one end and work our way to the other. LINQ offers another option. Consider the following LINQ Query:

From catPost In dc.CategoryPosts _
Where catPost.Category.Title = Request.QueryString("Category") _
Select catPost.PostItem

In this query, we actually start in the middle. We can do it because we can use object trees rather than having to rely on joins. With LINQ, the many-to-many table contains a reference to the related objects on both sides. This way, we can use one side (the Category) in the Where clause and the other side (PostItem) for the Select clause. Naturally, we could also mix and match the results.

For the rss feed, we need to return the full results if no query string is supplied, but filter it if there is one. In addition, we will replace the spaces with underscores so that we can include them in the query string. With VB's Replace method, we can easy convert back from the underscores to the spaces. Here's the first query that the example will use:

Dim query As IQueryable(Of PostItem)
If Request.QueryString("Category") <> "" Then
query = From catPost In dc.CategoryPosts _
           Where catPost.Category.Title = Replace(Request.QueryString("Category"), "_", " ") _
           Select catPost.PostItem
query = dc.PostItems
End If

We declare the query as IQueryable(Of PostItem) so that we can further refine the query results later. Regardless of whether we use the filtered or unfiltered version, we still want to limit the results to the most recent 20 items. The composability of IQueryable allows us to further refine our query as follows:

query = From post In query _
            Order By post.PublicationDate Descending _
            Take 20

Notice that we don't include the Select clause as it is optional in VB. Now that we have our target results, we can generate our XML as we did before in this post.

Posted on - Comment
Categories: VB Dev Center - VB - LINQ -
comments powered by Disqus