How do I perform the following sql query with an ActionLink MVC 5?

Question

Is it possible to click on a actionlink name and have it retrieve the associated records much like the sql statement below? CustomerName html link --> goes to the index of the customer detail view only retrieving the records associated with the customer name (CustomerNameID).

SELECT dbo.CustomerName.CustomerNameName, dbo.CustomerDetail.CustomerNameID
FROM dbo.CustomerDetail INNER JOIN
     dbo.CustomerName ON dbo.CustomerDetail.CustomerNameID = dbo.Cust

Model: CustomerName

 public partial class CustomerName
    {
        public CustomerName()
        {
            this.CustomerDetails = new HashSet<CustomerDetail>();
            this.CustomerEquipments = new HashSet<CustomerEquipment>();
            this.CustomerHealthChecks = new HashSet<CustomerHealthCheck>();
        }

        public int CustomerNameID { get; set; }
        public Nullable<int> CustomerHealthCheckID { get; set; }
        public Nullable<int> CustomerEquipmentID { get; set; }
        public int MasterLicNum { get; set; }
        public string CustomerNameName { get; set; }
        public Nullable<int> Active { get; set; }

        public virtual CustomerDetail CustomerDetail { get; set; }

        public virtual ICollection<CustomerDetail> CustomerDetails { get; set; }
        public virtual ICollection<CustomerEquipment> CustomerEquipments { get; set; }
        public virtual ICollection<CustomerHealthCheck> CustomerHealthChecks { get; set; }
    }
}

Model:CustomerDetail

public partial class CustomerDetail
    {
        public CustomerDetail()
        {
            this.CustomerNotes = new HashSet<CustomerNote>();
        }

        public int CustomerDetailID { get; set; }
        public int CustomerNameID { get; set; }
        public System.DateTime DateUpdated { get; set; }
        public System.DateTime DateCreated { get; set; }
        public int CustomerPriorityID { get; set; }
        public int CustomerStatusHealthID { get; set; }
        public Nullable<int> EquipmentID { get; set; }
        public Nullable<int> CustomerHealthCheckID { get; set; }
        public int LREngineerID { get; set; }
        public int CustomerEngineerID { get; set; }
        public string Description { get; set; }
        public byte[] RowVersion { get; set; }

        public virtual CustomerEngineer CustomerEngineer { get; set; }
        public virtual CustomerName CustomerName { get; set; }
        public virtual CustomerPriority CustomerPriority { get; set; }
        public virtual CustomerStatusHealth CustomerStatusHealth { get; set; }
        public virtual LREngineer LREngineer { get; set; }
        public virtual ICollection<CustomerNote> CustomerNotes { get; set; }
    }
}

Controller:

return View(CustomerName.ToPagedList(pageNumber, pageSize));

View:

@Html.ActionLink(item.CustomerNameName.ToString(), "Index", "CustomerDetail", new {ID = item.CustomerNameID }, null)

Route:

 routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
            routes.MapMvcAttributeRoutes();
            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "CustomerName", action = "Index", id = UrlParameter.Optional }

I rebuilt my controller to clean it up. It is the standard out of the box controller at the moment. CustomerNameController Index():

// GET: CustomerName
public async Task<ActionResult> Index(int? CustomerNameID = null)
{
    return View(await db.CustomerNames.ToListAsync());
}

// GET: CustomerName/Details/5
public async Task<ActionResult> Details(int? id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    CustomerName customerName = await db.CustomerNames.FindAsync(id);
    if (customerName == null)
    {
        return HttpNotFound();
    }
    return View(customerName);
}

CustomerDetailController Index():

  public async Task<ActionResult> Index(string sortOrder, string currentFilter, string searchString, int? page)
        {
            var customerDetails = db.CustomerDetails.Include(c => c.CustomerEngineer).Include(c => c.CustomerName).Include(c => c.CustomerPriority).Include(c => c.CustomerStatusHealth).Include(c => c.LREngineer);
            ViewBag.CurrentSort = sortOrder;
            ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
            ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";

            if (searchString != null)
            {
                page = 1;
            }
            else
            {
                searchString = currentFilter;
            }

            ViewBag.CurrentFilter = searchString;
             var CustomerDetails = from s in db.CustomerDetails
                           select s;
            if (!String.IsNullOrEmpty(searchString))
            {
                CustomerDetails = CustomerDetails.Where(s => s.Description.Contains(searchString));
            }
            switch (sortOrder)
           {
                case "name_desc":
                    CustomerDetails = CustomerDetails.OrderByDescending(s => s.CustomerNameID);
                    break;
                case "Date":
                    CustomerDetails = CustomerDetails.OrderBy(s => s.DateUpdated);
                    break;
                case "date_desc":
                    CustomerDetails = CustomerDetails.OrderByDescending(s => s.DateUpdated);
                    break;
                default:  // Name ascending 
                    CustomerDetails = CustomerDetails.OrderBy(s => s.CustomerNameID);
                    break;
            }

            int pageSize = 15;
            int pageNumber = (page ?? 1);

            //return View(await customerDetails.ToListAsync());
            return View(CustomerDetails.ToPagedList(pageNumber, pageSize));
        }

Any assistance would be appreciated. Is this even possible?

Answer 1

The answer was quite simple yet I failed to grasp it. After running the program numerous times I noticed that the CustomerName/Index ID was being passed but the CustomerDetail/Index was failing to pick it up and filter the results. I added the following code and it finally worked. Thanks all for the help.

        // GET: CustomerDetail
    public async Task<ActionResult> Index(int CustomerNameID)
    {

        IQueryable<CustomerDetail> CustomerDetail = db.CustomerDetails
           .Where(c => c.CustomerNameID == CustomerNameID)
           .OrderBy(d => d.CustomerNameID)
           .Include(d => d.CustomerName);


        return View(await CustomerDetail.ToListAsync());
    }