C# linq expression not pulling the data correctly

Question

I am trying to query two tables and need to pull related records from both the tables. I am using enityframeworkcore 3 One is system versioned table and the other is history table. My resultset is containing data only from history table and not system-versioned table. Could somebody tell me what is wrong with my statement . I am ensured that the personid in the systemversion table matches the history table.

Query

  var personNotes = (from pn in _context.PersonNotes
                              join pnh in _context.PersonNotesHistory on pn.PersonId equals pnh.PersonId
                              select pn);
            return personNotes;

Answer 1

You need to specify the column names you want to return in the result:

var personNotes = (from pn in _context.PersonNotes
                              join pnh in _context.PersonNotesHistory on pn.PersonId equals pnh.PersonId
                              select new {
                                           data1 = pn.column1,
                                           data2 = pn.column2,
                                           data3 = pn.column3,
                                           data4 = pnh.column1,
                                           data5 = pnh.column2,
                                           data6 = pnh.column3,


                                          }).ToList();
            return personNotes;

Just change the column1, column2, column3 with column names you want to retrieve from the database.

Answer 2

As an alternative to the first answer you may use this syntax:

var personNotes = _context.PersonNotes
    .Join(_context.PersonNotesHistory, pn => pn.PersonId, pnh => pnh.PersonId, (pn, pnh) => new
    {
        pn.Note,
        pn.AuthorID,
        pn.CreatedBy,
        pnh.Note,
        pnh.AuthorID,
        pnh.CreatedBy
    })
    .ToList();