Error running a query on multiple IQueryable results

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
Paul_Ibis
Posts: 20
Joined: Wed 22 Jan 2020 03:02

Error running a query on multiple IQueryable results

Post by Paul_Ibis » 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.

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();
		}
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

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)
and this is the equivalent code produced by LinqToSQL

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]

Paul_Ibis
Posts: 20
Joined: Wed 22 Jan 2020 03:02

Re: Error running a query on multiple IQueryable results

Post by Paul_Ibis » Fri 13 Mar 2020 00:30

I changed the Linq query to be as below, and it got a bit better

Code: Select all

var query = (from data in analysisData
                             join prod in products on data.Fk_Product equals prod.PrimaryKey
                             join acc in accounts on data.Fk_Account_Agent equals acc.Fk_Contact
                             join cont in contacts on acc.Fk_Contact equals cont.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),
                             });
 
This now produces this error message : "'dbo.AnalysisBookings.ProductDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."
Looking at the SQL produced as below, the problem is in the first sub query. It should only have the SUM([t25].[Pax]) AS [C1] row. Those other rows should not be in there. Remove them and the whole thing works as expected.

Code: Select all

SELECT (
    SELECT SUM([t25].[Pax]) AS [C1], 
			[t25].[ProductDate], 
			[t25].[Gross], 
			[t25].[Net], 
			[t25].[Pax]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([t25].[ProductDate] >= @p5) AND ([t25].[ProductDate] <= @p6)
    ) AS [C1], 
	(
    SELECT SUM([Gross]) AS [C1]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([ProductDate] >= @p7) AND ([ProductDate] <= @p8)
    ) AS [C2], (
    SELECT SUM([Net]) AS [C1]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([ProductDate] >= @p9) AND ([ProductDate] <= @p10)
    ) AS [C3], (
    SELECT SUM([Pax]) AS [C1]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([ProductDate] >= @p11) AND ([ProductDate] <= @p12)
    ) AS [C4], (
    SELECT SUM([Gross]) AS [C1]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([ProductDate] >= @p13) AND ([ProductDate] <= @p14)
    ) AS [C5], (
    SELECT SUM([Net]) AS [C1]
    FROM [dbo].[AnalysisBookings] [t25]
    INNER JOIN [dbo].[Product] [t26] ON [t25].[Fk_Product] = [t26].[PrimaryKey]
    INNER JOIN [dbo].[Account] [t27] ON [t25].[Fk_Account_Agent] = [t27].[fk_Contact]
    INNER JOIN [dbo].[Contact] [t28] ON [t27].[fk_Contact] = [t28].[PrimaryKey]
    WHERE ([ProductDate] >= @p15) AND ([ProductDate] <= @p16)
    ) AS [C6]
FROM [dbo].[AnalysisBookings] [t1]
INNER JOIN [dbo].[Product] [t2] ON [t1].[Fk_Product] = [t2].[PrimaryKey]
INNER JOIN [dbo].[Account] [t3] ON [t1].[Fk_Account_Agent] = [t3].[fk_Contact]
INNER JOIN [dbo].[Contact] [t4] ON [t3].[fk_Contact] = [t4].[PrimaryKey]
WHERE ((([t1].[ProductDate] >= @p0) AND ([t1].[ProductDate] <= @p1)) OR (([t1].[ProductDate] >= @p2) AND ([t1].[ProductDate] <= @p3))) AND ((([ProductDate] >= @p0) AND ([ProductDate] <= @p1)) OR (([ProductDate] >= @p2) AND ([ProductDate] <= @p3))) AND (@p4 = @p4)

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Error running a query on multiple IQueryable results

Post by Shalex » Mon 16 Mar 2020 22:41

We have reproduced both errors and are investigating the issue. We will notify you about the result.

Post Reply