LINQ 2 SQL 将多个返回为单个字符串

Question

我在 tblBusiness 和 tblPhone 之间存在一对多关系。对于特定的 BusinessID，我尝试返回单个信息字符串以绑定到文本框。下面是我的最新尝试以及从同一 LINQ 生成的 SQL。无论我尝试什么，它都会返回 NULL ，除非所有 3 个字段都有值。

我做错了什么？谢谢！

---

首先是 LINQ：

using (var context = ConnectDataContext.Create())
{
   context.Log = Console.Out;

   var business = from businesse in context.tblBusinesses
                 where businesse.BusinessID == businessID
                select businesse.BusinessName 
                     + businesse.ContactName 
                     + businesse.tblPhones.Select(p=>p.PhoneNumber)
                                          .FirstOrDefault() 
                                         ?? string.Empty;

            return business.Single();
}

---

现在是 SQL：

SELECT [t2].[value]
FROM (
    SELECT COALESCE(([t0].[BusinessName] + [t0].[ContactName]) + ((
        SELECT TOP (1) [t1].[PhoneNumber]
        FROM [dbo].[tblPhone] AS [t1]
        WHERE [t1].[BusinessID] = [t0].[BusinessID]
        )),@p0) AS [value], [t0].[BusinessID]
    FROM [dbo].[tblBusiness] AS [t0]
    ) AS [t2]
WHERE [t2].[BusinessID] = @p1
    -- @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
    -- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [118]

Answer 1

我自己是 LINQ to Entities 人员，并且不确定 LINQ to SQL 提供程序支持多少字符串操作，因此我会在 LINQ 上下文之外进行字符串连接和空测试：

var business = from businesse in context.tblBusinesses 
               where businesse.BusinessID == businessID 
               select new
               {
                   businesse.BusinessName,
                   businesse.ContactName,
                   Phone = businesse.tblPhones.Select(p=>p.PhoneNumber)
                       .FirstOrDefault() ?? string.Empty
               }.Single();
return (business.BusinessName ?? string.Empty) +
    (business.ContactName ?? string.Empty) +
    (business.Phone ?? string.Empty);

原始查询的问题是空值传播，所以“blah”+ NULL 是 NULL。

Answer 2

我们是否尝试过：

var business = from businesse in context.tblBusinesses 
             where businesse.BusinessID == businessID 
            select (businesse.BusinessName ?? string.Empty)
                 + (businesse.ContactName  ?? string.Empty)
                 + (businesse.tblPhones.Select(p=>p.PhoneNumber) 
                                      .FirstOrDefault()  
                                     ?? string.Empty); 

当然，我真的应该问，“你到底在追求什么？”。三个电话号码串联在一起？其中第一个非空？对于后者，请尝试：

var business = from businesse in context.tblBusinesses 
             where businesse.BusinessID == businessID 
            select businesse.BusinessName ?? 
                   businesse.ContactName  ?? 
                   businesse.tblPhones.Select(p=>p.PhoneNumber) 
                                      .FirstOrDefault()  
                                     ?? string.Empty;