Many to Many Linq Query

Question

I'm struggling with a linq query.

I have the following models:

public class Message
{
    public Message()
    {
        Date = DateTime.Now;
        MessageRecipients = new List<MessageRecipient>();
    }
    [Key]
    public int MessageID { get; set; }

    public string Body { get; set; }

    public DateTime Date { get; set; }

    public List<MessageRecipient> MessageRecipients { get; set; }

}

public class MessageRecipient
{
    public MessageRecipient()
    {
        HasBeenRead = false;
    }
    [Key]
    public int MessageRecipientID { get; set; }

    public Message Message { get; set; }
    public string User { get; set; }
    public bool HasBeenRead { get; set; }

}

A message goes out to multiple message recipients. I'm trying to write a query that will get the unread messages for a particular user.

I can get the messages for a user, but I can't seem to extend the query to get the unread ones:

public List<Message> GetMessageForUser(ApplicationUser user)
{
var messages = dbContext.Messages
    .Where(x => x.MessageRecipients.Select(y => y.User).Contains(user.UserName))                    
    .OrderByDescending(p => p.Date);
}

I'm not using a foreign key link between ApplicationUser and MessageRecipients because of a company decision - the user object isn't to have any dependencies on other objects so we query just by username, rather than using a navigation property.

Answer 1

var messages = dbContext.MessageRecipients
                        .Where(y => y.User ==.user.UserName && !y.HasBeenRead)
                        .Select(g=>g.Message)
                        .ToList()

Answer 2

You could use Any extension and verify user is one of the recipient and message is unread.

var messages = dbContext.Messages
    .Where(x => x.MessageRecipients.Any(y => y.User ==.user.UserName && !y.HasBeenRead ))                    
    .OrderByDescending(p => p.Date);