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]
Ryan Rillo
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
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?