(Linq to SQL) Why is my Concat failing?

Question

What im trying to do is search thru multiple tables (that are kind of unrelated) and everytime it finds a match - create a new more generic "Results" Type

for everytable - concat this result type set. The error i get is: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

The funny thing is Concat works on 2 tables, but the 3rd one doesnt work. Here is the SQL profile

Working

exec sp_executesql N'SELECT [t2].[Id], [t2].[value] AS [Type], [t2].[Name] AS     [Description], [t2].[value2] AS [Action]
FROM (
SELECT [t0].[Id], @p1 AS [value], [t0].[Name], @p2 AS [value2]
FROM [dbo].[PLID] AS [t0]
WHERE [t0].[Name] LIKE @p0
UNION ALL
SELECT [t1].[Id], @p4 AS [value], [t1].[Number], @p5 AS [value2]
FROM [dbo].[Phone] AS [t1]
WHERE [t1].[Number] LIKE @p3
) AS [t2]',N'@p0 nvarchar(6),@p1 nvarchar(4),@p2 nvarchar(5),@p3 nvarchar(6),@p4 nvarchar(9),@p5 nvarchar(5)',    @p0=N'%2010%',@p1=N'PLID',@p2=N'Phone',@p3=N'%2010%',@p4=N'Telephone',@p5=N'Phone'

Not Working

exec sp_executesql N'SELECT [t2].[Id], [t2].[value] AS [Type], [t2].[Name] AS [Description], [t2].[value2] AS [Action]
FROM (
SELECT [t0].[Id], @p1 AS [value], [t0].[Name], @p2 AS [value2]
FROM [dbo].[PLID] AS [t0]
WHERE [t0].[Name] LIKE @p0
UNION ALL
SELECT [t1].[Id], @p4 AS [value], [t1].[LastName]
FROM [dbo].[Employee] AS [t1]
WHERE [t1].[LastName] LIKE @p3
) AS [t2]',N'@p0 nvarchar(6),@p1 nvarchar(4),@p2 nvarchar(5),@p3 nvarchar(6),@p4 nvarchar(8)',@p0=N'%2010%',@p1=N'PLID',@p2=N'Phone',@p3=N'%2010%',@p4=N'Employee'

Here is the actual c# code (Working)

public IQueryable<TelephonySearchResults> SearchPhones(string search)
    {
        IQueryable<TelephonySearchResults> results = from phone in _dataContext.Phones
                                                     where phone.Number.Contains(search)
                                                     select new TelephonySearchResults
                                                                {
                                                                    Description = phone.Number,
                                                                    Type = SearchTelephonyResultsType.Phone,
                                                                    Id = phone.Id,
                                                                    Action = "Phone"
                                                                };


        return results;
    }

    public IQueryable<TelephonySearchResults> SearchPlids(string search)
    {
        IQueryable<TelephonySearchResults> results = from plid in _dataContext.PLIDs
                                                     where plid.Name.Contains(search)
                                                     select new TelephonySearchResults
                                                                {
                                                                    Description = plid.Name,
                                                                    Type = SearchTelephonyResultsType.Plid,
                                                                    Id = plid.Id,
                                                                    Action = "Phone"
                                                                };


        return results;
    }

Not Working

public IQueryable<TelephonySearchResults> SearchEmployees(string search)
    {

        IQueryable<TelephonySearchResults> results = from employee in _dataContext.Employees
                                                     where employee.LastName.Contains(search)
                                                     select new TelephonySearchResults
                                                     {
                                                         Description = employee.LastName,
                                                         Type = SearchTelephonyResultsType.Employee,
                                                         Id = employee.Id,
                                                         Action = "Employee"
                                                     };

        return results;
    }

And the code that concats them

IQueryable<TelephonySearchResults> phones = _repository.SearchPhones(searchCriteria);
        IQueryable<TelephonySearchResults> plids = _repository.SearchPlids(searchCriteria);
        IQueryable<TelephonySearchResults> employees = _repository.SearchEmployees(searchCriteria);

//works
        IQueryable<TelephonySearchResults> results = plids.Concat(phones);
//does not
IQueryable<TelephonySearchResults> results = plids.Concat(employees);

As far as i can tell everything looks exactly the same codewise, but in the SQL profiler the only difference is there is no "p5 AS [value2]" like there is on the working one

Any help would be greatly appreciated.

Answer 1

Do you get the same results if you concat phones & employees together?

I would also try to eliminate the Type property from the TelephonySearchResults, something in my gut tells me that might have something to do with the issue.