Discussion Forum Update (tables and classes)

And finally, the new PostRepository class. Now simplified from the previous versions…

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Road.Helpers;

namespace Road.Models
{
	public sealed class PostRepository : BaseRepository
	{
		/// <summary>
		/// Constructor
		/// </summary>
		/// <param name="_db">Datacontext</param>
		public PostRepository(CMDataContext _db) : base(_db) { }

		#region Retrieval

		/// <summary>
		/// Gets a paged list of topics
		/// </summary>
		/// <param name="index">Current page index</param>
		/// <param name="limit">Page size limit</param>
		/// <param name="unapproved">Include unapproved topics (optional)</param>
		/// <param name="status">Array of status topic status types (optional)</param>
		/// <param name="newestFirst">Sort by newest topics first (optional)</param>
		/// <returns>Paged list of topics</returns>
		public PagedList<Topic> TopicPageList(int index, int limit,
			bool unapproved = false, TopicStatus[] status = null,
			bool newestFirst = true)
		{
			var query = from p in db.Posts
						select p;

			return
				TopicQuery(query, unapproved, status, newestFirst).ToPagedList(index, limit);
		}

		/// <summary>
		/// Gets a topic by the given id and finds corresponding replies if any
		/// </summary>
		/// <param name="id">Topic id to search</param>
		/// <param name="index">Current page index</param>
		/// <param name="limit">Page size limit</param>
		/// <param name="unapproved">Include unapproved replies (optional)</param>
		/// <param name="status">Array of reply status types (optional)</param>
		/// <param name="newestFirst">Sort by newest replies first (optional)</param>
		public Topic TopicById(int id, int index, int limit,
			bool unapproved = false, ReplyStatus[] status = null,
			bool newestFirst = false)
		{
			var query = from p in db.Posts
						where p.PostId == id
						select p;

			Topic topic = TopicQuery(query).FirstOrDefault();

			// We have a topic and replies were also requested
			if (topic != null && limit > 0)
			{
				var rquery = from p in db.Posts
							 where p.ParentId == topic.Id
							 select p;

				topic.Replies =
					ReplyQuery(rquery, unapproved, status, newestFirst).ToPagedList(index, limit);
			}

			return topic;
		}

		/// <summary>
		/// Gets a paged list of topics belonging to a tag(s)
		/// (This uses the lowercase TagName property)
		/// </summary>
		/// <param name="tag">Array of tags to search</param>
		/// <param name="index">Current page index</param>
		/// <param name="limit">Page size limit</param>
		/// <param name="unapproved">Include unapproved topics (optional)</param>
		/// <param name="status">Array of status topic status types (optional)</param>
		/// <param name="newestFirst">Sort by newest topics first (optional)</param>
		/// <returns>Paged list of topics</returns>
		public PagedList<Topic> TopicsByTags(string[] tag, int index, int limit,
			bool unapproved = false, TopicStatus[] status = null, bool newestFirst = true)
		{
			var query =
				from t in db.PostTags
				join pt in db.PostTagRelationships on t.TagId equals pt.TagId
				join p in db.Posts on pt.PostId equals p.PostId
				where tag.Contains(t.TagName)
				select p;

			return
				TopicQuery(query, unapproved, status, newestFirst).ToPagedList(index, limit);
		}

		/// <summary>
		/// Gets an individual topic belonging to a list of tag(s)
		/// </summary>
		/// <param name="tagId">Array of tag Ids to search</param>
		/// <param name="index">Current page index</param>
		/// <param name="limit">Page size limit</param>
		/// <param name="unapproved">Include unapproved topics (optional)</param>
		/// <param name="status">Array of status topic status types (optional)</param>
		/// <param name="newestFirst">Sort by newest topics first (optional)</param>
		/// <returns>Paged list of topics</returns>
		public PagedList<Topic> TopicsByTagIds(int[] tagId, int index, int limit,
			bool unapproved = false, TopicStatus[] status = null, bool newestFirst = true)
		{
			var query =
				from t in db.PostTags
				join pt in db.PostTagRelationships on t.TagId equals pt.TagId
				join p in db.Posts on pt.PostId equals p.PostId
				where tagId.Contains(t.TagId)
				select p;

			return
				TopicQuery(query, unapproved, status, newestFirst).ToPagedList(index, limit);
		}

