c# - 如何使用 ActionLink MVC 5 执行以下 sql 查询？

Question

是否可以单击一个操作链接名称并让它检索关联的记录，就像下面的 sql 语句一样？ CustomerName html 链接 --> 转到客户详细信息 View 的索引，仅检索与客户名称 (CustomerNameID) 关联的记录。

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

型号:客户姓名

 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; }
    }
}

模型: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));

查看:

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

路线:

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

我重建了我的 Controller 来清理它。它是目前开箱即用的标准 Controller 。CustomerNameController 索引():

// 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 索引():

  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));
        }

如有任何帮助，我们将不胜感激。这可能吗？

Answer 1

答案很简单，但我没能理解。多次运行该程序后，我注意到正在传递 CustomerName/Index ID，但 CustomerDetail/Index 未能提取它并过滤结果。我添加了以下代码，它终于起作用了。感谢大家的帮助。

        // 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());
    }