locked
Populating drop down with entity Framework RRS feed

  • Question

  • User351619809 posted

     have the following table bookAssignment:

    ID   Group   OFFICE  name
    ---------------------------
    1    Test1      X   West
    2    Test3      Y   North
    3    Test1      Z   South
    4    Test1      P   South

    I have around 3000 rows in this table where ID is unique and primary key. GROUP and Office has lot of repeated value.

    I have another table for Office and Group

    tblOffice
    
    OfficeID    OfficeName
    1            X
    2            Y
    3            Z
    4            P
    
    tblGroup
    
    GroupID     GroupName
    1            Test1
    2            Test3
    3            Test6

    There is no relationship between BookAssignment table and tblGroup and tblOffice other than BookAssignment Office and Group columns has the same name as in the columns of office and Group

    I have the following model class:

     public partial class bookAssignment_new
    {
        public int ID { get; set; }
        public Nullable<int> Book { get; set; }
        public string TechName { get; set; }
        public string Office { get; set; }
        public IEnumerable<SelectListItem> OfficeList { get; set; }
        public IEnumerable<SelectListItem> GroupList { get; set; }
    }

    I want to populate two drop downs on my view with the Office and group with distinct, not null and no empty string values.

    This is what I tried, but did not work.

    In the controller, I tried to add the code like this:

      public ActionResult Index()
        {
            var model = new bookAssignment_new();
            using (BookAssignmentEntities db = new BookAssignmentEntities())
            {
                var dbData = db.bookAssignment_new.ToList();
                model.OfficeList = GetSelectListItems(dbData);
            }
    
            return View(model);
        }
    
    
    private IEnumerable<SelectListItem> GetSelectListItems(IEnumerable<bookAssignment_new> elements)
            {
                var selectList = new List<SelectListItem>();
                foreach (var element in elements)
                {
                    if(element.Office != null && element.Office !="")
                    selectList.Add(new SelectListItem
                    {
                        Value = element.Office.ToString(),
                        Text = element.Office.ToString()
                    });
                }
                return selectList.Distinct();
            }

    My view has the code like so:

    @Html.DropDownList("OfficeList", Model.OfficeList)
    
    @Html.DropDownListFor("GroupList", Model.GroupList)

    I am getting an error in the view saying :

    There is no ViewData item of type 'IEnumerable<SelectListItem>' that has the key 'OfficeList'.'

    Is their no way, I can populate a drop down in my View with the Group and Office data without any relationship.

    I also tried putting the value directly in ViewData

    public ActionResult Index()
            {
                using (BookAssignmentEntities Entity = new BookAssignmentEntities())
                {
                    var fromDatabaseEF = new SelectList(Entity.OfficeNames.ToList(), "OfficeID", "Office");
                    ViewData["DBMySkills"] = fromDatabaseEF;
                }
    
                return View();
            }

    In my View, I did this:

    @Html.DropDownList("OfficeNames", (IEnumerable<SelectListItem>)ViewData["DBMySkills"])

    I still get the same error

    Sunday, September 20, 2020 12:40 AM

