using System;

using System.Collections.Generic;

using System.Linq;

​

​

class Program {

    static void Main(string[] args) {

​

        var notes = new List<Note> {

                new Note { Id = 1, AcctBalance = 10.0m, NoteContent = "Note1", PostDate = DateTime.Now.AddDays(-3), VisitId = 1 },

                new Note { Id = 2, AcctBalance = 10.0m, NoteContent = "Note2", PostDate = DateTime.Now.AddDays(-3), VisitId = 1 },

                new Note { Id = 3, AcctBalance = 10.0m, NoteContent = "Note3", PostDate = DateTime.Now.AddDays(-3), VisitId = 1 },

                new Note { Id = 4, AcctBalance = 10.0m, NoteContent = "Note3", PostDate = DateTime.Now.AddDays(-2), VisitId = 2 },

                new Note { Id = 5, AcctBalance = 10.0m, NoteContent = "Note3", PostDate = DateTime.Now.AddDays(-1), VisitId = 3 }

            };

​

        var systemNotes = new List<SystemNote> {

               new SystemNote{ Id = 1, FacilityId = 1, NoteContent = "SystemNote1", NoteDate = DateTime.Now.AddDays(-3), VisitId = 1 },

               new SystemNote{ Id = 2, FacilityId = 1, NoteContent = "SystemNote2", NoteDate = DateTime.Now.AddDays(-3), VisitId = 1 },

               new SystemNote{ Id = 3, FacilityId = 1, NoteContent = "SystemNote3", NoteDate = DateTime.Now.AddDays(-3), VisitId = 1 },

               new SystemNote{ Id = 4, FacilityId = 1, NoteContent = "SystemNote3", NoteDate = DateTime.Now.AddDays(-2), VisitId = 2 },

               new SystemNote{ Id = 5, FacilityId = 1, NoteContent = "SystemNote3", NoteDate = DateTime.Now.AddDays(-1), VisitId = 3 }

            };

​

        var query = from note in notes//get Notes DbSet from the context

                    join systemNote in systemNotes //get SystemNotes DbSet from the context

                        on note.VisitId equals systemNote.VisitId

                    orderby note.PostDate descending

                    select new {

                        NoteId = note.Id,

                        note.NoteContent,

                        note.PostDate,

                        note.VisitId,

                        SystemNoteId = systemNote.Id,

                        SystemNoteContent = systemNote.NoteContent,

                        systemNote.NoteDate,

                        systemNote.FacilityId

                    };

​

        var noteList = query.ToList();

​

        foreach (var item in noteList) {

            Console.WriteLine($"NoteId :{item.NoteId} Date:{item.NoteDate} Content: '{item.NoteContent}' - SystemNotId :{item.SystemNoteId} Content : '{item.SystemNoteContent}'");

        }

    }

}

​

public partial class Note {

    public int Id { get; set; }

    public int VisitId { get; set; }

    public string NoteContent { get; set; }

    public DateTime PostDate { get; set; }

    public decimal AcctBalance { get; set; }

}

​

public partial class SystemNote {

    public int Id { get; set; }

    public int VisitId { get; set; }

    public int FacilityId { get; set; }

    public string NoteContent { get; set; }

    public DateTime NoteDate { get; set; }

}