Error in MVC application populating a dropdown from a table join in LINQ

0

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!

linq
join
model-view-controller
dropdown

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0