Calling SelectMany from VB throws InvalidOperationException

[kev160967]

EFBug.zip

File a bug

There seems to be an issue with SelectMany when called from VB. The same queries work in C#. I've created an example using the Blogging database from here. I've added a property Posts to the Blog object, which returns a collection of the posts associated with a blog

I've attached a zipped solution that contains the VB project along with a file CreateDatabase.sql which will create the database and add a blog and two posts. The solution also contains a C# project that references the model defined in the VB project and executes the same query that fails in VB

The following query gives an error in VB:

Dim posts = model.Blogs.Where(Function(x) x.BlogID = 1).SelectMany(Function(x) x.Posts).ToList()

The same query works in C#:

var posts = session.Blogs.Where(x => x.BlogID == 1).SelectMany(x => x.Posts).ToList();

Other variants on the query fail in the same way, for example:

Dim posts = (From b In model.Blogs, p In b.Posts Where b.BlogID = 1 Select p).ToList()

The exception details are as follows

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression 'x => (IEnumerable<Post>)x.Posts' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessSelectMany(NavigationExpansionExpression source, LambdaExpression collectionSelector, LambdaExpression resultSelector)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at EFBug.Program.Main(String[] args) in C:\Temp\Saturn\EFBug\Program.vb:line 6

EF Core version: 6.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 10
IDE: Visual Studio 202 17.3.4

[kev160967]

Workaround: If you use DefaultIfEmpty() with the inner property, you don't get an error. So, for example, this fails:

Dim posts = model.Blogs.Where(Function(x) x.BlogID = 1).SelectMany(Function(x) x.Posts).ToList()

but this works:

Dim posts = model.Blogs.Where(Function(x) x.BlogID = 1).SelectMany(Function(x) x.Posts.DefaultIfEmpty()).ToList()

Similarly, the first call below fails, but the second one works:

Dim posts = (From b In model.Blogs, p In b.Posts Where b.BlogID = 1 Select p).ToList()
Dim posts = (From b In model.Blogs, p In b.Posts.DefaultIfEmpty() Where b.BlogID = 1 Select p).ToList()

[kev160967]

You can also work around it with

Dim posts = model.Blogs.Where(Function(x) x.BlogID = 1).SelectMany(Function(x) x.Posts.Where(function(x) True).ToList()

i.e., pretty much any where clause. I suspect there would be other ways to achieve the same thing