This is used in a complex grid report that users can click around in and filter out various things.
It could probably do with re-writing at some point, but at the moment, there is not any time allocated to do this, so would like to just get it working.
Code: Select all
private List<AnalyserModel.DataRow> SetModelDataForAll(bool filterByProdDate,
AnalyserModel.DateRanges dates,
IQueryable<AnalysisBookings> analysisData,
IQueryable<Product> products,
IQueryable<Contact> contacts,
IQueryable<Account> accounts)
{
return (from cont in contacts
from prod in products
from acc in accounts.Where(p => p.Fk_Contact == cont.PrimaryKey)
from data in analysisData.Where(p => p.Fk_Product == prod.PrimaryKey && p.Fk_Account_Agent == acc.PrimaryKey)
group data by 1 into grp
select new AnalyserModel.DataRow()
{
RowCaption = "All data",
Pax1 = grp.Where(q => q.ProductDate >= dates.StartDate1 && q.ProductDate <= dates.EndDate1).Sum(q => q.Pax),
Gross1 = grp.Where(q => q.ProductDate >= dates.StartDate1 && q.ProductDate <= dates.EndDate1).Sum(q => q.Gross),
Net1 = grp.Where(q => q.ProductDate >= dates.StartDate1 && q.ProductDate <= dates.EndDate1).Sum(q => q.Net),
Pax2 = grp.Where(q => q.ProductDate >= dates.StartDate2 && q.ProductDate <= dates.EndDate2).Sum(q => q.Pax),
Gross2 = grp.Where(q => q.ProductDate >= dates.StartDate2 && q.ProductDate <= dates.EndDate2).Sum(q => q.Gross),
Net2 = grp.Where(q => q.ProductDate >= dates.StartDate2 && q.ProductDate <= dates.EndDate2).Sum(q => q.Net),
}).ToList();
}
This is the error that is returned :
qlException: Ambiguous column name 'fk_Contact'.
Ambiguous column name 'PrimaryKey'.
Invalid column name 'PrimaryKey1'.
Invalid column name 'PrimaryKey2'.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
It seems that internally it cannot deal with aliasing the two fk_Contact fields and the three PrimaryKey fields. Not sure about the final error.
Seems like a bug, but is there an initial workaround ?
Thanks
This is the invalid SQL statement that has been generated
Code: Select all
SELECT (
SELECT SUM([t28].[Pax]) AS [C1],
[t28].[ProductDate],
[t28].[Gross],
[t28].[Net],
[t28].[Pax],
[t27].[fk_Contact],
[t25].[PrimaryKey],
[t28].[Fk_Product],
[t28].[Fk_Account_Agent],
[t26].[PrimaryKey] AS [PrimaryKey1],
[t27].[PrimaryKey] AS [PrimaryKey2]
FROM [dbo].[Contact] [t25],
[dbo].[Product] [t26],
[dbo].[Account] [t27],
[dbo].[AnalysisBookings] [t28]
WHERE ([t28].[ProductDate] >= @p5)
AND ([t28].[ProductDate] <= @p6)
) AS [C1],
(
SELECT SUM([Gross]) AS [C1]
FROM [dbo].[Contact] [t25], [dbo].[Product] [t26], [dbo].[Account] [t27], [dbo].[AnalysisBookings] [t28]
WHERE ([ProductDate] >= @p7)
AND ([ProductDate] <= @p8)
) AS [C2],
(
SELECT SUM([Net]) AS [C1]
FROM [dbo].[Contact] [t25], [dbo].[Product] [t26], [dbo].[Account] [t27], [dbo].[AnalysisBookings] [t28]
WHERE ([ProductDate] >= @p9)
AND ([ProductDate] <= @p10)
) AS [C3],
(
SELECT SUM([Pax]) AS [C1]
FROM [dbo].[Contact] [t25], [dbo].[Product] [t26], [dbo].[Account] [t27], [dbo].[AnalysisBookings] [t28]
WHERE ([ProductDate] >= @p11)
AND ([ProductDate] <= @p12)
) AS [C4],
(
SELECT SUM([Gross]) AS [C1]
FROM [dbo].[Contact] [t25], [dbo].[Product] [t26], [dbo].[Account] [t27], [dbo].[AnalysisBookings] [t28]
WHERE ([ProductDate] >= @p13) AND ([ProductDate] <= @p14)
) AS [C5],
(
SELECT SUM([Net]) AS [C1]
FROM [dbo].[Contact] [t25], [dbo].[Product] [t26], [dbo].[Account] [t27], [dbo].[AnalysisBookings] [t28]
WHERE ([ProductDate] >= @p15) AND ([ProductDate] <= @p16)
) AS [C6]
FROM [dbo].[Contact] [t1],
[dbo].[Product] [t2],
[dbo].[Account] [t3],
[dbo].[AnalysisBookings] [t4]
WHERE ([t3].[fk_Contact] = [t1].[PrimaryKey])
AND ([t4].[Fk_Product] = [t2].[PrimaryKey])
AND ([t4].[Fk_Account_Agent] = [t3].[PrimaryKey])
AND ((([t4].[ProductDate] >= @p0)
AND ([t4].[ProductDate] <= @p1)) OR (([t4].[ProductDate] >= @p2)
AND ([t4].[ProductDate] <= @p3)))
AND ([fk_Contact] = [PrimaryKey])
AND ((([ProductDate] >= @p0)
AND ([ProductDate] <= @p1)) OR (([ProductDate] >= @p2)
AND ([ProductDate] <= @p3)))
AND ([Fk_Product] = [PrimaryKey1])
AND ([Fk_Account_Agent] = [PrimaryKey2])
AND (@p4 = @p4)
Code: Select all
SELECT CONVERT(Decimal(29,4),(
SELECT SUM([t9].[Pax])
FROM [dbo].[Contact] AS [t6],
[dbo].[Product] AS [t7],
[dbo].[Account] AS [t8],
[dbo].[AnalysisBookings] AS [t9]
WHERE ([t9].[ProductDate] >= @p5) AND ([t9].[ProductDate] <= @p6) AND ([t5].[value] = @p7) AND ([t8].[fk_Contact] = ([t6].[PrimaryKey])) AND ([t9].[Fk_Product] = ([t7].[PrimaryKey])) AND ([t9].[Fk_Account_Agent] = ([t8].[PrimaryKey])) AND ((([t9].[ProductDate] >= @p1) AND ([t9].[ProductDate] <= @p2)) OR (([t9].[ProductDate] >= @p3) AND ([t9].[ProductDate] <= @p4)))
)) AS [Pax1], (
SELECT SUM([t13].[Gross])
FROM [dbo].[Contact] AS [t10], [dbo].[Product] AS [t11], [dbo].[Account] AS [t12], [dbo].[AnalysisBookings] AS [t13]
WHERE ([t13].[ProductDate] >= @p8) AND ([t13].[ProductDate] <= @p9) AND ([t5].[value] = @p7) AND ([t12].[fk_Contact] = ([t10].[PrimaryKey])) AND ([t13].[Fk_Product] = ([t11].[PrimaryKey])) AND ([t13].[Fk_Account_Agent] = ([t12].[PrimaryKey])) AND ((([t13].[ProductDate] >= @p1) AND ([t13].[ProductDate] <= @p2)) OR (([t13].[ProductDate] >= @p3) AND ([t13].[ProductDate] <= @p4)))
) AS [Gross1], (
SELECT SUM([t17].[Net])
FROM [dbo].[Contact] AS [t14], [dbo].[Product] AS [t15], [dbo].[Account] AS [t16], [dbo].[AnalysisBookings] AS [t17]
WHERE ([t17].[ProductDate] >= @p10) AND ([t17].[ProductDate] <= @p11) AND ([t5].[value] = @p7) AND ([t16].[fk_Contact] = ([t14].[PrimaryKey])) AND ([t17].[Fk_Product] = ([t15].[PrimaryKey])) AND ([t17].[Fk_Account_Agent] = ([t16].[PrimaryKey])) AND ((([t17].[ProductDate] >= @p1) AND ([t17].[ProductDate] <= @p2)) OR (([t17].[ProductDate] >= @p3) AND ([t17].[ProductDate] <= @p4)))
) AS [Net1], CONVERT(Decimal(29,4),(
SELECT SUM([t21].[Pax])
FROM [dbo].[Contact] AS [t18], [dbo].[Product] AS [t19], [dbo].[Account] AS [t20], [dbo].[AnalysisBookings] AS [t21]
WHERE ([t21].[ProductDate] >= @p12) AND ([t21].[ProductDate] <= @p13) AND ([t5].[value] = @p7) AND ([t20].[fk_Contact] = ([t18].[PrimaryKey])) AND ([t21].[Fk_Product] = ([t19].[PrimaryKey])) AND ([t21].[Fk_Account_Agent] = ([t20].[PrimaryKey])) AND ((([t21].[ProductDate] >= @p1) AND ([t21].[ProductDate] <= @p2)) OR (([t21].[ProductDate] >= @p3) AND ([t21].[ProductDate] <= @p4)))
)) AS [Pax2], (
SELECT SUM([t25].[Gross])
FROM [dbo].[Contact] AS [t22], [dbo].[Product] AS [t23], [dbo].[Account] AS [t24], [dbo].[AnalysisBookings] AS [t25]
WHERE ([t25].[ProductDate] >= @p14) AND ([t25].[ProductDate] <= @p15) AND ([t5].[value] = @p7) AND ([t24].[fk_Contact] = ([t22].[PrimaryKey])) AND ([t25].[Fk_Product] = ([t23].[PrimaryKey])) AND ([t25].[Fk_Account_Agent] = ([t24].[PrimaryKey])) AND ((([t25].[ProductDate] >= @p1) AND ([t25].[ProductDate] <= @p2)) OR (([t25].[ProductDate] >= @p3) AND ([t25].[ProductDate] <= @p4)))
) AS [Gross2], (
SELECT SUM([t29].[Net])
FROM [dbo].[Contact] AS [t26], [dbo].[Product] AS [t27], [dbo].[Account] AS [t28], [dbo].[AnalysisBookings] AS [t29]
WHERE ([t29].[ProductDate] >= @p16) AND ([t29].[ProductDate] <= @p17) AND ([t5].[value] = @p7) AND ([t28].[fk_Contact] = ([t26].[PrimaryKey])) AND ([t29].[Fk_Product] = ([t27].[PrimaryKey])) AND ([t29].[Fk_Account_Agent] = ([t28].[PrimaryKey])) AND ((([t29].[ProductDate] >= @p1) AND ([t29].[ProductDate] <= @p2)) OR (([t29].[ProductDate] >= @p3) AND ([t29].[ProductDate] <= @p4)))
) AS [Net2]
FROM (
SELECT [t4].[value]
FROM (
SELECT @p0 AS [value], [t2].[fk_Contact], [t0].[PrimaryKey], [t3].[Fk_Product], [t1].[PrimaryKey] AS [PrimaryKey2], [t3].[Fk_Account_Agent], [t2].[PrimaryKey] AS [PrimaryKey3], [t3].[ProductDate]
FROM [dbo].[Contact] AS [t0], [dbo].[Product] AS [t1], [dbo].[Account] AS [t2], [dbo].[AnalysisBookings] AS [t3]
) AS [t4]
WHERE ([t4].[fk_Contact] = ([t4].[PrimaryKey])) AND ([t4].[Fk_Product] = ([t4].[PrimaryKey2])) AND ([t4].[Fk_Account_Agent] = ([t4].[PrimaryKey3])) AND ((([t4].[ProductDate] >= @p1) AND ([t4].[ProductDate] <= @p2)) OR (([t4].[ProductDate] >= @p3) AND ([t4].[ProductDate] <= @p4)))
GROUP BY [t4].[value]
) AS [t5]