ASP.NET 3.5 Social Networking : Messaging (part 2)

2/16/2013 8:21:57 PM

3. Solution

Now let's take a look at our solution.

3.1 Implementing the database

First; let's take a look at what tables are needed:


A message will primarily be made up of the subject and its body. In addition to that we will need to know what type of message we are sending so that we can do some more fancy things in the UI down the road. In addition to this, we are going to maintain who owns the message or created the message at this level.

There aren't really any major complexities to note here other than the fact that the Body is made up of a varchar(MAX) data type. If you feel this is too large for your system, feel free to make it anything you are comfortable with. 


Message Types allows us to assign a type to our messages. This is purely a lookup table that will allow us to know what the types are during queries. We will keep a list of enums in the code to make the lookups easier from that end.


A message recipient is simply the receiving party to the message. But as we try to minimize the data that we manage in our system, the message recipient is also a very important part of the message. In our case, it is the receiving party as well as all the things that the receiving party does with their subscription of that message. We will use this subscription to denote which folder the receiver is keeping the message in, and whether the receiver has read the message or not. Also, if the receiver chooses to delete the message, he/she can just delete the subscription to a message (unless they are the last subscription, in which case we will delete the message as well).

The SQL for this subscription is actually quite straightforward. It tracks a relationship to the message, a relationship to the receiver, which folder the subscription is currently in, and the status of the message for this receiver.


The message recipient type allows us to track the receiver of this message addressed in the TO, CC, or BCC fields. Initially, our interface will only have a TO field. I figure that we should add this bit of metadata though just in case we want to expand our capabilities down the road! This is another example of a lookup table that we might need to use in the SQL queries. In our case, we will have an enum defined that maintains this lookup for us on the code side.


MessageStatusTypes allows us to track what a recipient is doing with his/her copy of the message, whether they have read the message, replied to the message, and so on. This is primarily so that we can change the UI to reflect its status to the recipient. However, we could also create a dashboard down the road for the senders of the messages to know whether their message was read or not and by whom (think of all the big brother things one could do...but probably should not do!).


MessageFolders in our first round of implementation will simply hold copies of new messages in the Inbox and copies of sent messages in the Sent folder. We will also have a trash folder and a spam folder. That said, I always wanted to build a system with the future in mind if it doesn't require a lot of extra work, and so we have also baked in the concept of a user being able to create and manage his/her own folders.

Therefore, rather than just see the MessageFolders table as another lookup table, you will see that there is an IsSystem flag to denote which folders are to be seen system-wide. And you will see an AccountID column for custom folders so that we know who owns which folders.

Creating the relationships

Once all the tables are created, we can create the relationships.

For this set of tables, we have relationships between the following tables:

  • Messages and MessageRecipients

  • Messages and Accounts

  • Messages and MessageTypes

  • MessageRecipients and MessageRecipientTypes

  • MessageRecipients and MessageFolders

  • MessageRecipients and MessageStatusTypes

3.2 Setting up the data access layer

The data access layer in this case is very straightforward. Open up your Fisharoo.dbml file and drag all of your new messaging oriented tables.

Remember that we are not letting LINQ track our relationships at this point (to avoid built in concurrency management issues). So be sure to remove all the relationships that pop up as you drag your tables on to the design surface. Once you save this, you should now have a list of new domain objects in your arsenal.

Building repositories

With these new tables come some additional repositories. We will create the following repositories.

  • MessageRepository

  • MessageRecipientRepository

  • MessageFolderRepository

 We will create a method for selecting a single entity by ID, a group of entities by their parents, saving entities, and deleting entities.

Having said that, there are a couple of methods that have something special in the set of repositories. As we are using message subscriptions, we don't necessarily want to delete recipients haphazardly. We may want to delete a recipient, and if that recipient is the last recipient with a subscription to a message, we may also want to delete the message. On the other end of the spectrum, if we do delete a message, we may also want to remove all the recipient subscriptions.

In addition to these different ways of deleting data, we will also run into a scenario where selecting a single entity from our repositories won't be quite good enough. So in this case, we have created an aggregate class that will allow us to select several entities out at once for use in our inbox scenarios.


