public void GroupBy_WithConstantKey_GetsReplacedBySubquery() { CheckQuery(Cooks.GroupBy(c => 0).Select(c => c.Key), "SELECT [q1].[key] AS [value] FROM (" + "SELECT [t0].[value] AS [key] FROM [CookTable] AS [t2] CROSS APPLY (SELECT @1 AS [value]) AS [t0] GROUP BY [t0].[value]" + ") AS [q1]", new CommandParameter("@1", 0)); }
public void GroupBy_WithResultSelector_AndElementSelector() { CheckQuery( Cooks.GroupBy(c => c.Name, c => c.ID, (key, group) => new { Name = key }), "SELECT [q0].[key] AS [Name] " + "FROM (SELECT [t1].[Name] AS [key] FROM [CookTable] AS [t1] GROUP BY [t1].[Name]) AS [q0]", row => (object)new { Name = row.GetValue <string> (new ColumnID("Name", 0)) }); }
public void AllAfterGroupBy_WithResultSelector() { CheckQuery( () => Cooks.GroupBy(c => c.Name, c => c.ID, (key, group) => new { Name = key }).All(x => x.Name != null), "SELECT CONVERT(BIT, CASE WHEN NOT EXISTS((" + "SELECT [q0].[key] AS [Name] " + "FROM (SELECT [t1].[Name] AS [key] FROM [CookTable] AS [t1] GROUP BY [t1].[Name]) AS [q0] " + "WHERE NOT ([q0].[key] IS NOT NULL))) " + "THEN 1 ELSE 0 END) AS [value]"); }
public void GroupBy_WithResultSelector_AndElementSelector_AndAggregate() { CheckQuery( Cooks.GroupBy(c => c.Name, c => c.ID, (key, group) => new { Name = key, Count = group.Min() }), "SELECT [q0].[key] AS [Name], [q0].[a0] AS [Count] " + "FROM (SELECT [t1].[Name] AS [key],MIN([t1].[ID]) AS [a0] FROM [CookTable] [t1] GROUP BY [t1].[Name]) AS [q0]", row => (object)new { Name = row.GetValue <string> (new ColumnID("Name_key", 0)), Count = row.GetValue <int> (new ColumnID("Count", 1)) }); }
public void GroupBy_AfterGroupBy() { CheckQuery( Cooks.GroupBy(c => c).GroupBy(c => c.Key.Kitchen.ID).Select(g => g.Key), "SELECT [q1].[key] AS [value] FROM (" + "SELECT [q0].[key_KitchenID] AS [key] FROM (" + "SELECT [t2].[ID] AS [key_ID],[t2].[FirstName] AS [key_FirstName],[t2].[Name] AS [key_Name],[t2].[IsStarredCook] AS [key_IsStarredCook]," + "[t2].[IsFullTimeCook] AS [key_IsFullTimeCook],[t2].[SubstitutedID] AS [key_SubstitutedID],[t2].[KitchenID] AS [key_KitchenID]," + "[t2].[KnifeID] AS [key_KnifeID],[t2].[KnifeClassID] AS [key_KnifeClassID] " + "FROM [CookTable] AS [t2] " + "GROUP BY [t2].[ID],[t2].[FirstName],[t2].[Name],[t2].[IsStarredCook],[t2].[IsFullTimeCook],[t2].[SubstitutedID],[t2].[KitchenID]," + "[t2].[KnifeID],[t2].[KnifeClassID]) AS [q0] " + "GROUP BY [q0].[key_KitchenID]) AS [q1]"); }
public void GroupBy_TopLevel() { CheckQuery( () => Cooks.GroupBy(c => c.Name), ""); }