		/// <summary>
		/// Gets a paged list of topics by the search criteria
		/// </summary>
		/// <param name="search">Title and body search terms</param>
		/// <param name="index">Current page index</param>
		/// <param name="limit">Page size limit</param>
		/// <param name="unapproved">Include unapproved topics (optional)</param>
		/// <param name="status">Array of status topic status types (optional)</param>
		/// <param name="newestFirst">Sort by newest topics first (optional)</param>
		/// <returns>Paged list of topics</returns>
		public PagedList<Topic> TopicsBySearch(string search, int index, int limit,
			bool unapproved = false, TopicStatus[] status = null, bool newestFirst = true)
		{
			var query = from p in db.Posts
						where p.BodyText.Contains(search) || p.Title.Contains(search)
						select p;

			return TopicQuery(query, unapproved, status, newestFirst).ToPagedList(index, limit);
		}

		/// <summary>
		/// Gets a paged list of replies by the search criteria
		/// (only searches the bodytext)
		/// </summary>
		/// <param name="search">Search terms</param>
		/// <param name="index">Current page index</param>
		/// <param name="limit">Page size limit</param>
		/// <param name="unapproved">Include unapproved topics (optional)</param>
		/// <param name="status">Array of topic status types (optional)</param>
		/// <param name="newestFirst">Sort by newest topics first (optional)</param>
		/// <returns>Paged list of topics</returns>
		public PagedList<Reply> RepliesBySearch(string search, int index, int limit,
			bool unapproved = false, ReplyStatus[] status = null, bool newestFirst = true)
		{
			var query = from p in db.Posts
						where p.BodyText.Contains(search)
						select p;

			return ReplyQuery(query, unapproved, status, newestFirst).ToPagedList(index, limit);
		}

		#endregion

		#region Modify Methods

		/// <summary>
		/// Saves or creates a new reply under the given topic
		/// </summary>
		/// <param name="topic">Topic the reply belongs to</param>
		/// <param name="reply">Reply to save</param>
		/// <returns>Returns the saved reply</returns>
		public Reply SaveReply(Topic topic, Reply reply)
		{
			Post p = null;
			Post t = null;
			DateTime dt = DateTime.UtcNow;

			// Editing existing reply
			if (reply.Id != 0)
			{
				p = (from post in db.Posts
					 where post.PostId == reply.Id
					 select post).FirstOrDefault();
			}
			else // New reply
			{
				t = (from post in db.Posts
					 where p.PostId == topic.Id
					 select post).Single();

				// Increment reply count
				t.ReplyCount++;

				// Topic has changed activity
				t.LastModified = dt;

				p = new Post();
				p.ParentId = topic.Id;
				p.CreatedDate = dt;
				p.ReplyCount = 0;
				p.ViewCount = 0;
				db.Posts.InsertOnSubmit(p);
			}

			p.IsApproved = reply.Approved;
			p.Status = (byte)reply.Status;
			p.LastModified = dt;

			// Adding/Editing replies changes last activity on topic
			t.LastActivity = dt;

			p.BodyHtml = reply.Body;
			p.BodyText = reply.Summary;

			p.ContentHash = reply.ContentHash;
			p.Threshold = reply.Threshold;

			// Save reply
			db.SubmitChanges();

			// If this is a new reply...
			if (p.PostId > 0 && reply.Id == 0)
			{
				// We now have an Id to set
				reply.Id = p.PostId;

				// Create Author, PostRelation and PostAuthor relationships
				Author a = new Author();
				a.MemberId = reply.CreatedBy.MemberId;
				a.AuthorIP = reply.CreatedBy.IP;
				a.AuthorName = reply.CreatedBy.Name;

				a.AuthorEmail =
					(!string.IsNullOrEmpty(reply.CreatedBy.Email)) ?
					SecUtility.Encrypt(reply.CreatedBy.Email) : "";

				a.AuthorWeb = reply.CreatedBy.Web;
				db.Authors.InsertOnSubmit(a);
				db.SubmitChanges();

				if (a.AuthorId > 0)
				{
					reply.CreatedBy.Id = a.AuthorId;

					PostAuthorRelationship pa = new PostAuthorRelationship();
					pa.AuthorId = a.AuthorId;
					pa.PostId = p.PostId;

					db.PostAuthorRelationships.InsertOnSubmit(pa);
					db.SubmitChanges();
				}
			}

			return reply;
		}

