Refrain from doing split query when not necessary

[roji]

Test Collection_projection_over_GroupBy_over_parameter runs the following LINQ query:

var validIds = new List<string> { "L1 01", "L1 02" };

return AssertQuery(
    async,
    ss => ss.Set<Level1>()
        .Where(l1 => validIds.Contains(l1.Name))
        .GroupBy(l => l.Date)
        .Select(g => new { g.Key, Ids = g.Select(e => e.Id) }),
    elementSorter: e => e.Key,
    elementAsserter: (e, a) =>
    {
        AssertEqual(e.Key, a.Key);
        AssertCollection(e.Ids, a.Ids);
    });

This produces the following SQL with single query (ComplexNavigationsCollectionsQuerySqlServerTest):

SELECT [t].[Date], [t0].[Id]
FROM (
    SELECT [l].[Date]
    FROM [LevelOne] AS [l]
    WHERE [l].[Name] IN (N'L1 01', N'L1 02')
    GROUP BY [l].[Date]
) AS [t]
LEFT JOIN (
    SELECT [l0].[Id], [l0].[Date]
    FROM [LevelOne] AS [l0]
    WHERE [l0].[Name] IN (N'L1 01', N'L1 02')
) AS [t0] ON [t].[Date] = [t0].[Date]
ORDER BY [t].[Date]

And the following two queries with split query (ComplexNavigationsCollectionsSplitQuerySqlServerTest):

SELECT [l].[Date]
FROM [LevelOne] AS [l]
WHERE [l].[Name] IN (N'L1 01', N'L1 02')
GROUP BY [l].[Date]
ORDER BY [l].[Date];

SELECT [t0].[Id], [t].[Date]
FROM (
    SELECT [l].[Date]
    FROM [LevelOne] AS [l]
    WHERE [l].[Name] IN (N'L1 01', N'L1 02')
    GROUP BY [l].[Date]
) AS [t]
INNER JOIN (
    SELECT [l0].[Id], [l0].[Date]
    FROM [LevelOne] AS [l0]
    WHERE [l0].[Name] IN (N'L1 01', N'L1 02')
) AS [t0] ON [t].[Date] = [t0].[Date]
ORDER BY [t].[Date];

There doesn't seem to be a good reason to actually split the query. I'm not sure what the characteristics of this scenario are, but we should investigate if there are other scenarios where we're needlessly doing split query.

[stevendarby]

Think this is a consequence of split queries always beginning with the initial set. A simpler example of this without a group by is:

var result = context.Blogs
    .Select(b => new { b.Id, b.Posts })
    .AsSplitQuery()
    .ToList();

SELECT [b].[Id]
FROM [Blogs] AS [b]
ORDER BY [b].[Id]

SELECT [p].[Id], [p].[BlogId], [p].[Title], [b].[Id]
FROM [Blogs] AS [b]
INNER JOIN [Post] AS [p] ON [b].[Id] = [p].[BlogId]
ORDER BY [b].[Id]

As with your example, the 2nd query gets everything you need and the first one seems kind of pointless. If you selected more than just the Id from Blog, the difference between the query in Single mode and the 2nd query in Split mode would start to be more obvious. (Though the splitting would still be overkill imo - but is consistent with the philosophy of split query always splitting on the first collection and not just when the collections would lead to a cartesian product.)

[roji]

Thanks for the simplification @stevendarby - yeah, I can see the connection with ours discussions around collection navigations and cartesian explosion vs. property duplication. We should definitely think a bit more about this.