I am building an MVC application that includes two drop-down lists - one for customer names and a second for the contacts associated with that customer.
Each of the contacts also has one or more categories assigned to it. I only want to show the contacts that have a category ID of 6 or 7. I'm initially populating the contact drop-down list via an AJAX script that is invoked when a customer is selected from the customer drop-down. That process works correctly; each time a different customer is chosen, the appropriate contacts (the ones associated to the customer and having the category ID of 6 or 7).
When the create button is clicked, I check the form for errors, including the requirement that a customer has been chosen and that a contact for that customer has been chosen. I'm using a [Range()] validator, and it works correctly.
The problem I'm running in to is building the list of contacts for the drop-down if one has not been selected. I can populate a set of contacts based on the customer ID that has been selected and populate the drop-down successfully. However, if I add a join statement, the drop-down list errors out stating that the contact ID column is not in the set.
My models are as follows (some columns omitted to keep things brief):
Customer
public int Customer_UID
public string Name
public string Street
public string City
Contact
public int Contact_UID
public int Customer_UID
public string NAME
public string Phone
public string Email
Category_Link
public int Link_UID
public int Contact_UID
public int Category_UID
The View includes the following code to display the drop-down list of Contacts:
<div class="form-group">
@Html.Label("Customer:", htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.DropDownList("Customer_UID",null,"Select One...", htmlAttributes: new { @class = "form-control" })
@Html.ValidationMessageFor(model => model.Customer_UID, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.Contact_UID, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.DropDownList("Contact_UID", null, htmlAttributes: new { @class = "form-control" })
@Html.ValidationMessageFor(model => model.Contact_UID, "", new { @class = "text-danger" })
</div>
</div>
<!-- JS includes -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script src="//netdna.bootstrapcdn.com/bootstrap/3.1.1/js/bootstrap.min.js"></script>
<script src="//ajax.aspnetcdn.com/ajax/jquery.validate/1.11.1/jquery.validate.min.js"></script>
<script src="//ajax.aspnetcdn.com/ajax/mvc/4.0/jquery.validate.unobtrusive.min.js"></script>
<script type="text/javascript">
var contactUrl = '@Url.Action("FetchContacts")';
var contacts = $('#Contact_UID')
$('#Customer_UID').change(function() {
contacts.empty();
$.getJSON(contactUrl, { ID: $(this).val() }, function (data) {
if (!data) {
return;
}
contacts.append($('<option></option>').val('').text('Select One...'));
$.each(data, function (index, item) {
contacts.append($('<option></option>').val(item.Value).text(item.Text));
});
});
})
</script>
The Controller has the following code for the initial display and to fetch the updated Contact list:
// GET: Portal_Request_Master/Create
public ActionResult Create()
{
ViewBag.Customer_UID = new SelectList(db.tblOrders__Customer_Master.Where(e => e.Status == "A").OrderBy(e => e.Name), "Customer_UID", "Name");
ViewBag.Contact_UID = new SelectList(Enumerable.Empty<SelectListItem>());
return View();
}
public JsonResult FetchContacts(int ID)
{
var data = db.vwCustomer_Contacts
.Join(db.tblOrders__Customer_Contact_Category_Link, c=>c.Contact_UID, l=> l.Contact_UID, (c,l) => new { c,l})
.Where(l => l.c.Customer_UID == ID && (l.l.Category_UID == 6 || l.l.Category_UID == 7))
.Select(l => new { Value = l.c.Contact_UID, Text = l.c.NAME }).Distinct() ;
return Json(data, JsonRequestBehavior.AllowGet);
}
The controller has the following code in the Create action, and this code works, but includes all contacts for the customer, regardless of whether they have the correct categories assigned:
public ActionResult Create([Bind(Include = "Request_UID,Customer_UID,Contact_UID")] tblNOCPortal__Request_Master tblNOCPortal__Request_Master)
{
if (ModelState.IsValid)
{
db.tblNOCPortal__Request_Master.Add(tblNOCPortal__Request_Master);
db.SaveChanges();
return RedirectToAction("Index");
}
// The model was not valid. Display the form with errors
// Add an empty item in case no contacts are found
ViewBag.Contact_UID = new SelectList(Enumerable.Empty<SelectListItem>());
ViewBag.Customer_UID = new SelectList(db.tblOrders__Customer_Master.Where(e => e.Status == "A").OrderBy(e => e.Name), "Customer_UID", "Name", tblNOCPortal__Request_Master.Customer_UID);
if (tblNOCPortal__Request_Master.Customer_UID > 0)
{
// If a customer was selected, populate the contact list
int CustUID = tblNOCPortal__Request_Master.Customer_UID;
// #### Problem code section ####
var data = db.vwCustomer_Contacts
.Where(e => e.Customer_UID == CustUID);
// #### End of problem code section ####
ViewBag.Contact_UID = new SelectList(data,
"Contact_UID",
"Name",
tblNOCPortal__Request_Master.Contact_UID);
}
return View(tblNOCPortal__Request_Master);
}
The code above works okay, but doesn't narrow down the list of available contacts. However, if I use the following code to populate the data object:
var data = db.vwCustomer_Contacts
.Where(e => e.Customer_UID == CustUID)
.Join(db.tblOrders__Customer_Contact_Category_Link,
c => c.Contact_UID,
z => z.Contact_UID,
(c, z) => new { c, z });
Then I receive the following error message:
System.Web.HttpException HResult=0x80004005 Message=DataBinding: '<>f__AnonymousType3`2[[NOCPortal.DAL.vwCustomer_Contacts, NOCPortal, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null],[NOCPortal.DAL.tblOrders__Customer_Contact_Category_Link, NOCPortal, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]' does not contain a property with the name 'Contact_UID'. Source= StackTrace:
I'm not clear on what changes when I do the table join. To test things, I've omitted the where clause on the LINQ code, just to make sure that it wasn't causing the problem. But I'm at a loss as to why the result set isn't working. I'm new to LINQ and lambda coding, and I don't really understand the "resultSelector" syntax in the join statement. I suspect I'm doing something obvious here, and I'd appreciate any insights as to how to fix it. This is a snap in native SQL, but LINQ is throwing me for a loop.
Thanks in advance for any and all help with this!
User contributions licensed under CC BY-SA 3.0