Query: correlated collection optimization is broken for queries containing top level First/FirstOrDefault/Single/SingleOrDefault

[maumar]

Repro:

var result = ctx.Parents.Select(p =>
new
{
    Children = p.Children
}).FirstOrDefault();

Problem here is that when we rewrite QM to create query that fetches inner elements, we produce join between inner elements and cloned parent query model. However, if parent model contains result operators that change cardinality, we would clone it also and hence produce invalid QM.

[smitpatel]

In the presence of Single value from sequence result operator, we should not be doing any optimization. Rather than we should just use N+1 mode of evaluation since it would run 2 queries anyway. It gives us benefit that 2nd query is filtered based on a parameter rather than a join (also no order by is needed in 2nd query) which would be efficient than running a normal collection query.

[maumar]

@smitpatel correlated collections optimization doesn't work if the outer is using outer parameter, so in case of multi-level correlation we would be producing inefficient queries. We could make the optimization you suggest once/if #10877 is addressed

[maumar]

fixed in d787c13

[smitpatel]

Re-opening this. This is partially fixed. We need tracking issue to do outer parameter injection for single record collection query. Either we use this or file a new one. For include pipeline (perhaps older one) #2182

[maumar]

@smitpatel #10877 is already tracking this (see one of the comments)

[maumar]

Workaround:

First/FirstOrDefault:

var result = ctx.Parents.Select(p =>
new
{
    Children = p.Children
}).Take(1).ToList().FirstOrDefault();

Single/SingleOrDefault:

var result = ctx.Parents.Select(p =>
new
{
    Children = p.Children
}).Take(2).ToList().SingleOrDefault();