C# – Subsonic 3.0 and “Link” tables

asp.netclinqsubsonicsubsonic3

I have the following set-up.

BlogPosts
BlogToCategory
Category

One blog post can have many categorys and a category can be in many blog posts. (Hence the intermediate table.

How would I go about getting a list of all the blog-posts in one category.

I've tried this but cant seem to get it right (I get a IQueryable -> IEnumerable cast error)

public IEnumerable<BlogPost> FetchAllBlogs(int? CatId)
{
        return from c in CategoryLink.All()
                   where c.CategoryID == CatId
                   select c.BlogPost;

}

Ok as below I've tried the following.

return from blogToCategories in subtext_Link.All()
                      join blogPosts in subtext_Content.All() on blogToCategories.BlogId equals blogPosts.BlogId
                      where blogToCategories.CategoryID == CatId
                      orderby (blogPosts.DateAdded) descending
                      select blogPosts;

Now this is wierd it seems the Join is wrong as whenever there is some data in the Links table (Tablethat links category to blog) it returns ALL blogs.

Also tried the below.

BlogList = new TransformDB().Select
                  .From<subtext_Content>()
                  .InnerJoin<subtext_Link>(subtext_LinksTable.BlogIdColumn, subtext_ContentTable.BlogIdColumn)
                  .Where(subtext_LinksTable.CategoryIDColumn).IsEqualTo(CatId)
                  .ExecuteTypedList<subtext_Content>();

Generated SQL

SELECT [dbo].[subtext_Links].[LinkID],
[dbo].[subtext_Links].[Title],
[dbo].[subtext_Links].[Url],
[dbo].[subtext_Links].[Rss],
[dbo].[subtext_Links].[Active],
[dbo].[subtext_Links].[CategoryID],
[dbo].[subtext_Links].[BlogId],
[dbo].[subtext_Links].[PostID],
[dbo].[subtext_Links].[NewWindow],
[dbo].[subtext_Links].[Rel],
\r\n[dbo].[subtext_Content].[ID],
[dbo].[subtext_Content].[Title],
[dbo].[subtext_Content].[DateAdded],
[dbo].[subtext_Content].[PostType],
[dbo].[subtext_Content].[Author],
[dbo].[subtext_Content].[Email],
[dbo].[subtext_Content].[BlogId],
[dbo].[subtext_Content].[Description],
[dbo].[subtext_Content].[DateUpdated],
[dbo].[subtext_Content].[Text],
[dbo].[subtext_Content].[FeedBackCount],
[dbo].[subtext_Content].[PostConfig],
[dbo].[subtext_Content].[EntryName],
[dbo].[subtext_Content].[DateSyndicated]\r\n
FROM [dbo].[subtext_Links]\r\n INNER
JOIN [dbo].[subtext_Content] ON
[dbo].[subtext_Links].[BlogId] =
[dbo].[subtext_Content].[BlogId]\r\n
WHERE
[dbo].[subtext_Links].[CategoryID] =
@0"

Best Answer

You need to join the BlotToCategory and BlogPost tables:

public IEnumerable<BlogPost> FetchAllBlogs(int? CatId)
{
  return from blogToCategories in BlogToCategory.All() 
         join blogPosts in BlogPost.All() on blogPosts.Id equals blogToCategories.BlogId 
         where blogToCategories.CategoryID == CatId
         select blogPosts;

}
Related Topic