views:

26

answers:

1

This is strange. I am moving a stored proc to a service. The TSQL unions multiple selects. To replicate this I created multiple queries resulting in a common new concrete type. Then I issue a return result.ToString(); and the resulting SQL selects have varying numbers of columns specified thus causing an MSSQL Msg 205...

 using (var db = GetDb())
        {
             var fundInv = from f in db.funds
                          select
                                  new Investments
                                      {
                                              Company = f.company,
                                              FullName = f.fullname,
                                              Admin = f.admin,
                                              Fund = f.fund1,
                                              FundCode = f.fundcode,
                                              Source = STR_FUNDS,
                                              IsPortfolio = false,
                                              IsActive = f.active,
                                              Strategy = f.strategy,
                                              SubStrategy = f.substrategy,
                                              AltStrategy = f.altstrategy,
                                              AltSubStrategy = f.altsubstrategy,
                                              Region = f.region,
                                              AltRegion = f.altregion,
                                              UseAlternate = f.usealt,
                                              ClassesAllowed = f.classallowed
                                      };



             var stocksInv = from s in db.stocks
                            where !fundInv.Select(f => f.Company).Contains(s.vehcode)                                 select
                                    new Investments
                                        {
                                                Company = s.company,
                                                FullName = s.issuer,
                                                Admin = STR_PRS,
                                                Fund = s.shortname,
                                                FundCode = s.vehcode,
                                                Source = STR_STOCK,
                                                IsPortfolio = false,
                                                IsActive = (s.inactive == null),
                                                Strategy = s.style,
                                                SubStrategy = s.substyle,
                                                AltStrategy = s.altstyle,
                                                AltSubStrategy = s.altsubsty,
                                                Region = s.geography,
                                                AltRegion = s.altgeo,
                                                UseAlternate = s.usealt,
                                                ClassesAllowed = STR_GENERIC
                                        };


            var bondsInv = from oi in db.bonds
                           where !fundInv.Select(f => f.Company).Contains(oi.vehcode)
                           select
                                   new Investments
                                       {
                                               Company = string.Empty,
                                               FullName = oi.issue,
                                               Admin = STR_PRS1,
                                               Fund = oi.issue,
                                               FundCode = oi.vehcode,
                                               Source = STR_BONDS,
                                               IsPortfolio = false,
                                               IsActive = oi.closed,
                                               Strategy = STR_OTH,
                                               SubStrategy = STR_OTH,
                                               AltStrategy = STR_OTH,
                                               AltSubStrategy = STR_OTH,
                                               Region = STR_OTH,
                                               AltRegion = STR_OTH,
                                               UseAlternate = false,
                                               ClassesAllowed = STR_GENERIC
                                       };

            return (fundInv.Concat(stocksInv).Concat(bondsInv)).ToList();
        }

The code above results in a complex select statement where each "table" above has different column count. (see SQL below) I've been trying a few things but no change yet. Ideas are welcome.

SELECT [t6].[company] AS [Company], 
    [t6].[fullname] AS [FullName], 
    [t6].[admin] AS [Admin], 
    [t6].[fund] AS [Fund], 
    [t6].[fundcode] AS [FundCode], 
    [t6].[value] AS [Source], 
    [t6].[value2] AS [IsPortfolio], 
    [t6].[active] AS [IsActive], 
    [t6].[strategy] AS [Strategy], 
    [t6].[substrategy] AS [SubStrategy], 
    [t6].[altstrategy] AS [AltStrategy], 
    [t6].[altsubstrategy] AS [AltSubStrategy], 
    [t6].[region] AS [Region], 
    [t6].[altregion] AS [AltRegion], 
    [t6].[usealt] AS [UseAlternate], 
    [t6].[classallowed] AS [ClassesAllowed]
FROM (
    SELECT [t3].[company], 
        [t3].[fullname], 
        [t3].[admin], 
        [t3].[fund], 
        [t3].[fundcode], 
        [t3].[value], 
        [t3].[value2], 
        [t3].[active], 
        [t3].[strategy], 
        [t3].[substrategy], 
        [t3].[altstrategy], 
        [t3].[altsubstrategy], 
        [t3].[region], 
        [t3].[altregion], 
        [t3].[usealt], 
        [t3].[classallowed]
FROM (
    SELECT [t0].[company], 
        [t0].[fullname], 
        [t0].[admin], 
        [t0].[fund], 
        [t0].[fundcode], 
        @p0 AS [value], 
        [t0].[active], 
        [t0].[strategy], 
        [t0].[substrategy], 
        [t0].[altstrategy], 
        [t0].[altsubstrategy], 
        [t0].[region], 
        [t0].[altregion], 
        [t0].[usealt], 
        [t0].[classallowed]
    FROM [zInvest].[funds] AS [t0]
    UNION ALL
    SELECT [t1].[company], 
        [t1].[issuer], 
        @p6 AS [value], 
        [t1].[shortname], 
        [t1].[vehcode], 
        @p7 AS [value2], 
        @p8 AS [value3],  
        (CASE 
            WHEN [t1].[inactive] IS NULL THEN 1
            ELSE 0
        END) AS [value5], 
        [t1].[style], 
        [t1].[substyle], 
        [t1].[altstyle], 
        [t1].[altsubsty], 
        [t1].[geography], 
        [t1].[altgeo], 
        [t1].[usealt], 
        @p10 AS [value6]
    FROM [zBank].[stocks] AS [t1]
    WHERE (NOT (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM [zInvest].[funds] AS [t2]
        WHERE [t2].[company] = [t1].[vehcode]
        ))) AND ([t1].[vehcode] <> @p2) AND (SUBSTRING([t1].[vehcode], @p3 + 1, @p4) <> @p5)
    ) AS [t3]
UNION ALL
SELECT @p11 AS [value], 
    [t4].[issue], 
    @p12 AS [value2], 
    [t4].[vehcode], 
    @p13 AS [value3], 
    @p14 AS [value4], 
    [t4].[closed], 
    @p16 AS [value6], 
    @p17 AS [value7]
FROM [zMut].[bonds] AS [t4]
WHERE NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [zInvest].[funds] AS [t5]
    WHERE [t5].[company] = [t4].[vehcode]
    ))
) AS [t6]
+1  A: 

Think it is a bug in LINQ to SQL. You can find it here:

http://connect.microsoft.com/VisualStudio/feedback/details/355734/linq-to-sql-produces-incorrect-tsql-when-using-union-or-concat

Hope the workaround given there works for you.

Raja
I think you're correct. I tried using ToString() on all the columns but didn't work. I ran my queries seperately then used concat on the results. That worked as far as I can tell. Luckily the queries are such that the workaround didn't take much change. I'll check performance on this.
secoast
Sounds good..Hope it works out. good luck :-)
Raja