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?
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?