Wednesday, January 28, 2009

Linq to SQL and UNION - "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

I'm trying to link together quite a few different tables at the moment in Linq...

And I've hit an interesting SQL error....

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

Bascially what is happening is:

The two queries I'm trying to UNION are:

                var inwardActivityCommentFeedItems = from aComment in db.SlodgeComments_ActivityComments
                                                join comment in db.SlodgeComments_Comments on aComment.CommentId equals comment.CommentId
                                                join activity in db.SlodgeRoutes_Activities on aComment.ActivityId equals activity.ActivityId
                                                join activityUser in db.dnn_Users on activity.UserId equals activityUser.UserID
                                                join commentUser in db.dnn_Users on comment.CommenterUserId equals commentUser.UserID
                                                where activityUser.UserID == userId
                                                orderby comment.CommentDate descending
                                                select new FeedItem
                                                {
                                                    ActivityId = activity.ActivityId,
                                                    Date = comment.CommentDate,
                                                    CommentingUserId = commentUser.UserID,
                                                    CommentingUserName = commentUser.Username,
                                                    CommentText = comment.CommentText,
                                                    FeedItemType = FeedItemType.ActivityComment,
                                                    ActivityTitle = activity.Title,
                                                    TargetUserId = activityUser.UserID,
                                                    CommentId = comment.CommentId,
                                                    TargetUserName = activityUser.Username                                           
                                                };

                var activityFeedItems = from activity in db.SlodgeRoutes_Activities
                                      join user in db.dnn_Users on activity.UserId equals user.UserID
                                      where activity.UserId == userId
                                      orderby activity.StartTime descending
                                      select new FeedItem
                                      {
                                          ActivityId = activity.ActivityId,
                                          ActivityTitle = activity.Title,
                                          Date = activity.StartTime,
                                          CommentingUserId = -1,
                                          CommentingUserName = string.Empty,
                                          CommentText = string.Empty,
                                          CommentId = -1,
                                          FeedItemType = FeedItemType.Activity,
                                          TargetUserId = user.UserID,
                                          TargetUserName = user.Username
                                      };

So they both produce FeedItems...

But the SQL produced is:

{SELECT TOP (40) [t8].[value] AS [FeedItemType], [t8].[UserID] AS [CommentingUserId], [t8].[Username] AS [CommentingUserName], [t8].[UserID2] AS [TargetUserId], [t8].[Username2] AS [TargetUserName], [t8].[CommentText], [t8].[CommentDate] AS [Date], [t8].[ActivityId], [t8].[Title] AS [ActivityTitle], [t8].[CommentId]
FROM (
    SELECT [t7].[value], [t7].[UserID], [t7].[Username], [t7].[UserID2], [t7].[Username2], [t7].[CommentText], [t7].[CommentDate], [t7].[ActivityId], [t7].[Title], [t7].[CommentId]
    FROM (
        SELECT @p1 AS [value], [t4].[UserID], [t4].[Username], [t3].[UserID] AS [UserID2], [t3].[Username] AS [Username2], [t1].[CommentText], [t1].[CommentDate], [t2].[ActivityId], [t2].[Title], [t1].[CommentId]
        FROM [dbo].[SlodgeComments_ActivityComment] AS [t0]
        INNER JOIN [dbo].[SlodgeComments_Comment] AS [t1] ON [t0].[CommentId] = [t1].[CommentId]
        INNER JOIN [dbo].[SlodgeRoutes_Activities] AS [t2] ON [t0].[ActivityId] = [t2].[ActivityId]
        INNER JOIN [dbo].[dnn_Users] AS [t3] ON [t2].[UserId] = [t3].[UserID]
        INNER JOIN [dbo].[dnn_Users] AS [t4] ON [t1].[CommenterUserId] = [t4].[UserID]
        WHERE [t3].[UserID] = @p0
        UNION
        SELECT @p3 AS [value], @p4 AS [value2], @p5 AS [value3], [t6].[UserID], [t6].[Username], [t5].[StartTime], [t5].[ActivityId], [t5].[Title]
        FROM [dbo].[SlodgeRoutes_Activities] AS [t5]
        INNER JOIN [dbo].[dnn_Users] AS [t6] ON [t5].[UserId] = [t6].[UserID]
        WHERE [t5].[UserId] = @p2
        ) AS [t7]
    ) AS [t8]
}

If you look closely at the SQL you can see the two selection statements inside the union have been generated as:
        SELECT @p1 AS [value],
[t4].[UserID],
[t4].[Username],
[t3].[UserID] AS [UserID2],
[t3].[Username] AS [Username2],
[t1].[CommentText],
[t1].[CommentDate],
[t2].[ActivityId],
[t2].[Title],
[t1].[CommentId]

        SELECT @p3 AS [value],
@p4 AS [value2],
@p5 AS [value3],
[t6].[UserID],
[t6].[Username],
[t5].[StartTime],
[t5].[ActivityId],
[t5].[Title]

So the second statement does have less SELECT items returned than the first.

....


Eventually I worked this out as.... the problem is in bit of the the Linq which says:
                                          CommentingUserId = -1,
                                          CommentingUserName = string.Empty,
                                          CommentText = string.Empty,
                                          CommentId = -1,

The SQL generated by this somehow decides it can cut down on returning the -1's twice and the string.empty's twice...

So if I replace this with
                                          CommentingUserId = -1,
                                          CommentingUserName = "1",
                                          CommentText = "2",
                                          CommentId = -2,

Then I no longer see the error....

Of course I'm not saying this Linq is optimal or good practice - I'm just coding at the moment :)

7 comments:

  1. I've got a similar problem that I've not managed to solve as yet. My query is:-

    var allActiveOrders = from o in db.Orders where o.Active select o;

    Further down I setup 2 queries that get different parts of the orders table and then UNION both results. This works fine. But if I also want to check that the customer is active and I change it to:

    var allActiveOrders = from o in db.Orders where o.Active && o.Customer.Active select o;

    the UNION fails because the SQL generated for the first half of the UNION has Customer.ScreenName column appended to it, wheras the second half doesn't. Any ideas would be appreciated! Ta.

    ReplyDelete
  2. I've been struggling with the same problem and I'm glad to say that I've found a temporary workaround. The workaround is easy but feels like quite a hack.

    Basically I use the let keyword to set up some temporary range variables to hold my temporary values.

    In your case I believe it would look something like this.

    from ...

    let d1 = -1
    let d2 = string.Empty
    let d3 = string.Empty
    let d4 = -1

    select new FeedItem()
    {
    ...
    CommentingUserId = d1,
    CommentingUserName = d2,
    CommentText = d3,
    CommentId = d4,
    ...
    }

    The SQL this produces is also more messy than it should be however it seems to perform just fine. For me this one of the top items I hope they address in Linq to SQL for .NET Framework 4.0.

    ReplyDelete
  3. I had this problem today and solved it by converting the queries to lists first using the ToList function then doing a Union. Hope that helps.

    ReplyDelete
  4. @Mike,

    While this does fix it, it completely destroys any advantages you get from using the IEnumerable (or IQueryable) returned by Linq2SQL. Creating an in-memory list with large data is pretty bad performance wise.

    It does work, just not well. I would consider this a bug in Linq2SQL.

    ReplyDelete
  5. I like Jeff's solution. It's hacky, but in the best possible way to deal with a Microsoft bug. Thanks man.

    ReplyDelete
  6. After struggling from 10 days, converting to ToList() solved my problem. Thanks.

    ReplyDelete
  7. Thanks, using the let keyword worked perfectly.

    ReplyDelete