Answers

  • User1686398519 posted

    Hi anjaliagarwal5, 

    According to your needs, I modified the code you provided, and wrote a simple example, you can refer to it.

    • You can use Include to load related entities.

    Model

        public partial class bookAssignment_new
        {
            public int ID { get; set; }
            public Nullable<int> Book { get; set; }
            public int OfficeID { get; set; }
            [ForeignKey("OfficeID")]
            public OfficeName OfficeName { get; set; }
            public int GroupID { get; set; }
            [ForeignKey("GroupID")]
            public GroupName GroupName { get; set; }
            [NotMapped]
            public SelectList OfficeSelectList { get; set; }
            [NotMapped]
            public SelectList GroupSelectList { get; set; }
        }
        public partial class OfficeName
        {
            [Key]
            public int OfficeID { get; set; }
            public string Office { get; set; }
            public ICollection<bookAssignment_new> bookAssignment_new { get; set; }
        }
        public partial class GroupName
        {
            [Key]
            public int GroupID { get; set; }
            public string Group { get; set; }
            public ICollection<bookAssignment_new> bookAssignment_new { get; set; }
        }

    Controller

        public class DropdownListController : Controller
        {
            public DailyMVCDemoContext db = new DailyMVCDemoContext();
            public ActionResult Index()
            {
                var model = db.bookAssignment_new.Include(m => m.GroupName).Include(m => m.OfficeName).ToList();
                model.ForEach(m=>{
                    m.OfficeSelectList = GetOfficeSelectList(m.OfficeID);
                    m.GroupSelectList = GetGroupSelectList(m.GroupID);
                });
                return View(model);
            }
            public SelectList GetOfficeSelectList(int OfficeID)
            {
                return new SelectList(db.OfficeNames.ToList(), "OfficeID", "Office", OfficeID);
            }
            public SelectList GetGroupSelectList(int GroupID)
            {
                return new SelectList(db.GroupNames.ToList(), "GroupID", "Group", GroupID);
            }
        }

    Index

    @model IEnumerable<DailyMVCDemo.Models.bookAssignment_new>
    <table class="table">
        <tr>
            <td>
                @Html.DisplayNameFor(m=>m.ID)
            </td>
            <td>
                @Html.DisplayNameFor(m => m.OfficeName.Office)
            </td>
            <td>
                @Html.DisplayNameFor(m => m.OfficeName.Office)
            </td>
            <td>
                @Html.DisplayNameFor(m => m.GroupName.Group)
            </td>
            <td>
    @Html.DisplayNameFor(m => m.GroupName.Group) </td> </tr> @foreach (var item in Model) { <tr> <td>@item.ID</td> <td> @Html.DropDownListFor(m => item.OfficeID, item.OfficeSelectList) </td> <td>@item.OfficeName.Office</td> <td> @Html.DropDownListFor(m => item.GroupID, item.GroupSelectList) </td> <td>@item.GroupName.Group</td> </tr> } </table>

    Here is the result.

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 22, 2020 8:52 AM