		/// <summary>
		/// Removes a given reply from the given topic
		/// </summary>
		/// <param name="topic">Topic to remove from</param>
		/// <param name="reply">Reply to remove</param>
		/// <returns>True if removed sucessfully, defaults to false</returns>
		public bool DeleteReply(Topic topic, Reply reply)
		{
			Post p = (from post in db.Posts
					  where post.PostId == reply.Id
					  select post).FirstOrDefault();

			Post t = (from post in db.Posts
					  where post.PostId == topic.Id
					  select post).FirstOrDefault();

			if (p != null && t != null)
			{
				t.ReplyCount--;

				db.Posts.DeleteOnSubmit(t);
				db.SubmitChanges();
				return true;
			}

			return false;
		}

		/// <summary>
		/// Saves or creates a new topic
		/// </summary>
		/// <param name="topic">Topic to save</param>
		/// <returns>Returns the saved topic</returns>
		public Topic SaveTopic(Topic topic)
		{
			Post p = null;
			DateTime dt = DateTime.UtcNow;

			// Editing existing topic
			if (topic.Id != 0)
			{
				p = (from post in db.Posts
					 where post.PostId == topic.Id
					 select post).Single();
			}
			else // New topic
			{
				p = new Post();
				p.CreatedDate = dt;
				p.ViewCount = 0;
				p.ReplyCount = 0;
				db.Posts.InsertOnSubmit(p);
			}

			p.Title = topic.Name;
			p.ParentId = 0; // Topic has no parent
			p.IsApproved = topic.Approved;
			p.Status = (byte)topic.Status;
			p.Slug = topic.Slug;

			p.LastActivity = dt;
			p.LastModified = dt;

			p.BodyHtml = topic.Body;
			p.BodyText = topic.Summary;

			p.ContentHash = topic.ContentHash;
			p.Threshold = topic.Threshold;

			// Save
			db.SubmitChanges();

			// If this is a new topic...
			if (p.PostId > 0 && topic.Id == 0)
			{
				// Set the Id, now that we have one
				topic.Id = p.PostId;

				// Create author and set relationship
				Author a = new Author();
				a.MemberId = topic.CreatedBy.MemberId;
				a.AuthorIP = topic.CreatedBy.IP;
				a.AuthorName = topic.CreatedBy.Name;

				a.AuthorEmail = (!String.IsNullOrEmpty(topic.CreatedBy.Email)) ?
					SecUtility.Encrypt(topic.CreatedBy.Email) : "";

				a.AuthorWeb = topic.CreatedBy.Web;
				db.Authors.InsertOnSubmit(a);

				db.SubmitChanges();
				if (a.AuthorId > 0)
				{
					topic.CreatedBy.Id = a.AuthorId;

					PostAuthorRelationship pa = new PostAuthorRelationship();
					pa.AuthorId = a.AuthorId;
					pa.PostId = p.PostId;

					db.PostAuthorRelationships.InsertOnSubmit(pa);
					db.SubmitChanges();
				}
			}

			ApplyTags(topic.Tags.ToList(), topic);

			return topic;
		}

		/// <summary>
		/// Removes a given topic and optionally, the replies
		/// </summary>
		/// <param name="topic">Topic to delete</param>
		/// <param name="deleteReplies">(optional) Delete replies</param>
		/// <returns>True if removed sucessfully, defaults to false</returns>
		public bool DeleteTopic(Topic topic, bool deleteReplies = false)
		{
			List<Post> p = null;
			Post t = (from post in db.Posts
					  where post.PostId == topic.Id
					  select post).FirstOrDefault();

			if (t != null)
			{
				if (deleteReplies)
				{
					p = (from post in db.Posts
						 where post.ParentId == t.PostId
						 select post).ToList<Post>();
					db.Posts.DeleteAllOnSubmit(p);
				}

				db.Posts.DeleteOnSubmit(t);
				db.SubmitChanges();
				return true;
			}

			return false;
		}

