Improve exception message text for queries that create new collections in projection

[bart-degreed]

When running the repro code below in EF Core 5 RC1 (v5.0.0-rc.1.20451.13), it produces the next error message:

System.InvalidOperationException: The query contains a final projection 'a => a.ArticleTags
    .Where(at => at.Tag.Value == "x")
    .OrderBy(at => at.Tag.Id)' to type 'IOrderedEnumerable<ArticleTag>'. 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>'.
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.VerifyReturnType(Expression expression, ParameterExpression lambdaParameter)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.VerifyReturnType(Expression expression, ParameterExpression lambdaParameter)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.VerifyReturnType(Expression expression, ParameterExpression lambdaParameter)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.NormalizeQueryableMethod(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPreprocessor.NormalizeQueryableMethod(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   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.EntityQueryProvider.Execute[TResult](Expression expression)
   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 EfCoreToListBug.Program.Main(String[] args) in D:\Source\Projects\EfCoreToListBug\EfCoreToListBug\Program.cs:line 31

When running in EF Core v3.1.8, it succeeds without error.

Repro code:

using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace EfCoreToListBug
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var dbContext = new AppDbContext())
            {
                dbContext.Database.EnsureDeleted();
                dbContext.Database.EnsureCreated();

                var tag = new Tag();
                var article = new Article();
                var articleTag = new ArticleTag {Article = article, Tag =  tag};

                dbContext.ArticleTags.Add(articleTag);
                dbContext.SaveChanges();

                var query =
                    dbContext.Articles.Select(a => new Article
                    {
                        ArticleTags = new HashSet<ArticleTag>(a.ArticleTags
                            .Where(at => at.Tag.Value == "x")
                            .OrderBy(at => at.Tag.Id))
                    });

                var results = query.ToList();
            }
        }
    }

    public class AppDbContext : DbContext
    {
        public DbSet<Article> Articles { get; set; }
        public DbSet<Tag> Tags { get; set; }
        public DbSet<ArticleTag> ArticleTags { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=sample.db");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ArticleTag>().HasKey(articleTag => new
            {
                articleTag.ArticleId, 
                articleTag.TagId
            });
        }
    }

    public abstract class Entity
    {
        public int Id { get; set; }
    }

    public class Article : Entity
    {
        public string Name { get; set; }

        public ISet<ArticleTag> ArticleTags { get; set; }
    }

    public class Tag : Entity
    {
        public string Value { get; set; }
    }

    public class ArticleTag
    {
        public int ArticleId { get; set; }
        public Article Article { get; set; }
        
        public int TagId { get; set; }
        public Tag Tag { get; set; }
    }
}

I believe that the "or some other mechanism" in the error message should allow the new HashSet<ArticleTag>(...) expression, which it fails to take into account. When replacing that with .ToHashSet() then the error goes away. But earlier versions of EF Core 3.x were unable to understand the .ToHashSet() call, which is why I'm using constructor syntax.

[ajcvickers]

@smitpatel Can we fix this for 5.0?

[smitpatel]

No.

[ajcvickers]

@smitpatel Okay, then we'll need to document it as a breaking change.

[smitpatel]

Exception message covers what needs to be done.

[AndriySvyryd]

@smitpatel It's still a breaking change

[smitpatel]

So a documented breaking change which user cannot utilize to find actual queries which would cause the issue, requiring the user to run the query which throws exception and evaluate the exception to figure out they actual hit the breaking change and take necessary action for the breaking change which is same as what is written in the error message. I don't see how it adds any value to any user. I am not gonna fight over this. @maumar - Can you add this "breaking change"?

[AndriySvyryd]

@smitpatel The documentation sets up some expectations for users planning to upgrade. It also provides some background and could prevent more issues being filed about it.

[bart-degreed]

Why was this closed? The bug has not been fixed yet, only the breaking change has been documented.

I think this either needs to be fixed in a future version or the error message needs to change because it is wrong: My code is doing what the error message suggests as a fix ("or some other mechanism"), so it should not be thrown in the first place.

[Mani4k]

Hi guys,
I'm trying to migrate our code from EF6 into EF Core 5 and have stuck getting same error as author of this topic.
The code that i'm using (part of it) is:

var usersQuery = (from u in RepositoryContext.Users
                              from n in RepositoryContext.NewsLetters.Where(x => x.IsSubscriber && u.Email.ToLower() == x.EmailAddress.ToLower())

                              let oldStyleResearches = (from ac in RepositoryContext.AssessmentCodes
                                                        join a in RepositoryContext.Assessments on ac.AssessmentKey equals a.AssessmentKey
                                                        join r in RepositoryContext.Research on a.AssessmentKey equals r.AssessmentKey
                                                        where ac.UserID == u.Id && a.CompletedDate != null
                                                        orderby a.CompletedDate descending
                                                        select new
                                                        {
                                                            Date = a.CompletedDate.Value,
                                                            Gender = r.Gender,
              
                                                        })
                              let newStyleResearches = (from ac in RepositoryContext.AssessmentCodes
                                                        join a in RepositoryContext.Assessments on ac.AssessmentKey equals a.AssessmentKey
                                                        join rr in RepositoryContext.ResearchResponses on a.AssessmentKey equals rr.AssessmentKey
                                                        where ac.UserID == u.Id && a.CompletedDate != null
                                                        orderby rr.CreatedOn descending
                                                        select new 
                                                        {
                                                            Date = rr.CreatedOn,
                                                            //I had to do some mappings like that as questions do not have common type shared between surveys...
                                                            Gender = rr.SurveyId == 1 ? RepositoryContext.ResearchAnswers.FirstOrDefault(x => x.ResponseId == rr.ID && x.QuestionId == 3).Value :
                                                                     rr.SurveyId == 2 ? RepositoryContext.ResearchAnswers.FirstOrDefault(x => x.ResponseId == rr.ID && x.QuestionId == 17).Value : string.Empty,
                                                        })
                              let researches = oldStyleResearches.Union(newStyleResearches).OrderByDescending(x => x.Date)

                              let firstNonNullWithGender = researches.FirstOrDefault(x => !string.IsNullOrEmpty(x.Gender))

                              select new GetMailChimpUserDatabaseResult
                              {
                                  Id = null,

                                  FirstName = string.Empty,
                                  LastName = string.Empty,
                                  Email = n.EmailAddress,
                                  Gender = firstNonNullWithGender != null ? firstNonNullWithGender.Gender : string.Empty,
                              });

            var result = usersQuery.ToList();

I think in our situation EF core does not like the use of let keywords, we have a lot of code like the one above where we used to use let in EF6 and that did great for reusability and readability of statements in "current" query. Is this pattern totally not supported in EF core or is it result of the change mentioned in the topic ?

[ajcvickers]

@Mani4k Please open a new issue and attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[bart-degreed]

@ajcvickers Can you reopen this issue?

[ajcvickers]

@bart-degreed Agreed after re-reading the exception message that it could be interpreted to include what your code does. I will re-open to consider making it clear in the message that this isn't supported.