c# - "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Question

我有一个相当复杂的 Linq 查询:

var q = from eiods in LinqUtils.GetTable<EIOfficialDesignee>()
        let eiodent = eiods.Entity
        join tel in LinqUtils.GetTable<EntityTelephone>()
        on new { EntityID = eiodent.ID, TelephoneType = EntityTelephone.TTelephone.Office } equals new { tel.EntityID, tel.TelephoneType }
        into Tel
        let Tel1 = Tel.FirstOrDefault()
        join fax in LinqUtils.GetTable<EntityTelephone>()
        on new { EntityID = eiodent.ID, TelephoneType = EntityTelephone.TTelephone.Fax } equals new { fax.EntityID, fax.TelephoneType }
        into Fax
        let Fax1 = Fax.FirstOrDefault()
        join cell in LinqUtils.GetTable<EntityTelephone>().DefaultIfEmpty()
        on new { EntityID = eiodent.ID, TelephoneType = EntityTelephone.TTelephone.Mobile } equals new { cell.EntityID, cell.TelephoneType }
        into Mobile
        let Mobile1 = Mobile.FirstOrDefault()
        where eiods.ID == CurrentEIPatient.EIOfficialDesigneeID
        select new {
          ID = eiods.ID,
          EIODName = eiodent.FormattedName,
          Phone = Tel1 != null ? Tel1.FormattedNumber : "",
          Fax = Fax1 != null ? Fax1.FormattedNumber : "",
          Cellphone = Mobile1 != null ? Mobile1.FormattedNumber : "",
        };

此查询返回一个 SQL 错误:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

是的，一式三份。这是一个明显的指标，表明查询问题被重复了 3 次，即在 3 种不同类型的电话号码中。

根据 SQL 服务器文档，这来自格式错误的查询。但这是 Linq，看在上帝的份上!它如何使查询变形？

除了主要答案之外，如果您对优化我的查询有任何意见，我也很感激...

谢谢!

Answer 1

我自己解决了，这里是为了其他人的利益。

魔鬼在最后的select子句中，具体来说:

Phone = Tel1 != null ? Tel1.FormattedNumber : "",
Fax = Fax1 != null ? Fax1.FormattedNumber : "",
Cellphone = Mobile1 != null ? Mobile1.FormattedNumber : "",

FormattedNumber 属性是一个基于 EntityTelephone 对象的 Number 和 Extension 属性的计算字段。当我将 FormattedNumber 替换为 Number 时，一切正常。

找到了这个问题的最佳解决方案here .