		/// <summary>
		/// Applies the given tags to a topic. Creates new tags
		/// if they don't already exist and assigns the author to them
		/// </summary>
		/// <param name="tags">Tags to apply</param>
		/// <param name="topic">Applied topic</param>
		private void ApplyTags(List<Tag> tags, Topic topic)
		{
			// Created date
			DateTime dt = DateTime.UtcNow;
			List<PostTagAuthorRelationship> authors;
			List<PostTag> savetags;

			// Get tagnames only
			string[] search =
				tags.Select(tg => tg.Name).ToArray();

			// Find existing tags with those names
			string[] existing =
				(from t in db.PostTags
				 where search.Contains(t.TagName)
				 select t.TagName).ToArray();

			// Tags except those already in the database
			string[] newtags =
				search.Except(existing).ToArray();

			// We have new tags to save
			if (newtags.Length > 0)
			{
				savetags =
					(from tg in tags
					 where newtags.Contains(tg.Name)
					 select new PostTag
					 {
						 DisplayName = tg.DisplayName,
						 TagName = tg.Name,
						 Slug = tg.Slug,
						 LastModified = dt,
						 CreatedDate = dt,
						 BodyHtml = tg.Body,
						 BodyText = tg.Summary,
						 Status = (byte)tg.Status,
						 IsApproved = tg.Approved
					 }).ToList();

				db.PostTags.InsertAllOnSubmit(savetags);
				db.SubmitChanges();

				// Apply author relationships to the new tags
				authors =
					(from tg in savetags
					 select new PostTagAuthorRelationship
					 {
						 AuthorId = topic.CreatedBy.Id,
						 TagId = tg.TagId
					 }).ToList();

				db.PostTagAuthorRelationships.InsertAllOnSubmit(authors);
			}

			// Remove old tags
			List<PostTagRelationship> oldTags =
				(from pt in db.PostTagRelationships
				 join t in db.PostTags on pt.TagId equals t.TagId
				 join post in db.Posts on pt.PostId equals topic.Id

				 where !search.Contains(t.TagName)
				 select pt).ToList();

			db.PostTagRelationships.DeleteAllOnSubmit(oldTags);

			// Insert new tags
			List<PostTagRelationship> applyTags =
				(from t in db.PostTags
				 where search.Contains(t.TagName)
				 select new PostTagRelationship
				 {
					 TagId = t.TagId,
					 PostId = topic.Id
				 }).ToList();

			db.PostTagRelationships.InsertAllOnSubmit(applyTags);

			// Save everything
			db.SubmitChanges();
		}

		#endregion

		#region Queries

		/// <summary>
		/// Creates a deferred execution IQueryable to search topics
		/// </summary>
		/// <param name="posts">Initial search query</param>
		/// <returns>Topic IQueryable</returns>
		private IQueryable<Topic> TopicQuery(IQueryable<Post> posts,
			bool unapproved = false, TopicStatus[] status = null,
			bool newestFirst = true)
		{
			var query =
				from p in posts
				join au in db.PostAuthorRelationships on p.PostId equals au.PostId
				join a in db.Authors on au.AuthorId equals a.AuthorId
				join m in db.Members on a.MemberId equals m.MemberId into author
				from auth in author.DefaultIfEmpty() // Empty if anonymous post

				let postauthor = getCreator(a, auth)
				let tags = getTagsForTopic(p.PostId)

				select new { p, postauthor, tags };

			// Include unapproved topics?
			query = (unapproved) ?
				query.Where(r => r.p.IsApproved == false) :
				query.Where(r => r.p.IsApproved == true);

			// Any status other than "Open"?
			query = (status != null) ?
				query.Where(r => status.Contains((TopicStatus)r.p.Status)) :
				query.Where(r => r.p.Status == (byte)TopicStatus.Open);

			// Sort by new topics first?
			query = (newestFirst) ?
				query.OrderByDescending(r => r.p.CreatedDate) :
				query.OrderBy(r => r.p.CreatedDate);

			return from r in query
				   select new Topic
				   {
					   Id = r.p.PostId,
					   Name = r.p.Title,
					   CreatedBy = r.postauthor,
					   CreatedDate = r.p.CreatedDate,
					   LastModified = r.p.LastModified,
					   LastActivity = r.p.LastActivity,
					   Summary = r.p.BodyText,
					   Slug = r.p.Slug,
					   Tags = new LazyList<Tag>(r.tags),
					   ViewCount = r.p.ViewCount,
					   ReplyCount = r.p.ReplyCount,
					   Status = (TopicStatus)r.p.Status,
					   Threshold = (float)r.p.Threshold,
					   ContentHash = r.p.ContentHash
				   };
		}

