MVC LINQ-在相关表中使用Contains搜索/查询文本

Question

我有一个显示用户的视图。在该列表中，列值来自三个表。mvc应用程序中的标准Users表，一个Persons表，它是Users表的一组附加信息(Persons.UserId=Users.Id)，我有一个Companies表，有些人引用了它(Person.CompanyId=Company.Id)。有些人没有相关的公司。

所以我有一个Linq声明，它返回了所有人对我的看法：

var query = from user in db.Users
join person in db.Persons on user.Id equals person.UserID
join company in db.Companies on person.CompanyId equals company.ID into companyResult
from company in companyResult.DefaultIfEmpty()
select new PersonIndexItem { ID = person.ID, Name = user.FullName, Phone = user.PhoneNumber, Email = user.Email, Company = company == null ? "" : company.Name };

这很好，我有我所有的用户，没有公司关系的用户在公司栏里什么都没有。

在我的视图中，我有一个与显示的大部分字段匹配的搜索框。这是我使用的linq语句：

var query = from user in db.Users where user.FullName.Contains(searchString) || user.Email.Contains(searchString) || user.PhoneNumber.Contains(searchString)
join person in db.Persons on user.Id equals person.UserID
join company in db.Companies on person.CompanyId equals company.ID into companyResult
from company in companyResult.DefaultIfEmpty()
select new PersonIndexItem { ID = person.ID, Name = user.FullName, Phone = user.PhoneNumber, Email = user.Email, Company = company == null ? "" : company.Name };

这很好，如果我键入一个字符串，它会返回用户名、电话或电子邮件中包含该字符串的所有记录。

但我想将搜索添加到Companies(Name)中，这样如果我键入一个公司名称，我就会获得该公司的所有用户。

我就是做不到。有什么想法吗？

EDIT对于那些想知道的人，数据结构如下：

// Users table
class User
{
int Id;
string FullName;
string Email;
string PhoneNumber;
}
// Persons table
class Person
{
int Id;
int UserId;     // Users table
int CompanyId;  // Companies table
}
// Companies table
class Company
{
int Id;
string Name;
}

Answer 1

我意识到这可能不可能只通过一个查询实现。我做了两个查询，并将它们组合在一起。它得到了预期的结果，没有重复。

var query = from user in db.Users where user.FullName.Contains(searchString) || user.Email.Contains(searchString) || user.PhoneNumber.Contains(searchString)
join person in db.Persons on user.Id equals person.UserID
join company in db.Companies on person.CompanyId equals company.ID into companyResult
from company in companyResult.DefaultIfEmpty()
select new PersonIndexItem { ID = person.ID, Name = user.FullName, Phone = user.PhoneNumber, Email = user.Email, Company = company == null ? "" : company.Name };
var query2 = from user in db.Users
join person in db.Persons on user.Id equals person.UserID
join company in db.Companies on person.CompanyId equals company.ID into companyResult
from company in companyResult.DefaultIfEmpty() where company.Name.Contains(searchString)
select new PersonIndexItem { ID = person.ID, Name = user.FullName, Phone = user.PhoneNumber, Email = user.Email, Company = company == null ? "" : company.Name };
var resultUnion = query.Union(query2);

如果有人有更好的解决方案，请告诉我！