How to Return Results from a Lookup Table in My MVC5 Controller and View?

Question

I have the following table in my schema:

CREATE TABLE [dbo].[LocationType] (
[LocationTypeID]   INT          IDENTITY (1, 1) NOT NULL,
[LocationTypeDesc] VARCHAR (50) NOT NULL,
CONSTRAINT [PK_LocationType] PRIMARY KEY CLUSTERED ([LocationTypeID] ASC)
);

My Quote entity is as follows: database first entity generated by EF

using System;
using System.Collections.Generic;

public partial class Quote
{
    public Quote()
    {
        this.QuoteItems = new HashSet<QuoteItem>();
        this.QuotePrices = new HashSet<QuotePrice>();
    }

    public System.Guid QuoteID { get; set; }
    public int Number { get; set; }
    public int TypeID { get; set; }
    public string OrigZip { get; set; }
    public string OrigCity { get; set; }
    public string OrigState { get; set; }
    public Nullable<int> OrigCountryID { get; set; }
    public int OrigLocationType { get; set; }
    public bool OrigLiftGate { get; set; }
    public bool OrigInside { get; set; }
    public string OrigContact { get; set; }
    public string OrigBusiness { get; set; }
    public string OrigAddress { get; set; }
    public string OrigPhone { get; set; }
    public string OrigFax { get; set; }
    public string OrigEmail { get; set; }
    public string DestZip { get; set; }
    public string DestCity { get; set; }
    public string DestState { get; set; }
    public string DestContact { get; set; }
    public string DestBusiness { get; set; }
    public string DestAddress { get; set; }
    public string DestPhone { get; set; }
    public string DestFax { get; set; }
    public string DestEmail { get; set; }
    public int DestLocationType { get; set; }
    public bool DestLiftGate { get; set; }
    public bool DestInside { get; set; }
    public Nullable<int> DestCountryID { get; set; }
    public Nullable<int> DestServicePointID { get; set; }
    public Nullable<int> TrailerTypeID { get; set; }
    public Nullable<int> TrailerPartType { get; set; }
    public Nullable<decimal> AmountOfTrailer { get; set; }
    public Nullable<System.DateTime> ReadyDate { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Company { get; set; }
    public string Phone { get; set; }
    public string PhoneExt { get; set; }
    public string Fax { get; set; }
    public string FaxExt { get; set; }
    public string Email { get; set; }
    public Nullable<double> Mileage { get; set; }
    public Nullable<decimal> Price { get; set; }
    public Nullable<int> Days { get; set; }
    public Nullable<decimal> ShipmentCost { get; set; }
    public Nullable<decimal> CreditCardCharge { get; set; }
    public Nullable<decimal> GrossProfit { get; set; }
    public string Carrier { get; set; }
    public string CarrierContact { get; set; }
    public string CarrierPhone { get; set; }
    public Nullable<System.DateTime> DeliveryDate { get; set; }
    public Nullable<System.DateTime> QuoteDate { get; set; }
    public Nullable<System.Guid> UserID { get; set; }
    public bool CallIn { get; set; }
    public bool ShipDocs { get; set; }
    public string SpecialInstructions { get; set; }
    public string ContactAddress { get; set; }
    public string ContactZip { get; set; }
    public string BillFirstName { get; set; }
    public string BillLastName { get; set; }
    public string BillAddress { get; set; }
    public string BillZip { get; set; }
    public string BillPhone { get; set; }
    public string BillFax { get; set; }
    public string BillEmail { get; set; }
    public Nullable<System.DateTime> ValidPickupDate { get; set; }

    public virtual ICollection<QuoteItem> QuoteItems { get; set; }
    public virtual ICollection<QuotePrice> QuotePrices { get; set; }
}

OrigLocationType and DestLocationType are the fields in the Quote entity I need to foreign key over to LocationTypeID in the table mentioned above. When I try to set those foreign keys in SQL Server Management Studio, I'm told that the target table is referenced twice.

I need to make the LocationTypeDesc values available as part of a view I'm creating which is typed to my Quote Model. What is the suggested method to provide the values from the above table to my view so that the user can select a description and the ID is stored as part of the quote process? I'm using EF6 Database First in tandem with MVC5.

Answer 1

First and foremost, when talking about accessing things via EF, post your entity classes, not your tables. The tables EF generates are mostly irrelevant. Since I don't have your entity classes to work with, I'll have to make assumptions that may or may not be correct.

It sounds like what you want is to create a one-to-many relationship between Quote and LocationType. All you need is a reference property on Quote and (to make your life easier) a property to track the actual foreign key column. EF will create an implicit column behind the scenes to track the foreign key, but unless it's actually on your entity, you can't access it or store to it directly.

public class Quote
{
    ...

    [ForeignKey("LocationType")]
    public int LocationTypeID { get; set; }
    public virtual LocationType LocationType { get; set; }
}

You'll need to run a migration to update your tables accordingly. Also, the ForeignKey attribute isn't strictly required here, but without it, you're depending on EF to discern that the int property is the foreign key for the reference property. It can usually handle that well enough if the naming conventions are followed, but I find it easier and less error-prone to just be explicit about it.

Then, you'll simply need to pass a list of LocationType options to whatever view you're editing Quote with. The best way is via a view model, but just to keep things simple, I'll use ViewBag here. So in your action:

ViewBag.LocationTypeChoices = db.LocationTypes.Select(m => new SelectListItem
{
    Value = m.LocationTypeID.ToString(),
    Text = m.LocationTypeDesc
});

Finally, in your view, add a drop down list:

@Html.DropDownListFor(m => m.LocationTypeID, (IEnumerable<SelectListItem>)ViewBag.LocationTypeChoices)