		/// <summary>
		/// Creates a deferred execution IQueryable to search replies
		/// </summary>
		/// <param name="posts">Initial posts query</param>
		/// <param name="status">Status restriction array</param>
		/// <param name="newestFirst">Sort by new replies first</param>
		/// <returns>Reply IQueryable</returns>
		private IQueryable<Reply> ReplyQuery(IQueryable<Post> posts,
			bool unapproved, ReplyStatus[] status, bool newestFirst)
		{
			var query =
				from p in posts
				join au in db.PostAuthorRelationships on p.PostId equals au.PostId
				join a in db.Authors on au.AuthorId equals a.AuthorId
				join m in db.Members on a.MemberId equals m.MemberId into author
				from auth in author.DefaultIfEmpty() // Empty if anonymous post

				let postauthor = getCreator(a, auth)

				select new { p, postauthor };

			// Include unapproved replies?
			query = (unapproved) ?
				query.Where(r => r.p.IsApproved == false) :
				query.Where(r => r.p.IsApproved == true);

			// Any status other than "Open"?
			query = (status != null) ?
				query.Where(r => status.Contains((ReplyStatus)r.p.Status)) :
				query.Where(r => r.p.Status == (byte)ReplyStatus.Open);

			// Sort by new replies first?
			query = (newestFirst) ?
				query.OrderByDescending(r => r.p.CreatedDate) :
				query.OrderBy(r => r.p.CreatedDate);

			return from r in query
				   select new Reply
				   {
					   Id = r.p.PostId,
					   CreatedBy = r.postauthor,
					   CreatedDate = r.p.CreatedDate,
					   LastModified = r.p.LastModified,
					   Body = r.p.BodyHtml,
					   Status = (ReplyStatus)r.p.Status,
					   Threshold = (float)r.p.Threshold,
					   ContentHash = r.p.ContentHash
				   };

		}

		/// <summary>
		/// Helper finds the tags for a topic by id
		/// </summary>
		/// <param name="id">Topic id to search</param>
		/// <returns>IQueryable Tag</returns>
		private IQueryable<Tag> getTagsForTopic(int id)
		{
			return
				from t in db.PostTags
				join pt in db.PostTagRelationships on t.TagId equals pt.TagId
				where pt.PostId == id
				select new Tag
				{
					Id = t.TagId,
					Name = t.TagName,
					Slug = t.Slug,
					DisplayName = t.DisplayName,
					CreatedDate = t.CreatedDate,
					LastModified = t.LastModified
				};
		}

		#endregion

		#region Private methods

		/// <summary>
		/// Gets an array of content hashes sorted from the good threshold
		/// to bad.
		/// </summary>
		/// <param name="limit">Number of content hashes</param>
		/// <param name="good">Good threshold limit</param>
		/// <param name="bad">Bad threshold limit</param>
		/// <returns>Sorted array of content hashes from good to bad</returns>
		private string[] GetThresholds(int limit, float good, float bad)
		{
			var query =
				from p in db.Posts
				where p.Threshold > good || p.Threshold < bad
				orderby p.Threshold
				select p.ContentHash;

			return query.Take(limit).ToArray();
		}

		#endregion
	}
}

Pages: 1 2 3 4