LinQ Join Query Into a Multidimensional Model

A customer can have multiple accounts, each account has multiple notes. On my Customer View, I want to see all the notes from all the accounts.

I have the following Objects:

 
public class Note
    {
        public int Id { get; set; }

        [Display(Name = "Date")]
        [DisplayFormat(DataFormatString = "{0:d}")]
        [DataType(DataType.Date)]
        [Required]
        public DateTime AsOfDate { get; set; }
        [Required]
        public int AccountId { get; set; }
        public Account Account { get; set; }
        [Required]
        public string Description { get; set; }
        [Display(Name = "Is System")]
        [Required]
        public bool IsSystem { get; set; }
    }
 
  public class Account
    {
        [Display(Name = "Account ID")]
        public int Id { get; set; }

        [ForeignKey("CustomerId")]
        [Display(Name = "Customer")]
        [Required]
        public int CustomerId { get; set; }
        public Customer Customer { get; set; }
        [Display(Name = "Account Name")]
        [Required]
        public string AccountName { get; set; }

        [Display(Name = "Account Date")]
        [DisplayFormat(DataFormatString = "{0:d}")]
        [DataType(DataType.Date)]
        [Required]
        public DateTime? AsOfDate { get; set; }

        [NotMapped]
        public List<Note> Notes { get; set; }
        [NotMapped]
        public List<Service> Services { get; set; }
    }
 
  public class Customer
    {
        [Display(Name = "Customer ID")]
        public int Id { get; set; }

        [Display(Name = "First Name")]
        [Required]
        public string FirstName { get; set; }
        [Display(Name = "Last Name")]
        [Required]
        public string LastName { get; set; }
        [Display(Name = "Middle Initial")]
        public string MiddleInit { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public string Country { get; set; }
        [Required]
        public string Email { get; set; }
        public string Phone { get; set; }

        [NotMapped]
        
        [Display(Name = "Customer Name")]
        public string Name
        {
            get
            {
               return FirstName + " " + LastName;
            }
        }
        [NotMapped]
        public List<Account> Accounts { get; set; }
        [NotMapped]
        public List<Note> AccountNotes { get; set; }
    }

The [Not Mapped] properties tell Entity Framework that they are not part of the database. My views have multiple tables and dashboards and that is the reason for many lists within a model.

 
 public async Task<Customer> GetCustomerPerIdAsync(int? id)
        {
            if(id==null)
            {
                return null;
            }
            var customer = await _context.Customer.FirstOrDefaultAsync(m => m.Id == id);

            if (customer == null)
            {
                return null;
            }

            var accounts = (from s in _context.Account where s.CustomerId == id select s).ToList();
            customer.Accounts = accounts;

            var notes = (from a in _context.Account 
                         join n in _context.Note on a.Id equals n.AccountId 
                         where a.CustomerId == id && n.IsSystem==false
                         orderby n.AsOfDate descending
                         select new Note {  AccountId=n.AccountId, 
                                            Account=a,
                                            Id=n.Id, 
                                            AsOfDate= n.AsOfDate,
                                            Description = n.Description, 
                                            IsSystem= n.IsSystem 
                                            }
                         ).ToList();
            customer.AccountNotes = notes;


            
            return customer;
        }

For a single customer ID we are populating all the lists and we are bringing the notes with its corresponding Account. All the account information for that note will be stored for the view to display the account name among any other information necessary.

The View:

 
@model KryptoService.Models.Customer

@{
    ViewData["Title"] = "Customer Details";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h1>@Model.Name</h1>
<div class="row">
    <div class="col-md-6">
        <h4>Customer</h4>
        <hr />
        <dl class="row">
            <dt class="col-sm-3">
                @Html.DisplayNameFor(model => model.FirstName)
            </dt>
            <dd class="col-sm-9">
                @Html.DisplayFor(model => model.FirstName)
            </dd>
            <dt class="col-sm-3">
                @Html.DisplayNameFor(model => model.LastName)
            </dt>
            <dd class="col-sm-9">
                @Html.DisplayFor(model => model.LastName)
            </dd>
            <dt class="col-sm-3">
                @Html.DisplayNameFor(model => model.MiddleInit)
            </dt>
            <dd class="col-sm-9">
                @Html.DisplayFor(model => model.MiddleInit)
            </dd>
            <dt class="col-sm-3">
                @Html.DisplayNameFor(model => model.Address)
            </dt>
            <dd class="col-sm-9">
                @Html.DisplayFor(model => model.Address)
            </dd>
            <dt class="col-sm-3">
                @Html.DisplayNameFor(model => model.City)
            </dt>
            <dd class="col-sm-9">
                @Html.DisplayFor(model => model.City)
            </dd>
            <dt class="col-sm-3">
                @Html.DisplayNameFor(model => model.State)
            </dt>
            <dd class="col-sm-9">
                @Html.DisplayFor(model => model.State)
            </dd>
            <dt class="col-sm-3">
                @Html.DisplayNameFor(model => model.Zip)
            </dt>
            <dd class="col-sm-9">
                @Html.DisplayFor(model => model.Zip)
            </dd>
            <dt class="col-sm-3">
                @Html.DisplayNameFor(model => model.Country)
            </dt>
            <dd class="col-sm-9">
                @Html.DisplayFor(model => model.Country)
            </dd>
            <dt class="col-sm-3">
                @Html.DisplayNameFor(model => model.Email)
            </dt>
            <dd class="col-sm-9">
                @Html.DisplayFor(model => model.Email)
            </dd>
            <dt class="col-sm-3">
                @Html.DisplayNameFor(model => model.Phone)
            </dt>
            <dd class="col-sm-9">
                @Html.DisplayFor(model => model.Phone)
            </dd>
        </dl>
    </div>
    <div class="col-md-6">
        <div class="row col-md-12">
            <div class="col-md-8">
                <h4>Accounts</h4>
            </div>
            <div class="col-md-4">
                <a asp-action="Create" asp-controller="Accounts" asp-route-customerId="@Model.Id" class="btn btn-outline-secondary">Create New</a>
            </div>
        </div>
       
        <hr />
        @foreach (Account account in Model.Accounts)
        {
            <dl class="row">
                <dt class="col-sm-3">
                    @Html.DisplayNameFor(x => account.AccountName)
                </dt>
                <dd class="col-sm-9">
                    @Html.ActionLink(account.AccountName, "Details", "Accounts", new { Id = account.Id })
                </dd>

            </dl>
        }

    </div>
</div>
<div>
    <a asp-action="Edit" asp-route-id="@Model.Id">Edit</a> |
    <a asp-action="Index">Back to List</a>
</div>

<div class="row">
    <h4>Notes</h4>
    <hr />
    
    <div class="col-12">
        <table class="table">
            <thead>
                <tr>
                    <th>
                        @Html.DisplayNameFor(model => model.AccountNotes[0].Account.AccountName)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.AccountNotes[0].AsOfDate)
                    </th>
                    <th>
                        @Html.DisplayNameFor(model => model.AccountNotes[0].Description)
                    </th>
                    
                    <th></th>

                </tr>
            </thead>
            <tbody>
                @foreach (Note note in Model.AccountNotes)
                {
                    <tr>
                        <td>
                            @Html.ActionLink(note.Account.AccountName,"Details","Accounts",new { Id = note.AccountId })
                        </td>
                        <td>
                            @Html.DisplayFor(x => note.AsOfDate)
                        </td>
                        <td>
                            @Html.Raw(note.Description)
                        </td>
                       
                        <td>
                           
                                @Html.ActionLink("Edit", "Edit", "Notes", new { Id = note.Id })
                          

                        </td>
                    </tr>
                }
            </tbody>
        </table>
    </div>
</div>

Related Posts

Comments are closed.