Linq To Sql Concat() dropping fields in created TSQL
- by user191468
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]
