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.

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]

 

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

Login to post a comment