CODEWITHSUNDEEP
c#mysqllinqasp.net-mvc-4lambda
Erick Bongo
Erick Bongo

Reputation: 109

MVC4 - Linq Lambda expression to query multiple association tables

Am trying to create a cascading drop down and have been following this tutorial here

My database is more complex than the one in the tutorial and I need help with creating a lambda expression

Here are my database tables

DB Tables

The cascading drop down I want to create, will allow a user to select a RiskType and then depending on the selection will display the associated GroupMembers for the selected RiskType.

Here is the code I have in my controller

public ActionResult AddNewRisk()
        {
            ViewBag.RiskTypeID = new SelectList(_DBContext.RiskTypes, "ID", "Description");
            ViewBag.GroupMembers = new SelectList(new List<GroupMember>(), "ID", "Name");
            return View();
        }


public IList<GroupMember> GetGroupMember(int SelectedRiskTypeID)
        {
            return   _DBContext
                     .RiskTypeHasGroups

        }

        public JsonResult GetJsonGroupMember(int ID)
        {
            var GroupMemberListT = this.GetGroupMember(Convert.ToInt32(ID));
            var GroupMemberList = GroupMemberListT.Select(x => new SelectListItem()
                                   {
                                       Text = x.Name,
                                       Value = x.ID.ToString()
                                   });
            return Json(GroupMemberList, JsonRequestBehavior.AllowGet);

        }

It's in the method named GetGroupMember that am having trouble and don't know how to write the correct lambda expression in order to pull back only the group members which have a matching RiskGroup.ID followed by a matching RiskType.ID. If anyone could show me the correct way to do this, I'd really appreciate it.

Thanks in advance.

Upvotes: 1

Views: 803

Answers (1)

Robert McKee
Robert McKee

Reputation: 21487

Once the model is simplified as I suggested, then your query becomes:

public IQueryable<GroupMember> GetGroupMember(int SelectedRiskTypeID)
{
  return _DBContext.GroupMembers
    .Where(g=>g.RiskGroups.Any(rg=>rg.ID=SelectedRiskTypeID));
}

If you decide to keep the IDs, then this would be your query:

public IQueryable<GroupMember> GetGroupMember(int SelectedRiskTypeID)
{
  return _DBContext.GroupMembers
    .Where(gm=>gm.RiskGroupHasGroupTypes
      .Any(rghgt=>rghg‌​t.RiskGroup.ID==SelectedRiskGroupTypeID))
}

Upvotes: 1

Related Questions