VistaDB Support Center

Start a new topic

Handling LINQ Group By

I am having issues returning data sets from a group. It is returning the correct number of rows, but it is returning rows of null values. Here is a simplified version of the sql statement used by the linq query


select x.groupColumn, count(x.columnToCount) as [Total]
 select groupColumn, columnToCount
 from myTable
) AS x
group by groupColumn



When I tried running it in the data builder, I get the same results. I found that if I manually make the outer select and group by match then the data returned is correct (ie, if I it to group by x.groupColumn).

As far as I'm aware I don't have control on the sql statement produced by linq in entity framework.

Any ideas?

What's the original LINQ statement which is producing this SQL query?

Here is the original linq statement:


from w in Context.myTable
	join wc in Context.otherTable
		on new {w.intColToMatch, w.groupColumn2} equals
			new {wc.intColToMatch, wc.groupColumn2} into tblJoin
	from c in tblJoin.DefaultIfEmpty()
where c.intColToMatch == someIntVariable && w.bitColToMatch
group c by new {w.groupColumn2, w.groupColumn1}
into grp
select new
	groupColumn2 = grp.Key.groupColumn2,
	MaxColToEval = (from dtl in grp select dtl.colToEval).Max(),
	groupColumn1 = grp.Key.groupColumn1


and here is the full sql produced:


	[GroupBy1].[K2] AS [groupColumn2], 
	CAST( [GroupBy1].[A1] AS smalldatetime) AS [C1], 
	[GroupBy1].[K1] AS [groupColumn1]
	[Filter1].[K1] AS [K1], 
	[Filter1].[K2] AS [K2], 
	Max([Filter1].[A1]) AS [A1]
			[Extent1].[groupColumn1] AS [K1], 
			[Extent1].[groupColumn2] AS [K2], 
			[Extent2].[colToEval] AS [A1]
		FROM  [myTable] AS [Extent1]
			INNER JOIN [otherTable] AS [Extent2]
				ON ((([Extent1].[groupColumn2] = [Extent2].[groupColumn2])
					AND ([Extent1].[intColToMatch] = [Extent2].[intColToMatch]))
					AND ([Extent2].[groupColumn2] = [Extent1].[groupColumn2]))
					AND ([Extent2].[intColToMatch] = [Extent1].[intColToMatch])
		WHERE ([Extent2].[intColToMatch] = @p__linq__0) AND ([Extent1].[bitColToMatch] = cast(1 as bit))
	)  AS [Filter1]
	GROUP BY [K1], [K2]
)  AS [GroupBy1]


We've been working this via a ticket.

We've fixed the SQL generation logic to avoid the bad query structure.  That will be released with 5.2.2.

Login to post a comment