PartialSchema So I have a working LINQ to SQL provider for BlogEngine.Net. Now what? Given a little spare time, how about I see if I can’t use it to support running multiple blogs from the same installation? More importantly, see if I can use it to support running multiple blogs from the same database?

Doing just that turns out not to be all that difficult.

Scheming

The current architecture for BlogEngine.Net’s data already has a bit more cohesion than it technically needs. All the objects have their own individual Ids and those Ids are used to relate objects to each other (though there is one exception). Since every object already has its own Id (usually a Guid), splitting objects into separate blogs isn’t the chore it might otherwise have been.

There are two options when it comes to dividing items up into multiple blogs. First, each object can have a column added to its table to indicate which blog it is associated with. Second, you can create a cross-reference table that associates a blog Id with the object Id for the blog.

Columns
My initial impulse in most cases would be to add a BlogId column to the tables where it is needed. The reason is simple: objects belonging to the blog are in a true parent-child relationship and that relationship is generally best expressed as a field on the child indicating its parent. The relationship can (and really should) be enforced with a foreign key constraint on the column to ensure that the relationship is intact.

Cross-references
Having cross-reference tables is a bit more problematic and carries with it some maintenance and performance concerns. Not only does it force a join when you want to read the objects for a specific blog, but it means that insert, update, and delete commands now have to involve two tables instead of just one. One advantage of cross-reference tables is that they’re easier to extract back out if you need to devolve your data. Additionally, foreign key constraint integrity is triggered when the cross-reference entry is created instead of on your blog objects themselves—making your touch a bit lighter if you have other actors in the system.

Complicating Things 
No decision is best for every occasion, and when it came time to design how I wanted multiple blogs to work, I was really reluctant to mess with the native tables of BlogEngine.Net. I’m not sure if my hesitation is a matter of respect for a project I’m not involved in or if I’m just being unreasonably squeamish, but I eventually chose to go the cross-reference route. My main reasoning is that I wanted my intrusions to remain light and easily devolved.

I ♥ Linq

Now, normally, adding a super-structure on top of an existing infrastructure is a real pain. Editing all your SQL statements manually becomes an exercise in precision string manipulation and if you’re working through stored procedures… ugh. Linq made this really easy.

Here’s an example from the FillProfiles method of the blog provider.

var profileData = from p in context.Profiles
                  select p;
if (isMultiBlog)
{
    profileData = from p in profileData
                  join bp in context.BlogProfiles on p.ProfileID equals bp.ProfileId
                  where bp.BlogId == Utils.GetBlogId()
                  select p;
}

The initial select is good for the general case. It pulls all the objects from the Profiles table. Adding a filter when we have multiple blogs is added in the if clause. Note that the second select references the first (“from p in profileData”). Linq knows that the second “from” is a refinement of the first and puts them together logically. Since Linq defers execution of the query until it’s actually used, the query sent to the server includes the full constraint (i.e. filtering happens on the database). Here’s the statement that’s actually sent.

SELECT [t0].[ProfileID], [t0].[UserName], [t0].[SettingName], [t0].[SettingValue]
FROM [dbo].[be_Profiles] AS [t0]
INNER JOIN [dbo].[be_BlogProfiles] AS [t1] ON [t0].[ProfileID] = [t1].[ProfileId]
WHERE [t1].[BlogId] = @p0

This method ensures that you only take the hit of the join if you are in a multi-blog setup. And without pulling everything to the client.

Settings

I had some fun with the Settings table because it is an exception to BlogEngine.Net’s Id rigor. It has interesting impact on the Linq situation, but I think I’ll give it its own (short) post later.

Beta Available

So I tested this in my own home-grown environment and it seems to work as expected. In consequence, I’ve created a new release at the project homepage. I’m calling it a beta, though it barely warrants the label. I worry that it has only been tested in a single environment. If you’re a hearty soul and a BE.Net user, please give it a go. I’ll be spending some time getting it set up and tested in an actual public setting with my personal blogs here shortly. As always, I welcome feedback either at codeplex or comments or via email.