Error running a query on multiple IQueryable results
Posted: Tue 10 Mar 2020 03:57
This is another query that worked in LinqToSQL and fails in LinqConnect. Version 4.9.1920
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.
Each of the underlying Tables (Contact,Product and Account) have a field called PrimaryKey. Contact and Product both have fields called fk_Contact. None of the tables have a key called PrimaryKey1 or PrimaryKey2.
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
and this is the equivalent code produced by LinqToSQL
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]