Query: Flatten out subquery models at query optimizer

[smitpatel]

  'from Customer c in 
      from Customer c in DbSet<Customer>
      where c.City == "Seattle"
      order by c.CustomerID asc
      select c
  where c.CustomerID == "ALFKI"
  select c'

Query model like above can be optimized by merging the bodyclauses.
Points to consider:

• Inner selector is selecting query source directly.
• No multiple from clauses inside subquery
• Presently ReLinq blocks lifting of subquery if there is orderby clause due to possible ambiguity in ordering. We can still lift if only one (inner or outer) contains OrderBy clause.
• outer selector can be anything.
• No result operator on subquery.

[BladeWise]

Would this scenario be covered by such optiimization?

var entities = context.Set<Entity>();

var info = entities.Select(x => new
                                {
                                   MaxId = entities.Max(x => x.Id),
                                   Count = entities.Count()
                                })
                   .FirstOrDefault();

In this case the generated query is

SELECT TOP 1
     (SELECT MAX(Id) FROM Entities),
     (SELECT COUNT(*) FROM Entities)
FROM Entities

while the optimal result would be

SELECT TOP 1
     MAX(ID),
     COUNT(*)
FROM Entities

[smitpatel]

@maumar - Assigning this to you as this is lifting of order by.

[maumar]

fixed in f022d66