How can a Ruby on Rails style counter cache be implemented using Linq2Sql?

Question

How can for example a Blog post comment counter cache be implemented using linq2sql?

The way I currently implement this in a site I am developing is using hand coded sql queries and the ExecuteCommand() adhoc sql execution method of the linq2sql datacontext.

I would like to know if there is a better/simpler approach using linq2sql that can do the same thing.

I am really curious to find out the approach that stack overflow uses to implement counter caching using linq 2 sql. I listened to a podcast about the stack overflow site architecture which uses linq2sql and uses counter cache's to store site statistics. So maybe a developer from stack overflow can chime in.

Below is the sql query script that I use when adding a comment to a post. the query adds a comment for the post and increments the number of comments for the post in a cached column in the posts table (hence the name counter cache which is a RoR term I believe)

string addcommentquery = @"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
        SET NOCOUNT ON
        SET XACT_ABORT ON
        DECLARE @CommentId INT
    Declare @Title nvarchar(50) set @Title = {0}
    Declare @PostId int set @PostId = {1}
    BEGIN TRANSACTION
        INSERT INTO dbo.Comments
        (
        ,Title
        ,PostId
        )
        VALUES
        (@Title
        ,@PostId
        )
        UPDATE dbo.Posts
    SET 
    CommentCount = CommentCount + 1 --atomic update-no need to get the post and then submit an update that could fail concurrency timestamp
    WHERE PostId= @PostId
    COMMIT TRANSACTION 
    SET @CommentId = SCOPE_IDENTITY() 
        SELECT @CommentId ";

//add comment to post with id=24
_dc.ExecuteQuery<int>(addcommentquery,"My 2 cents",24)

also when we remove a comment for a post we need to decrement the count in the counter cache column

string removecommentquery = @"SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
        SET NOCOUNT ON
        SET XACT_ABORT ON
Declare @CommentID int set @CommentID = {0}
Declare @PostId int set @PostId = {1}
BEGIN TRANSACTION
DELETE dbo.Comments
WHERE CommentID = @CommentID 
UPDATE dbo.Posts
CommentCount = CommentCount - 1 --atomic update
WHERE PostId= @PostId
COMMIT TRANSACTION

//remove comment with id=1 that was added to post with id=24
_dc.ExecuteNonQuery(removecommentquery,1,24)

Not sure if the approach below is how you would do this in linq 2 sql

//pseudo code
public partial class Post    
{

public void AddComment(Comment c)  
{  
    this.Comments.Add(c);
    this.commentCount = CommentCount + 1;
}

public void RemoveComment(Comment c)  
{  
    this.Comments.Remove(c);
    this.commentCount = CommentCount - 1;
}  

public void Persist()
{
    int tryCount=0;
    while(result==false && count<3)
    {
        try
        {
            tryCount++;
            _dc.SubmitChanges();
            result = true;

        }
        catch(VersionNoMatchException)
        {
            BlockForRandomMiliseconds(bound(10,100));
        }
    }  

    if(result == false)throw new RetryFailedException("max tries: " + tryCount );  

}  
}

I would really like to use linq2sql for this kind of functionality instead of hand rolling a query.

Thanks.