即使子表中没有记录，如何返回记录？

发布于 2024-10-15 07:19:37 字数 416 浏览 2 评论 0原文

以下查询返回员工列表及其姓名、电子邮件地址和各自的公司名称。问题在于我们系统中没有电子邮件地址的员工。如果员工没有电子邮件地址，他们将被完全排除在结果集中。

如何为不存在的电子邮件地址返回 null（或空字符串等），而不是排除该员工？

var employees = from e in Employees where e.ContactAttributes.Any (ca => ca.AttributeID == 19730317 )
from om in e.ChildOrganizationMaps
from ea in e.EmailAddresses
select new {e.FName, e.LName, ea.EmailAddress, om.Parent.CompanyName};

employees.Dump();

原文

The following query returns a list of employees and their names, email address, and respective company names. The problem is with employees that do not have an email address in our system. If an employee doesn't have an email address, they are excluded entirely from the resultset.

How do I return null (or empty string, etc) for an email address that doesn't exist, rather than excluding the employee?

var employees = from e in Employees where e.ContactAttributes.Any (ca => ca.AttributeID == 19730317 )
from om in e.ChildOrganizationMaps
from ea in e.EmailAddresses
select new {e.FName, e.LName, ea.EmailAddress, om.Parent.CompanyName};

employees.Dump();

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

故人爱我别走 2024-10-22 07:19:37

您的查询正在使用联接。您可以使用外部联接，或使用 Include 进行“预加载”，以便提取所有实体及其关系：

var employees = Employees.Include("ChildOrganizationMaps").Include("EmailAddresses").Where(e=>e.ContactAttributes.Any(ca.AttributeID = 19730317)
.Select(e => new {e.FName, e.LName, e.EmailAddress.Address});

Your query is using joins. You can either use an outer join, or use Include for "eager loading" so that you will pull all entities and their relations:

var employees = Employees.Include("ChildOrganizationMaps").Include("EmailAddresses").Where(e=>e.ContactAttributes.Any(ca.AttributeID = 19730317)
.Select(e => new {e.FName, e.LName, e.EmailAddress.Address});

回复收藏 0 原文

再浓的妆也掩不了殇 2024-10-22 07:19:37

使用外连接。对于表中没有行的字段，它将返回 null。

回复收藏 0 原文

夜司空 2024-10-22 07:19:37

编辑：
好的，使用显式左连接 (DefaultIfEmpty) 来实现。

var employees = 
from e in Employees
from om in ChildOrganizationMaps
  .where(o => e.pkKey == o.fkKey).DefaultIfEmpty()
from ea in EmailAddresses
  .where(o => e.pkKey == o.fkKey).DefaultIfEmpty()
where 
  e.ContactAttributes.Any (ca => ca.AttributeID == 19730317 )
select new 
{
  e.FName, 
  e.LName, 
  ea.EmailAddress, 
  om.Parent.CompanyName
};

Edit:
Ok, do it with explicit left join (DefaultIfEmpty) than.

var employees = 
from e in Employees
from om in ChildOrganizationMaps
  .where(o => e.pkKey == o.fkKey).DefaultIfEmpty()
from ea in EmailAddresses
  .where(o => e.pkKey == o.fkKey).DefaultIfEmpty()
where 
  e.ContactAttributes.Any (ca => ca.AttributeID == 19730317 )
select new 
{
  e.FName, 
  e.LName, 
  ea.EmailAddress, 
  om.Parent.CompanyName
};

回复收藏 0 原文

~没有更多了~