How to get values of table from another table in LINQ

Question

pretty sure its the lack of sleep and the fact i've run out of coffee but i'm really struggling to get this in my head.

So I have 2 tables, Conversations which my model is:

public class Conversation
{
    [Key]
    public int ConversationId { get; set; }
    public ICollection<Message> Messages { get; set; } = new Collection<Message>();
}

and Messages :

public class Message
{
    [Key]
    public int MessageId { get; set; }

    public string MessageContent { get; set; }

    public string Sender { get; set; }

    public string Receiver { get; set; }

    public DateTime DateCreated { get; set; }
}

now in my controller method I am trying to pull in all messages if they contain the passed in users in the method

e.g

    [Route("GetAllUserConversations")]
    [HttpGet]
    public async Task<List<Conversation>> GetAllUserConversations(string receiverEmail, string senderEmail)
    {
        var conversation = _dbContext.Conversations
        .Include(m => m.Messages)
        .SingleOrDefault(x => x.Messages.Where(x => x.Receiver == receiverEmail) && x.Messages.Where(y => y.Sender == senderEmail);

        return conversation;
    }

so if receiverEmail AND SenderEmail are present in the message then I want to pull in that message.

an example of the message I am trying to get is:

thanks!

I want all messages that have the Sender and Receiver properties equal to the passed in arguments — dros
– dros, Commented Jun 27, 2020 at 22:51
try if this query is translated to sql correctly : var conversations = _dbContext.Conversations.Include(m => m.Messages).Where(x => x.Messages.Any(x => x.Receiver == receiverEmail && x.Sender == senderEmail)).ToListAsync(); — Mohammed Sajid
– Mohammed Sajid, Commented Jun 27, 2020 at 22:54
hmmm that doesnt seem to work, i have updated my question with a screen shot of the message I am trying to pull in — dros
– dros, Commented Jun 27, 2020 at 23:09

Noldor · Accepted Answer · 2020-06-28 03:20:57Z

I think what you're looking for is this;

var conversation = _dbContext.Conversations
                  .Include(m => m.Messages.Where(x => x.Receiver == receiverEmail && x => x.Sender == senderEmail))                  
                  .SingleOrDefault();

But I think you should consider your data model over again. You will experience some difficulties such as this one you encountered a lot if you use this model. It would be better with this for instance:

- User
--- Id
--- Email
--- Name    

- Conversation
--- Id
--- Name
--- List<ConversationMember> Members    
--- List<Message> Messages 

- ConversationMember
--- Id
--- ConversationId
--- UserId    

- Message
--- Id
--- ConversationId
--- SenderMemberId

var conversation = _dbContext.Conversations
                  .Include(cnv => cnv.Members)                  
                  .Include(cnv => cnv.Messages)
                  .SingleOrDefault(cnv.Members.Any(mmb => mmb.UserId == userId1 || mmb.UserId == userId2));

Collectives™ on Stack Overflow

How to get values of table from another table in LINQ

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related