MVC4 asp.net 中的内部联接查询

本文关键字：查询 内部 asp net MVC4 | 更新日期: 2023-09-27 18:00:37

我写了关于搜索特定员工的查询，但没有工作，我知道我的查询中有错误，但现在没有在哪里。我有 2 张桌子，一张用于公司，另一张用于员工信息。进度将是：

  • 1'st 查询按部门编号和公司搜索公司表数字然后得到PK_companyID。
  • 2'ed 加入公司。PK_companyID==employee.FK_companyID .
  • 3'ed 查询将在员工表中按FK_companyID搜索，当名称输入在 employee table 中将相同。

我希望它清楚理解

 var query = (from c in db.Company
            where c.departmentNO== departmentNumber && c.companyNo==companyNumber
            join x in db.Employee c.PK_companyID==x.FK_companyID
            where (x.FirstName.Contains(firstName ?? x.FirstName)
            && x.SecondName.Contains(secondName ?? x.SecondName)
            && x.ThirdName.Contains(thirdName ?? x.ThirdName)
            && x.FourthName.Contains(fourthName ?? x.FourthName))
            select x).ToList();

提前谢谢。

MVC4 asp.net 中的内部联接查询

您可以使用下面的代码简化查询和过滤，如果您没有使用所有过滤器，也应该会产生更干净的 SQL。

var employees = (from c in db.Company
            join e in db.Employee on c.PK_companyID equals e.FK_companyID
            where c.departmentNO == departmentNumber && c.companyNo == companyNumber
            select e);
if (!String.IsNullOrEmpty(firstName))
    employees = employees.Where(pr => pr.FirstName.Contains(firstName));
if (!String.IsNullOrEmpty(secondName))
    employees = employees.Where(pr => pr.SecondName.Contains(secondName));
if (!String.IsNullOrEmpty(thirdName))
    employees = employees.Where(pr => pr.ThirdName.Contains(thirdName));
if (!String.IsNullOrEmpty(fourthName))
    employees = employees.Where(pr => pr.FourthName.Contains(fourthName));
return employees.ToList();

var query = (from c in db.Company                                        
             join x in db.Employee c.PK_companyID == x.FK_companyID
         where c.departmentNO == departmentNumber && c.companyNo == companyNumber
             && (x.FirstName.Contains(firstName ?? x.FirstName)
                          && x.SecondName.Contains(secondName ?? x.SecondName)
                          && x.ThirdName.Contains(thirdName ?? x.ThirdName)
                          && x.FourthName.Contains(fourthName ?? x.FourthName))
            select x).ToList();

您应该在连接中使用关键字"equals"而不是"=="，如下所示：

var query = (from c in db.Company                                        
             join x in db.Employee c.PK_companyID equals x.FK_companyID
         where c.departmentNO == departmentNumber && c.companyNo == companyNumber
         .......

希望这会起作用。

相关文章：