MVC Dropdown Menu using Linked Tables

Question

I'm pretty new to MVC and still trying to learn. I've been researching for a few hours now and have not found what I am looking for. What I have is two linked tables, table1 and table2. Let's say table2 contains/displays the ID from table1 and instead of that I wish to display the value linked to the ID, ex: the name, instead of the value when my views are in create or details, but when in edit or create, I wish to display a dropdown menu for the user to select the name. Like I said I have researched quite a while and been unable to find an answer. I have no code to show unfortunately, but a hint in the right direction would be extremely helpful.

Answer 1

First, create your tables:

--instead of Breaz, use your database name
USE [Breaz]
GO
/****** Object:  Table [dbo].[table1]    Script Date: 5/1/2017 10:11:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[table1](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](20) NULL,
 CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[table2]    Script Date: 5/1/2017 10:11:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[table2](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [table1Id] [int] NULL,
    [table2Desc] [varchar](20) NULL,
 CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[table1] ON 

GO
INSERT [dbo].[table1] ([Id], [name]) VALUES (1, N'ddl1')
GO
INSERT [dbo].[table1] ([Id], [name]) VALUES (2, N'ddl2')
GO
INSERT [dbo].[table1] ([Id], [name]) VALUES (3, N'ddl3')
GO
SET IDENTITY_INSERT [dbo].[table1] OFF
GO
SET IDENTITY_INSERT [dbo].[table2] ON 

GO
INSERT [dbo].[table2] ([Id], [table1Id], [table2Desc]) VALUES (1, 1, N'select1')
GO
INSERT [dbo].[table2] ([Id], [table1Id], [table2Desc]) VALUES (2, 2, N'select2')
GO
SET IDENTITY_INSERT [dbo].[table2] OFF
GO
ALTER TABLE [dbo].[table2]  WITH CHECK ADD  CONSTRAINT [FK_table2_table1] FOREIGN KEY([table1Id])
REFERENCES [dbo].[table1] ([Id])
GO
ALTER TABLE [dbo].[table2] CHECK CONSTRAINT [FK_table2_table1]
GO

Create an EDMX file like so:

Right click on the Models folder and click Add->ADO.DATA ENTITY MODEL, name it aDropDown . Click ok. With Generate from database highlighted already click Next. Click New Connection... Put your server name in the server name box. I use .\sqlexpress . If you use user name and password, click on Use Windows Auth, and put in your credentials and click Save my password. Select your database name from the dropdown. Click okay. If you had entered a password, wait until you can answer Y, include the sensitive.. Copy to the clipboard the name under 'Save entity connection setting...' Click Next. Expand Tables, and check table1 and table2, and click Finish. Click Ok twice to the windows that come up. Close the diagram.

Here is your controller/view:

public class DDLModel
{
    public DDLModel()
    {
        List<SelectListItem> list = new List<SelectListItem>();

        try
        {
            //BreazEntities16 for you should be the item that you had copied to the clipboard
            //before, or check the aDropDown.Context.cs file
            using (BreazEntities16 entity = new BreazEntities16())
            {
                entity.table1.
                    OrderBy(r => r.name).ToList().ForEach(r => list.Add(
                    new SelectListItem { Text = r.name, Value = r.Id.ToString() }));
            }
        }
        catch (Exception e)
        { }

        //add <select> to first item
        list.Insert(0, new SelectListItem { Text = "", Value = "" });
        Table1List = list;
    }

    public List<SelectListItem> Table1List { get; set; }

    //If you want to put [Required] or other attributes into your model property, and
    //you think you don't want to directly use table2, please use DO use the direct table2,
    //Just see the following post
    //http://stackoverflow.com/questions/14059455/adding-validation-attributes-with-an-entity-framework-data-model
    public table2 table2 { get; set; }
}

public class HomeController : Controller
{
    //I use Index60, you should use Index or what the RouteConfig points to
    [HttpPost]
    public ActionResult Index60(DDLModel ddlModel)
    {
        //BreazEntities16 for you should be the item that you had copied to the clipboard
        //before, or check the aDropDown.Context.cs file
        using (BreazEntities16 entity = new BreazEntities16())
        {
            //modify the tableId of table2
            entity.table2.Attach(ddlModel.table2);
            entity.Entry(ddlModel.table2).Property(x => x.table1Id).IsModified = true;
            entity.Configuration.ValidateOnSaveEnabled = false;
            entity.SaveChanges();  //tableid changes, not table2Desc
        }

        return View(ddlModel);
    }

    //I use Index60, you should use Index or what the RouteConfig points to
    public ActionResult Index60()
    {
        DDLModel ddlModel = new DDLModel();

        //BreazEntities16 for you should be the item that you had copied to the clipboard
        //before, or check the aDropDown.Context.cs file
        using (BreazEntities16 entity = new BreazEntities16())
        {
            //make sure there is using System.Data.Entity at top
            //I am getting first but you can .Where to find another 
            ddlModel.table2 = entity.table2.Include(q => q.table1).Where(r => r.Id == 1).FirstOrDefault();
        }

        return View(ddlModel);
    }

Here is your view:

@model Testy20161006.Controllers.DDLModel
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index60</title>
</head>
<body>
    @using (Html.BeginForm())
    {
        <div>
            <table>
                <tr>
                    <td>
                        @*need the hidden id*@
                        @Html.HiddenFor(r=>r.table2.Id)

                        @Html.LabelFor(r => r.table2.table1Id)
                    </td>
                    <td>
                        @Html.DropDownListFor(m => m.table2.table1Id,
                    new SelectList(Model.Table1List, "Value", "Text"))
                        @Html.ValidationMessageFor(model => model.table2.table1Id)
                    </td>
                </tr>
            </table>
        </div>
        <input type="submit" value="submit" />
    }
</body>
</html>