LINQ to SQL query problem (can't get the correct value)

I'm trying to create a mailing system (something similar to gmail), but that will serve as a private messaging system. Unfortunately (for me), I got stuck at writing a query that will display the name of the recipient of the message in the listview.

Here is the database schema for mailing system:

I believe that the schema is pretty much clear, but let me briefly explain each table. MailState table has data about mail location (Inbox, Sent, Trash) of the each mail of some user. MailStatus has only two states: "Read" or "New". It is obvious what mail table has.

What I've been trying for the last couple of hours is to display messages in the "Sent" mailbox for the specific user and I've almost made it. The problem is that I can't get my query to display correct User.DisplayName whenever I check whose mailbox this is (determined by the id of the logged in user).

Here is the code I've been using:

var _messages = from m in db.MailState
                        select m;

        if (mailboxType == "Inbox")
        {
           var mailbox = from m in _messages
                         where m.idUser == idUser
                         where m.Mail.idSender != idUser
                         where m.MailLocation.Name == mailboxType
                         select new { m.idMail, m.Mail.User.DisplayName, m.Mail.DateSent, m.Mail.Subject };

           lvMailbox.DataSource = mailbox;
        }
        else if (mailboxType == "Sent")
        {
            var mailbox = from m in db.MailState
                          where m.MailLocation.Name == mailboxType  
                          select new { m.Mail.idSender, m.idMail, m.Mail.DateSent, m.Mail.Subject, m.User.DisplayName };

            //mailbox = mailbox.Where(m => m.idSender == idUser);

            lvMailbox.DataSource = mailbox;
        }
        else
        {
            //NOTHING YET
        }          

        lvMailbox.DataBind();

Inbox is working correctly, but "Sent" mailbox is displaying the name of the logged in user instead of displaying recipient's 开发者_运维问答name. What bothers me the most is that it displays everything correctly if I'm my query doesn't contain where clause for the logged in user. That's why I need some help.

P.S. Any other suggestions (regarding code, database, etc) are welcome!

In situations where there are two paths to the "User" table queries can get very confusing and lead to results like you have with the:

"Sent" mailbox is displaying the name of the logged in user instead of displaying recipient's name.

One of the things that I try to do before doing multiple queries is to try flatten out the query's source data to make my queries more succinct. Something like this:

var messages =
    from ms in db.MailState
    select new
    {
        idMail = ms.idMail,
        Subject = ms.Mail.Subject,
        DateSent = ms.Mail.DateSent,
        Location = ms.MailLocation.Name,
        Sender_idUser = ms.Mail.idSender,
        Sender_DisplayName = ms.Mail.User.DisplayName,
        Recipient_idUser = ms.idUser,
        Recipient_DisplayName = ms.User.DisplayName,
    };

Now, given this, your existing queries would look like this:

/* Inbox */
var mailbox =
    from m in messages
    where m.Recipient_idUser == idUser
    where m.Sender_idUser != idUser
    where m.Location == mailboxType
    select new
    {
        m.idMail,
        m.Sender_DisplayName,
        m.DateSent,
        m.Subject
    };

/* Sent */
var mailbox =
    from m in messages
    where m.Location == mailboxType  
    select new
    {
        m.Sender_idUser,
        m.idMail,
        m.DateSent,
        m.Subject,
        m.Recipient_DisplayName
    };

These should be a little easier to read and reason about.

So from here I tried to imagine the records required to represent the sending and receiving of email.

Let's say we have three users Anne, Bill, and Ben. If Anne sends an email to both Bill and Ben my messages query above would have to return three records - one for the "Sent Items" in Anne's mailbox & one each for the Bill & Ben's "Inbox".

Field                 | Anne         | Bill       | Ben        |
----------------------+--------------+------------+------------+
idMail                | 1            | 1          | 1          |
Subject               | "Hi"         | "Hi"       | "Hi"       |
DateSent              | 2011/08/01   | 2011/08/01 | 2011/08/01 |
Location              | "Sent Items" | "Inbox"    | "Inbox"    |
Sender_idUser         | 1            | 1          | 1          |
Sender_DisplayName    | "Anne"       | "Anne"     | "Anne"     |
Recipient_idUser      | 1            | 2          | 3          |
Recipient_DisplayName | "Anne"       | "Bill"     | "Ben"      |

What this tells me is that the data available from the current database structure doesn't have the information required to determine who the recipients are for an item in a user's "Sent Items" folder. In fact, it highlights a naming issue with the "Recipient_*" fields.

Here's a better messages query to start off the queries:

var messages =
    from m in db.Mail
    select new
    {
        idMail = m.idMail,
        Subject = m.Subject,
        DateSent = m.DateSent,
        Sender = new
        {
            idUser = m.idSender,
            DisplayName = m.User.DisplayName,
        },
        Locations =
            from ms in db.MailState
            where ms.idMail == m.idMail
            select new
            {
                Location = ms.MailLocation.Name,
                idUser = ms.idUser,
                DisplayName = ms.User.DisplayName,
            },
        Recipients =
            from ms in db.MailState
            where ms.idMail == m.idMail
            where ms.idUser != m.idSender
            select new
            {
                idUser = ms.idUser,
                DisplayName = ms.User.DisplayName,
            },
    };

Using this as the base query, your existing queries can now look like this:

/* Inbox */
var mailbox =
    from m in messages
    from r in m.Recipients
    where r.idUser == idUser
    select new
    {
        m.idMail,
        r.DisplayName,
        m.DateSent,
        m.Subject
    };

/* Sent */
var mailbox =
    from m in messages
    where m.Sender.idUser == idUser 
    select new
    {
        m.Sender.idUser,
        m.idMail,
        m.DateSent,
        m.Subject,
        Recipients =
            from r in m.Recipients
            select r.DisplayName,
    };

These queries are quite a bit simpler and should be very easy to reason that they are correct or not.

There's no need to filter on the MailLocation field either as this is information is now captured in the query itself.

The only thing that you'll need to do is flatten the Recipients field into a single string for data bindings as this is a collection of recipient display names and not just a single string.

I hope that this helps.

Here is one approach, if you're able to change the database schema.

Add a new field to your Mail table, idRecipient, that identifies the message recipient.

(Your MailState table represents data for a specific mail message for a specific user. The recipient is a property of the message (Mail) but stored as a property of the mail state. If the recipient is able to delete the message, that piece of information would be removed from your schema altogether.)

Once you have this field in place, the query will become much simpler.

var mailbox = from m in db.MailState
  where m.MailLocation.Name == mailboxType 
  where m.idUser == idUser
  select new { m.Mail.idRecipient,
               m.idMail,
               m.Mail.DateSent,
               m.Mail.Subject,
               m.User.DisplayName /*or recipient display name*/ };

Would an approach like this work for you?

(Edit in response to comment)

Given the new information that there is a many-to-one relationship between the sender and recipient, one way to approach this is with a subquery.

var mailbox = from m in db.MailState
  where m.MailLocation.Name == mailboxType 
  where m.idUser == idUser
  select new { 
              Recipients = from z in db.MailState
                   where z.idMail == m.idMail // outer join on message ID
                   where z.idLocation.Id != mailboxType // exclude sender
                   select z.User,
               m.idMail,
               m.Mail.DateSent,
               m.Mail.Subject,
               m.User.DisplayName
   };

Then mailbox.Recipients is an IEnumerable<User> (depending on your model) containing all recipients of the message.

(Note that your requirement to store the recipients in MailState requires that records never be deleted from MailState, since that is the only repository for recipient information.)