LINQ 2 SQL Return Multiple as Single String

Question

I have a 1 to Many relationship between tblBusiness and tblPhone. For a specific BusinessID I am trying to return a single string of information to bind to a textbox. Below is my latest attempt along with the Generated SQL from that same LINQ. No matter WHAT I have tried it returns NULL unless there is a value for all 3 fields.

What am I doing wrong? Thanks!

First the 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();
}

Now the 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]

Stephen Cleary · Accepted Answer · 2010-07-09 19:09:43Z

1

I'm a LINQ to Entities man myself, and I'm not sure how much string manipulation the LINQ to SQL provider supports, so I would do the string concatenation and null testing outside of the LINQ context:

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);

The problem with the original query is that nulls propogate, so "blah" + NULL is NULL.

answered Jul 9, 2010 at 19:09

Stephen Cleary

461k78 gold badges722 silver badges858 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

James Curran · Accepted Answer · 2010-07-09 19:12:44Z

Have we tried:

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);

Of course, I really should ask, "what exactly are you after?". The three phone number concatenated together? The first of thoses that non-null? For the latter, try:

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;

Collectives™ on Stack Overflow

LINQ 2 SQL Return Multiple as Single String

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related