Subsonic 3.0 and "Link" tables.

Question

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"

Answer 1

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;

}

Answer 2

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

What about using the .ToList() method?

http://msdn.microsoft.com/en-us/library/bb342261.aspx

Answer 3

Hi Pino,

Yes, there is a more elegant way. If you're using the ActiveRecord templates, and the Category and BlogPost tables have a foreign key relationship to the BlogToCategory table then your generated Category and BlogPost classes will each have an IQueryable property representing that relationship:

IQueryable<BlogToCategory> BlogToCategories {...}

What you want is an

IQueryable<BlogPost> BlogPosts

property on your Category class. Create a partial class for the Category, and add the IQueryable property:

    public IQueryable<BlogPost> BlogPosts
    {
        get
        {
            var repo = BlogPost.GetRepo();
            return from items in repo.GetAll()
                   join linkItems in BlogToCategories 
                   on items.CatID equals linkItems.CategoryID
                   select items;
        }
    }

Now you can just call cat.BlogPosts.ToList() - the ToList() should be available, are you sure have included the namespace containing the extension methods?