How to create a list by narrowing down another list in c# and linq


I'm working in mvc and I have a unique situation.

I have two tables.


A user can create a new message and then add more users to the message. Currently, the user who creates the message is not saved as a MessageUser, but instead just at the message level.

However, in other situations this same user might be added as a MessageUser to another message.

I'm trying to display all messages that pulls from two lists:

  1. a list of messages where the user created the message.
  2. a list of messages that a user is an added user in the MessageUser table that lies below the Message table.

My attempts so far have fallen short.

    var userId = User.Identity.GetUserId();

    //Pulls all messages where the user created them.
    List<Message> MessageList_1 = db.Message.Where(u => u.UserId == userId).ToList();

    //This doesn't work at all, but it's what I'm hoping to do in English.
    //I want a list of just the messages where a messageuser is associated to the messageid.
    List<Message> MessageList2 = db.MessageUser.Where(u => u.UserId == userId).ToList();

    //Another failed attempt
    List<Message> MessageList3 = db.Message.Where(u => u.MessageId = MessageUserList.)

    //Original that just pulls messages the user created.  My hope is to combine the two lists and display them on the same view.
    var message = db.Message.Include(o => o.User).Where(u => u.UserId == userId);
    return View(await Message.ToListAsync());

Hope this is enough.


If there is a foreign key relationship set up properly then you can do something like this )

List<Message> MessageList_2 = db.MessageUser.Where(u => u.UserId == userId).Select(m=> m.Message).ToList();

MessageList_1 should contain your answers. You may need to look into selecting distinct messages by id as well. If you're foreign key properties aren't correct, then this won't work and you'll and up with a lot of other problems as well.

Assuming you're using Entity Framework and your database has the correct foreign-keys set up, then you don't need mess around with complicated Linq queries, you can use Navigation properties on your Entity objects directly:

Assuming your schema is:

Users( UserId, ... )
Messages( MessageId, SenderUserId, ... )
MessageUsers( MessageId, UserId )

And your entity classes resemble:

class User {
    public ICollection<Message> MessagesSent { get; } // bound to Messages.SenderUserId

    public ICollection<Message> MessagesReceived { get; } // bound to MessageUsers.MessageId+UserId as a M:M relationship

class Message {
    public User Sender { get; }
    public ICollection<User> Recipients { get; } // bound to MessageUsers.MessageId+UserId as a M:M relationship

Then you can simply do:

IEnumerable<Message> messagesSentByUser = user.MessagesSent;

IEnumerable<Message> messagesReceivedByUser = user.MessagesReceived;

If you have more properties in your MessageUser entity such that EF does not coalesce it into a simple M:M relationship (but instead gives you a M:1:M relationship) then you can do:

IEnumerable<Message> messagesSentByUser = user.MessagesSent;

IEnumerable<Message> messagesReceivedByUser = user.MessageUsers.Select( mu => mu.Message );
By : Dai

If I am reading this correctly, Message would contain a List<MessageUser>.

If that's the case then this should get you close:

var List<Message> messages = 
    ( from m 
      in db.Messages 
          m.UserID == userid || m.MessageUsers.Any(mu => mu.UserID == userID) 
      select m).ToList();
By : Sam Axe

This video can help you solving your question :)
By: admin