Why is LINQ to SQL Omitting Columns in Select

Question

I'm using LINQ to SQL to select records. I need to union two queries together but the select statements are being changed so that the expressions no longer match preventing the union.

This LINQ query omits my forced columns 'resultType' and 'imageFile' from the final result.

var taglist = from t in dc.ProductTags
 where t.Tag.StartsWith(prefixText)
 select new AutoSearch { 
      resultType = "Tag", 
      name = t.Tag, 
      imageFile = string.Empty, 
      urlElement = t.Tag };

This is the query that is presented.

{SELECT [t0].[Tag] AS [name] FROM [dbo].[ProductTag] AS [t0] WHERE [t0].[Tag] LIKE @p0}

This is the second query to be unioned with the initial one.

var brandlist = from b in dc.Businesses
                    join t in dc.Tags on b.BusinessId equals t.BusinessId
                    where b.Name.StartsWith(prefixText)
                    where b.IsActive == true
                    where t.IsActive == true
                        select new AutoSearch
                        { 
                        resultType = "Business", 
                        name = b.Name, 
                        imageFile = t.AdImage, 
                        urlElement = b.BusinessId.ToString() };

This is the sql for the second query.

SELECT [t0].[Name] AS [name], [t1].[AdImage] AS [imageFile], CONVERT(NVarChar(MAX) [t0].[BusinessId]) AS [urlElement] FROM [dbo].[Business] AS [t0] INNER JOIN [dbo].[Tag] AS [t1] ON ([t0].[BusinessId]) = [t1].[BusinessId] WHERE ([t0].[Name] LIKE @p0)

The union... that throws the error.

var unionedResults = taglist.Union(brandlist);

The error thrown.

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

This is the AutoSearch class.

    public class AutoSearch
    {
    public string name { get; set; }
    public string imageFile { get; set; }
    public string resultType { get; set; }
    public string urlElement { get; set; }
    }

Suggestions as to what is going???

UPDATE***

Found a work around...

Found the issue.

This is a known bug in LINQ, several discussions found here on SO that pointed me in the right direction. Turns out most of the work arounds listed on the site are no longer valid because version 4.0 of broke them too. I found another that worked..

LINQ omits duplicate values for optimization purposes. I was able to change the values of the throw away fields by converting them to strings or lower case or concatenating them.

Terribly inefficient, but it works. Whole day lost for me on this one, perhaps it will save others time.

            var taglist = from t in dc.ProductTags
                      where t.Tag.StartsWith(prefixText)
                      let resultType = "Tag"
                      select new AutoSearch() {
                          resultType = resultType, 
                          name = t.Tag,
                          imageFile = t.Tag.ToString(),
                          urlElement = t.Tag.ToLower()
                      };

        var brandlist = from b in dc.Businesses
                    join t in dc.Tags on b.BusinessId equals t.BusinessId
                    where b.Name.StartsWith(prefixText)
                    where b.IsActive == true
                        where t.IsActive == true
                        where t.AdImage != null
                        where t.AdImage != String.Empty
                        let resultType = "Business"
                        select new AutoSearch
                        {
                            resultType = resultType, 
                            name = b.Name, 
                            imageFile = t.AdImage, 
                            urlElement = b.BusinessId.ToString()
                        };

Answer 1

The only property you reference when you do the select part of your query is Tag, Linq to Sql knows this and optimizes the query to only select columns you're referencing.

In other words, this section of your query only refers to the "Tag" property, which is tied to the Tag column on your database.

new AutoSearch { 
  resultType = "Tag", 
  name = t.Tag, 
  imageFile = string.Empty, 
  urlElement = t.Tag };

What Linq does in this case is pass an expression to the underlying provider (very similar to a binary tree data structure). The provider then parses this tree and creates a SQL query from it at run time. The optimization is done by the provider at runtime which results in the SQL query you're seeing.

Update

For the second problem with the union you basically are trying to union two different SQL statements which is causing the union error. So lets take a look.

The resulting statement that would be causing the error would look something like this

SELECT [t0].[Tag] AS [name] FROM [dbo].[ProductTag] AS [t0] WHERE [t0].[Tag] LIKE @p0
UNION
SELECT [t0].[Name] AS [name], [t1].[AdImage] AS [imageFile], CONVERT(NVarChar(MAX) [t0].[BusinessId]) AS [urlElement] FROM [dbo].[Business] AS [t0] INNER JOIN [dbo].[Tag] AS [t1] ON ([t0].[BusinessId]) = [t1].[BusinessId] WHERE ([t0].[Name] LIKE @p0)

Obviously this is problametic since there is not the same number of columns between the two and that doesn't fly with SQL. While I do not have a pure linq solution there is a workaround.

First You'll need to create a SQL function that just returns a string sent to it.

CREATE FUNCTION ReturnString( @string varchar(max) )
RETURNS varchar(max)
AS
BEGIN
    RETURN @string
END
GO

Next drag and drop this new SQL function into your dbml file, and finally in your query simply call the method where appropriate.

var taglist = from t in dc.ProductTags
                where t.Tag.StartsWith(prefixText)
                select new AutoSearch
                {
                    resultType = dc.ReturnString("Tag"),
                    name = t.Tag,
                    imageFile = dc.ReturnString(string.Empty),
                    urlElement = dc.ReturnString(t.Tag)
                };

var brandlist = from b in dc.Businesses
                join t in dc.Tags on b.BusinessId equals t.BusinessId
                where b.Name.StartsWith(prefixText)
                where b.IsActive == true
                where t.IsActive == true
                select new AutoSearch
                {
                    resultType = dc.ReturnString("Business"),
                    name = b.Name,
                    imageFile = t.AdImage,
                    urlElement = b.BusinessId.ToString()
                };

Now you should be able to perform the union.