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]
from
(
 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?


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.

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:

 

SELECT 
	[GroupBy1].[K2] AS [groupColumn2], 
	CAST( [GroupBy1].[A1] AS smalldatetime) AS [C1], 
	[GroupBy1].[K1] AS [groupColumn1]
FROM
(
	SELECT 
	[Filter1].[K1] AS [K1], 
	[Filter1].[K2] AS [K2], 
	Max([Filter1].[A1]) AS [A1]
	FROM
	(
		SELECT 
			[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]

 

Login to post a comment