Entity Framework + Model + 2 Tables + List

Question

I have a problem with LINQ results

• My Database Structure

[ Foreign Key ] --> [ Primary Key (PRIMARY KEY TABLE)]

[companyFK] --> [companyID(companyTable) ]

[billFK] --> [billerID (billerTable)]

[attFK] --> [attentedID (attentedTable)]

*Here is my Invoice Model(this model comes automatically with the ADO.NET Entity Framework)

namespace pcis
{
using System;
using System.Collections.Generic;

public partial class invoiceTable
{
    public int invoiceID { get; set; }
    public Nullable<int> companyFK { get; set; }
    public string currency { get; set; }
    public Nullable<decimal> amt { get; set; }
    public Nullable<System.DateTime> startDate { get; set; }
    public Nullable<System.DateTime> endDate { get; set; }
    public Nullable<int> billFK { get; set; }
    public Nullable<int> attFK { get; set; }
    public string status { get; set; }

    public virtual attentedTable attentedTable { get; set; }
    public virtual billerTable billerTable { get; set; }
    public virtual companyTable companyTable { get; set; }
}
}

• Here is my Data Access Layer Code for Invoice Table, In this class I am getting everydata and store it to the List and return List

  using (var db = new PcisDBContext())
  {
      retAllInvoicesList = db.invoiceTables.ToList();
  }
  return retAllInvoicesList;
  

****THE PROBLEM : **** As you see in the code and in the pictures, I am returning only foreign key numbers. Instead of foreign keys I should just show the another field of its row like[company ID to company Name].

Possible Solution : I can just reach every row on the list and get the all data from foreign key's orginal tables and replace them from the specific tables. However In my model there are 3 virtual variables and I thought that I can use them for this problem but I could not find out

    public virtual attentedTable attentedTable { get; set; }
    public virtual billerTable billerTable { get; set; }
    public virtual companyTable companyTable { get; set; }

Answer 1

You can create a view model like below:

public class InvoiceViewModel
{
    public int invoiceID { get; set; }
    public string companyName { get; set; }
    public string currency { get; set; }
    public decimal? amt { get; set; }
    public DateTime? startDate { get; set; }
    public DateTime? endDate { get; set; }
    public string billerName { get; set; }
    public string attentedName { get; set; }
    public string status { get; set; }
}

Then assign value to every property of the ViewModel:

    using (var db = new PcisDBContext())
    {
        var retAllInvoicesList= db.invoiceTables.Select(m => new InvoiceViewModel
        {
            invoiceID = m.invoiceID,
            companyName = m.companyTable.companyName,
            currency = m.currency,
            amt = m.amt,
            startDate = m.startDate,
            endDate = m.endDate,
            billerName = m.billerTable.billerName,
            attentedName = m.attentedTable.attentedName,
            status = m.status
        });
    }
   return retAllInvoicesList;

Finally you can create a strongly typed view using the InvoiceViewModel ViewModel.

Note: Lazy Loading is enabled by default, if you have Lazy Loading disabled, above query will not work.