How to return two records linked to one id Entity Framework/Razor

Question

Can someone give a suggestion or assist please, I have a model called Message which contains my messages with an id, and also a model messageDocument which contains a foreign key linking the document to a message id.

Now 1 message can have multiple documents, so when retrieving the data and linking message id we have duplicate messages when we have multiple files. My question is how to eliminate the duplicate messages and return one message with multiple files? I'm using EF Core

Thank you

This is my code:

public class MessageModel
{
    int id { get; set; }
    string subject { get; set; }
    string message { get; set; }
    Datetime created { get; set; }
}

public class DocumentModel
{
    int id { get; set; }
    int messageId { get; set; }
    string fileName { get; set; }
    byte[] content { get; set; }
    Datetime created { get; set; }
}

Example data

MessagesModel

{ id = 1, subject = 'my message1', message = 'a long message', created = '2021-03-23 16:16:54.0000000' }  
{ id = 2, subject = 'my message 2', message = 'a short message', created = '2021-03-23 16:18:54.0000000' }

DocumentModel

{ id = 1, messageId = '1', filename = 'testing.txt', content = 'AlongBytearray', created ='2021-03-23 16:16:54.0000000' }

{ id = 2, messageId = '2', filename = 'testing1.txt', content = 'AlongBytearray', created ='2021-03-23 16:17:54.0000000' }

{ id = 3, messageId = '2', filename = 'testing2.txt', content = 'AlongBytearray', created ='2021-03-23 16:18:54.0000000' }

Using LINQ to retrieve the data:

var Messages = (from m in project.MessagesModel
                from md in project.messageDocument.Where(x => x.cri_message_id == m.id)
               select new ViewMessageModel
                          {
                               id = m.id,
                               subject = m.subject,
                               message = m.message,
                               create_date = m.created,
                               filename = md.fileName,
                               content = md.content
                          });

Then returning Messages model to the view:

@using project.Models;
@model List<ViewMessageModel>

@foreach (var item in Model)
{
       <div>item.id<div>
       <div>item.subject<div>
       <div>item.message<div>
       <div>item.createdate<div>
       <div>item.filename<div>
       
}

My result:

id = 1,subject='my message1',message='a long message',created ='2021-03-23 16:16:54.0000000',filename='testing.txt'  
id = 2,subject='my message 2',message='a short message',created='2021-03-23 16:18:54.0000000',filename='testing1.txt'  
id = 2,subject='my message 2',message='a short message',created='2021-03-23 16:18:54.0000000',filename='testing2.txt'

Now I don't want message id 2 to be repeated twice in my view, I want it to display only once but with two file names associated to it displayed as well. Thank you in advance

Answer 1

First of all you have to know what you want in your result. So if you have A 1->N B relations in your data then it is logically not possible to get B's without repeating the id of A. Normally i would recommend you that you use the documentId instead. Then you have unique Id's.

var Messages = (from m in project.MessagesModel
                from md in project.messageDocument.Where(x => x.cri_message_id == m.id)
               select new ViewMessageModel
                          {
                               id = md.id,
                               subject = m.subject,
                               message = m.message,
                               create_date = m.created,
                               filename = md.fileName,
                               content = md.content
                          });

But i think you don't want to repeat the message, too. For this scenario you have to nest the documents into the messages or get two separate lists. Messages and Documents. I would prefer the first scenario, sth like this:

@foreach (var message in Model)
{
       <div>message.id<div>
       <div>message.subject<div>
       <div>message.message<div>
       @foreach (var doc in message.Documents)
       {
           <div>doc.createdate<div>
           <div>doc.filename<div>
       }
}