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:
Messages
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.
MessageTypes
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.
MessageRecipients
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.
MessageRecipientTypes
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
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
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.
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.
MessageRepository
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.
//Core/Domain/MessageWithRecipient.cs
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.
//Core/Data&;Access/Impl/MessageRepository.cs
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
m.MessageID
join a in dc.Accounts on m.SentByAccountID equals
a.AccountID
where r.AccountID == AccountID && r.MessageFolderID ==
(int)Folder
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.
//Core/DataAccess/Impl/MessageRepository.cs
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)
{
dc.MessageRecipients.DeleteOnSubmit(mr);
}
dc.SubmitChanges();
dc.Messages.DeleteOnSubmit(message);
dc.SubmitChanges();
}
}
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.
MessageRecipientRepository
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.
//Core/DataAccess/Impl/MessageRecipientRepository.cs
public void DeleteMessageRecipient(MessageRecipient messageRecipient)
{
using(FisharooDataContext dc = conn.GetContext())
{
dc.MessageRecipients.Attach(messageRecipient,true);
dc.MessageRecipients.DeleteOnSubmit(messageRecipient);
dc.SubmitChanges();
//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.DeleteOnSubmit(
dc.Messages.Where(m => m.MessageID == messageRecipient.MessageID).FirstOrDefault());
dc.SubmitChanges();
}
}
}
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.
