Nested projections via AsQueryable...ToList and Expression.Invoke

[syndicatedshannon]

It appears that EF Core 5 generates correctly-related (server side) SQL queries in the following two nested projections:

public static IEnumerable<BookViewModel> ProjectBooks(IQueryable<WorkOrderResource> books)
{
    Expression<Func<AuthorViewModel, AuthorStorageModel>> authorProjection = l => new AuthorViewModel { };
    return books.Select(b => new BookViewModel
    {
        Author = Expression.Invoke(authorProjection, b.PrimaryAuthor),
        Authors = b.Authors.AsQueryable().Select(authorProjection).ToList()
    };
}

Why does this work? Would you please point me to related documentation?

This was not intuitive to me. My expectation from past work with EF would be that both .Compile() and .ToList() would break EF Expressions.

I would especially like to confirm this is a supported feature. I was unable to find unit tests for this in the repo.

Previously, to address this need, walking ExpressionVisitor with code such as https://github.com/scottksmith95/LINQKit was required.

EF Core version: 5.0
Database provider: Oracle
Target framework: .NET 5.0
Operating system: Win10
IDE: Visual Studio 2019 16.3

[smitpatel]

As exception message indicates that query contains result of type IQueryable<QueryEntry> which is not allowed in final result.

The first 2 scenario you mentioned, first one does convert it to list by using ToList method and in second one navigatedEntity is not queryable type so final result is of enumerable type.

[syndicatedshannon]

My question must have been poorly worded. I'm not concerned about the Exception; I'm only looking for documentation on this feature.

I will edit the question above to remove discussion of the Exception, and move it below.

[syndicatedshannon]

Note that if you remove the "ToList()" in the example above, it no longer works, and EFCore throws an exception, recommending the expression be updated. Therefore, it appears this is an intended feature, but I can't find it documented anywhere.

System.InvalidOperationException: The query contains a projection '

   s => s.Entries
      .AsQueryable()
      .Select(q => new QueueEntry{})

' of type 'IQueryable'.
Collections in the final projection must be an 'IEnumerable' type such as 'List'.
Consider using 'ToList' or some other mechanism to convert the 'IQueryable' or 'IOrderedEnumerable' into an 'IEnumerable'.

[syndicatedshannon]

I've made a number of edits to the question that I hope clarify it. Would you please review before closing this question as resolved?

[smitpatel]

By default EF Core will try to translate every query it can. So there is no documentation for everything which is translated.
The only error is when materializing IQueryable as indicated in error message.

• The Author part works because it is single result.
• The Authors part work when it has ToList since you converted the source to Queryable using AsQueryable. Note that it is not about what is type of enumerable in the subquery exactly, by default when materializing enumerable EF Core always generates a list or concrete type of collection navigation when information is present. The issue happens without ToList that type of Authors in BookViewModel becomes IQueryable and we cannot materialize IQueryable objects.
• Compile.Invoke works for everything as long as the object being compiled is type of Expression<Func<>>

At the end of day it is all about what expression tree EF gets and what object it has to create. objects of IQueryable types are not possible to create and it throws exception with providing guidance what to do.

[syndicatedshannon]

Thank you, that does help clarify some things. I'd like to clarify "translate every query it can" if you don't mind.

If I understand you correctly, Expression<Func<>> containing IQueryable.Select.ToList and Compile.Invoke is working by design, and it is safe/supported for me to replace my usage of LinqKit with this approach today? I think I hear you saying it's OK to do this, and my code won't break tomorrow.

Also I know it's a broad statement, but I think it would really be helpful if the EFCore documentation contained a topic with examples on best practices for code/expression reuse and nested projections.

From my perspective as a developer, I wonder how I would have discovered EF Core supports this today, given it didn't work in EF or early EF Core versions and I don't believe it was announced in release notes? I wonder if I'm still missing other projection tricks to clean up my code that aren't just a question of software development experience, but rather "what EF Core can currently translate".

[smitpatel]

LINQ is an infinite problem space. Unlimited numbers of queries can be written by combing various different LINQ operators hence it makes impossible for us to define exactly what is supported.
e.g. A normal Join operation is supported but you cannot use a client side collection (rather than a server correlated table) in join or you cannot use a client side function in join key.
Which makes it very hard to define list of supported things compared to list of unsupported things. So if you write a query and it works then it is supported. When things don't work, we try to do one of the following

• Throw a useful error message indicating what users should do to remedy it. Above exception message is one of those kind where user can change their query to resolve the problem.
• Throw an error message with details why certain things cannot be translated and what is reason behind it. e.g. GroupJoin operator without SelectMany flattening. It doesn't have a server equivalent and there is no easy remedy. User would need to think how do they want to rewrite query to get results they want without using GroupJoin. We have docs for this here https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators#groupjoin
• Throw error message for features which is not implemented yet but we plan to support it in future. The error message either provide reference to tracking issue or says not supported yet.
• A hard to interpret error message when basically there is a bug in code base.

For things where user needs to rewrite query but is not trivial we do link to docs in exception message. For not yet implemented feature, tracking issues kinda serves as docs page.

So if you write a query and it works then it is supported and it will continue to be supported in future versions (especially if you are using version 3.1 or higher).

For best practices, we have docs https://docs.microsoft.com/en-us/ef/core/performance/efficient-querying which talks about performance specifically. Apart from that how to write query is personal preference of developer. As a query engine, what we see is an expression tree. How it is created doesn't matter to query engine so whichever way it is easy for the user to do it they are free to do so.

[syndicatedshannon]

Thank you. Based on your answer "if you write a query and it works then it is supported and it will continue to be supported in future versions." Because this works today it is supported.

Based on this I agree that EF Core itself is an infinite problem space by design, and this strategy is probably what led to my confusion.

I expected that behind the scenes the EFCore team defined specific supported scenarios in order to create a finite problem space, and I thought I would find that Compile...Invoke was added around version 3 but simply not added to documentation.

Still, I would love the specific topic of nested expressions to be mentioned in documentation. I think the means to do this are non-intuitive and I bet many developers would find it helpful to discover there is a way to do it. Per your exempli gratia regarding client side collection being supported or not, that is included in documentation, largely because it violates principle of least surprise... not because of an EF Core flaw, but just because it's not intuitive.

Regardless, this does answer my question, thank you so much.

[syndicatedshannon]

Please kindly consider my request for documentation on the topic.

[Dreamescaper]

Yeah, better documentation would be useful. I understand it's really hard to document everything possible, but anyway.

I was surprised to discover that I'm able to use methods invocations in projections in some cases in EF Core 5.
E.g.

    return DbContext.Books.Select(b => new BookViewModel
    {
        Author = MyNonExpressionMethod(b.Author.Name),
        Title = b.Title
    };

(It simply retrieves b.Author.Name from DB, and the executes MyNonExpressionMethod on each value after actual DB call).

But I wasn't able to find any docs or even release notes when that was done.

[smitpatel]

@Dreamescaper - That is not invocation in pure expression tree terms since there is not Invoke method. EF Core has allowed client eval in top level projection since very first version. We have extensive documentation regarding that https://docs.microsoft.com/en-us/ef/core/querying/client-eval

[AndriySvyryd]

cc: @roji

[roji]

Just for my 2 cents, I'm generally skeptical of documenting which constructs are translatable and which aren't, for the same reason @smitpatel detailed above in https://github.com/dotnet/efcore/issues/24991#issuecomment-850654091... I generally believe that when a query cannot be translated, our exception message should be good enough to convey what the problem is, and point the user in the right direction.

For example, if a user attempts to project IQueryable out, we currently throw an exception with the following message:

The query contains a projection 'b => b.Posts
    .AsQueryable()' of type 'IQueryable<Post>'. Collections in the final projection must be an 'IEnumerable<T>' type such as 'List<T>'. Consider using 'ToList' or some other mechanism to convert the 'IQueryable<T>' or 'IOrderedEnumerable<T>' into an 'IEnumerable<T>'.

So it seems like all the relevant information is being provided to the user. Adding a documentation page in addition to that doesn't seem very valuable; after all, users are likely to be interested in this topic only if they try to project an IQueryable - I'm not sure who it would help to have a long list of patterns which we can't translate.