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),
"");
}