All replies

  • User475983607 posted

    The table is not designed properly.   You need a many-to-many relationship.   Add a Group and Office table each with a Primary key.   Add one more table, OfficeGroup, which has foreign key GroupId and OfficeId columns.

    You can learn about joins and many-to-many relationships at the following links.

    https://dba.stackexchange.com/questions/118693/efficient-way-to-join-two-tables-with-one-to-many-relationship

    https://docs.microsoft.com/en-us/sql/ssms/visual-db-tools/map-many-to-many-relationships-visual-database-tools?view=sql-server-ver15

    https://www.google.com/search?q=entity+framework+many+to+many

    Sunday, September 20, 2020 10:28 AM
  • User351619809 posted

    I made the following changes in my table relationship, but I still cannot populate the drop down box:

    I have the following table bookAssignment:

    ID   Group   OFFICE  name  FKOffice     FKGroup
    ---------------------------
    1    Test1      X   West     1          1
    2    Test3      Y   North    2          2
    3    Test1      Z   South    3          1
    4    Test1      P   South    4          1

    I have around 3000 rows in this table where ID is unique and primary key. GROUP and Office has lot of repeated value.

    I have another table for Office and Group

    tblOffice
    
    OfficeID    OfficeName
    1            X
    2            Y
    3            Z
    4            P
    
    tblGroup
    
    GroupID     GroupName
    1            Test1
    2            Test3
    3            Test6

    There is a relationship between BookAssignment table and tblGroup and tblOffice. FKOffice and FKGroup are the foreign keys in BookAssignment table.

    I have the following model class:

     public partial class bookAssignment_new
    {
        public int ID { get; set; }
        public Nullable<int> Book { get; set; }
        public string TechName { get; set; }
        public string Office { get; set; }
        public IEnumerable<SelectListItem> OfficeList { get; set; }
        public IEnumerable<SelectListItem> GroupList { get; set; }
        public virtual GroupName GroupName { get; set; }
        public virtual OfficeName OfficeName { get; set; }
    }

    My GroupName Model is like so:

    public partial class GroupName
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public GroupName()
        {
            this.bookAssignment_new = new HashSet<bookAssignment_new>();
        }
    
        public int GroupID { get; set; }
        public string Groups { get; set; }
    
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<bookAssignment_new> bookAssignment_new { get; set; }
    }

    My OfficeName Model is like so:

    public partial class OfficeName
        {
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
            public OfficeName()
            {
                this.bookAssignment_new = new HashSet<bookAssignment_new>();
            }
        
            public int OfficeID { get; set; }
            public string Office { get; set; }
        
            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
            public virtual ICollection<bookAssignment_new> bookAssignment_new { get; set; }
        }

    I want to populate two drop downs on my view with the Office and group.

    Below is my controller code:

      public ActionResult Index()
        {
            
            return View();
        }

    How can I make a Group and Office drop down in my view and populate it with Group and Office drop down values.

    Thank You.

    Monday, September 21, 2020 12:16 AM
  • User1686398519 posted

    Hi anjaliagarwal5, 

    According to your needs, I modified the code you provided, and wrote a simple example, you can refer to it.

    • You can use Include to load related entities.

    Model

        public partial class bookAssignment_new
        {
            public int ID { get; set; }
            public Nullable<int> Book { get; set; }
            public int OfficeID { get; set; }
            [ForeignKey("OfficeID")]
            public OfficeName OfficeName { get; set; }
            public int GroupID { get; set; }
            [ForeignKey("GroupID")]
            public GroupName GroupName { get; set; }
            [NotMapped]
            public SelectList OfficeSelectList { get; set; }
            [NotMapped]
            public SelectList GroupSelectList { get; set; }
        }
        public partial class OfficeName
        {
            [Key]
            public int OfficeID { get; set; }
            public string Office { get; set; }
            public ICollection<bookAssignment_new> bookAssignment_new { get; set; }
        }
        public partial class GroupName
        {
            [Key]
            public int GroupID { get; set; }
            public string Group { get; set; }
            public ICollection<bookAssignment_new> bookAssignment_new { get; set; }
        }

    Controller

        public class DropdownListController : Controller
        {
            public DailyMVCDemoContext db = new DailyMVCDemoContext();
            public ActionResult Index()
            {
                var model = db.bookAssignment_new.Include(m => m.GroupName).Include(m => m.OfficeName).ToList();
                model.ForEach(m=>{
                    m.OfficeSelectList = GetOfficeSelectList(m.OfficeID);
                    m.GroupSelectList = GetGroupSelectList(m.GroupID);
                });
                return View(model);
            }
            public SelectList GetOfficeSelectList(int OfficeID)
            {
                return new SelectList(db.OfficeNames.ToList(), "OfficeID", "Office", OfficeID);
            }
            public SelectList GetGroupSelectList(int GroupID)
            {
                return new SelectList(db.GroupNames.ToList(), "GroupID", "Group", GroupID);
            }
        }

    Index

    @model IEnumerable<DailyMVCDemo.Models.bookAssignment_new>
    <table class="table">
        <tr>
            <td>
                @Html.DisplayNameFor(m=>m.ID)
            </td>
            <td>
                @Html.DisplayNameFor(m => m.OfficeName.Office)
            </td>
            <td>
                @Html.DisplayNameFor(m => m.OfficeName.Office)
            </td>
            <td>
                @Html.DisplayNameFor(m => m.GroupName.Group)
            </td>
            <td>
    @Html.DisplayNameFor(m => m.GroupName.Group) </td> </tr> @foreach (var item in Model) { <tr> <td>@item.ID</td> <td> @Html.DropDownListFor(m => item.OfficeID, item.OfficeSelectList) </td> <td>@item.OfficeName.Office</td> <td> @Html.DropDownListFor(m => item.GroupID, item.GroupSelectList) </td> <td>@item.GroupName.Group</td> </tr> } </table>

    Here is the result.

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 22, 2020 8:52 AM