When we think of a standard inbox, we know that we need to see the messages that we have, who sent them, when they were sent, and at least the subject of their message. In this case, we have discussed two different entities here. When we think about the fact that we also need to know who they were sent to, we have added a third entity. While we could run three separate queries for this data, it would be better for us to run one query (as we would have done in the old days) and return the data that we need in one shot.

Having said that, we know that LINQ can only return one entity or a list of single entities. What do we do? In this case, we need to create an aggregate. This is a class that contains other entities. We will therefore create a MessageWithRecipient class that will contain the sender's account info, the message, and the recipient. This should provide us with enough data to represent messages in our inbox view later.

Before we write any queries, we first need to create the aggregate.

namespace Fisharoo.FisharooCore.Core.Domain
public class MessageWithRecipient
public Account Sender { get; set; }
public Message Message { get; set; }
public MessageRecipient MessageRecipient{ get; set; }

With this aggregate in place we can now turn our attention to the repository that will get all this data for us.

public List<MessageWithRecipient> GetMessagesByAccountID(Int32 AccountID, Int32 PageNumber, MessageFolders Folder)
List<MessageWithRecipient> result = new List<MessageWithRecipient>();
using(FisharooDataContext dc = conn.GetContext())
IEnumerable<MessageWithRecipient> messages =
(from r in dc.MessageRecipients
join m in dc.Messages on r.MessageID equals
join a in dc.Accounts on m.SentByAccountID equals
where r.AccountID == AccountID && r.MessageFolderID ==
orderby m.CreateDate descending
select new MessageWithRecipient()
Sender = a,
Message = m,
MessageRecipient = r
}).Skip((PageNumber - 1)*10).Take(10);
result = messages.ToList();
return result;


This is a fun method! This method involves selecting a list of our MessageWithRecipient aggregate objects. The LINQ query is joining all the tables that we need and selecting a new instance of the MessageWithRecipient aggregate, which is then populated with the three classes that we need in the aggregate. Additionally, we have introduced some paging logic with the .Skip and .Take methods to produce a subset of the MessageWithRecipient objects.

In addition to the selection method above, we also need to discuss the delete method for this repository. As we have data holding a subscription to our message data, it is important that we first remove all the subscriptions prior to removing the message itself.

public void DeleteMessage(Message message)
using(FisharooDataContext dc = conn.GetContext())
IEnumerable<MessageRecipient> recipients = dc.MessageRecipients.Where(mr => mr.MessageID == message.MessageID);
foreach(MessageRecipient mr in recipients)


This is easily accomplished by opening a new instance of the data context. We then get a list of recipients for this message. Once we have the list, we iterate over each recipient adding it to the context for deletion. Finally, we call SubmitChanges() to delete all the subscriptions effectively. We then delete the message as we normally would.


The message recipient repository is considerably easier. It simply has an altered delete statement to adjust for the fact that if we delete the last subscription to a message, it will amount to deleting the message.

public void DeleteMessageRecipient(MessageRecipient messageRecipient)
using(FisharooDataContext dc = conn.GetContext())
//if the last recipient was deleted
//...also delete the message
int RemainingRecipientCount =
dc.MessageRecipients.Where(mr => mr.MessageID == messageRecipient.MessageID).Count();
if (RemainingRecipientCount == 0)
dc.Messages.Where(m => m.MessageID == messageRecipient.MessageID).FirstOrDefault());


In this method, we delete the recipient in question. We then get a count of the remaining recipients for the message , which has the last recipient removed. If that count is zero, then there are no more recipients remaining for that message. In that case we perform a delete on that message and remove it from the system as well.
Top 10
Review : Sigma 24mm f/1.4 DG HSM Art
Review : Canon EF11-24mm f/4L USM
Review : Creative Sound Blaster Roar 2
Review : Philips Fidelio M2L
Review : Alienware 17 - Dell's Alienware laptops
Review Smartwatch : Wellograph
Review : Xiaomi Redmi 2
Extending LINQ to Objects : Writing a Single Element Operator (part 2) - Building the RandomElement Operator
Extending LINQ to Objects : Writing a Single Element Operator (part 1) - Building Our Own Last Operator
3 Tips for Maintaining Your Cell Phone Battery (part 2) - Discharge Smart, Use Smart
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone