Type-Generic Where Clause unable to be translated when using projections (EF Core 3.1.1)

[tj111]

When trying to access a defined property on a generic, projected type, the object in the Where clause lambda is unable to be translated.

public virtual async Task<IEnumerable<T>> GetAsync(IEnumerable<Guid> ids)
    => await this.repository.Read().Where(r => r.Id.ToString().Contains("0")).ToListAsync();

Outputs:

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression 'DbSet<Person>
    .Where(p => new Person{ 
        ContactNumber = p.ContactNumber, 
        Email = p.Email, 
        FullName = p.FullName, 
        Id = p.Id 
    }
    .Id.ToString().Contains("0"))' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
  Source=Microsoft.EntityFrameworkCore

Steps to reproduce

//service class, either method fails
	public abstract class Service<T> : IService<T> where T : class, ICommonModel {
		protected readonly IRepository<T> repository;

		public Service(IRepository<T> repository)
			=> this.repository = repository;

		public virtual async Task<IEnumerable<T>> GetAsync(IEnumerable<Guid> ids)
			=> await this.repository.Read().Where(r => ids.Contains(r.Id)).ToListAsync();

		public virtual async Task<IDictionary<Guid, T>> GetDictionaryAsync(IEnumerable<Guid> ids)
			=> await this.repository.Read().Where(r => r.Id.ToString().Contains("0")).ToDictionaryAsync(i => i.Id, i => i);
	}
}

//repo class
	public abstract class Repository<TCommon, TData> : IRepository<TCommon>
		where TCommon : class, ICommonModel
		where TData : class, IDataModel {

		protected AppContext Context { get; }

		protected IMapper Mapper { get; }

		public Repository(AppContext context, IMapper mapper) {
			this.Context = context;
			this.Mapper = mapper;
		}

		public virtual IQueryable<TCommon> Read()
			=> this.Context.Set<TData>().ProjectTo<TCommon>(this.Mapper.ConfigurationProvider);

	}
}

//interfaces
	public interface ICommonModel {
		Guid Id { get; set; }
	}


	public interface IDataModel {

		Guid Id { get; set; }
	}
}

Stack Trace:

System.InvalidOperationException
  HResult=0x80131509
  Message=The LINQ expression 'DbSet<Person>
    .Where(p => new Person{ 
        ContactNumber = p.ContactNumber, 
        Email = p.Email, 
        FullName = p.FullName, 
        Id = p.Id 
    }
    .Id.ToString().Contains("0"))' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
  Source=Microsoft.EntityFrameworkCore
  StackTrace:
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   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__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.<ToDictionaryAsync>d__90`3.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Denali.Services.Service`1.<GetDictionaryAsync>d__9.MoveNext() in C:\Users\tleahy\source\repos\Denali.API\Services\Service.cs:line 42

  This exception was originally thrown at this call stack:
	Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall.__CheckTranslated|8_0(Microsoft.EntityFrameworkCore.Query.ShapedQueryExpression, ref Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<>c__DisplayClass8_0)
	Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(System.Linq.Expressions.MethodCallExpression)
	Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(System.Linq.Expressions.MethodCallExpression)
	System.Linq.Expressions.MethodCallExpression.Accept(System.Linq.Expressions.ExpressionVisitor)
	Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(System.Linq.Expressions.MethodCallExpression)
	Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(System.Linq.Expressions.MethodCallExpression)
	System.Linq.Expressions.MethodCallExpression.Accept(System.Linq.Expressions.ExpressionVisitor)
    ...
    [Call Stack Truncated]

Further technical details

EF Core version: 3.1.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows
IDE: Visual Studio 2019 16.4.3

[ajcvickers]

@maumar to take a look.

[ajcvickers]

Pinging @maumar

[maumar]

Seems to be working in current master.
I'm using the following repro:

        [ConditionalFact]
        public void Repro19679()
        {
            using var ctx = new Context19679();
            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();

            var query = ctx.People.Where(p => new Person19679
            {
                ContactNumber = p.ContactNumber,
                Email = p.Email,
                FullName = p.FullName,
                Id = p.Id
            }.Id.ToString().Contains("0")).ToList();
        }

        public class Context19679 : DbContext
        {
            public DbSet<Person19679> People { get; set; }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer(@"Server=.;Database=Repro19679;Trusted_Connection=True;MultipleActiveResultSets=True");
            }
        }

        public class Person19679
        {
            public Guid Id { get; set; }
            public string ContactNumber { get; set; }
            public string Email { get; set; }
            public string FullName { get; set; }
        }

which yields the following sql:

SELECT [p].[Id], [p].[ContactNumber], [p].[Email], [p].[FullName]
FROM [People] AS [p]
WHERE CHARINDEX(N'0', CONVERT(VARCHAR(36), [p].[Id])) > 0

[maumar]

@tj111 is there more to your query? Translation for ToString should have been present in 3.1.1, so likely something else is wrong. Ideally, please submit full runnable repro, so it's easier for us to pin it down.

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

[NetTecture]

WTF? This is one critical issue and it is super easy reproduced in ef 3.1.1. I am fighting this all the time:

---> System.InvalidOperationException: The LINQ expression 'DbSet
.Where(o => !(o.BIsDeleted) == True)
.Where(o => new Document{
Created = o.DCreateDate,
Description = o.SDescription,
FileType = o.SMimeType,
Id = o.PkOBDocumentID,
Identity = o.GIdentity,
IsDeleted = o.BIsDeleted,
IsVirtual = o.BIsVirtual,
Name = o.SOrigFilename,
Size = o.ISizeInkBytes
}
.Identity == __key_0)' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Saying "Seems to be working in current master." is an insult to everyone who has to ship something in the timeframe BEFORE 5.0 some point in half a year. This is a regression from 2.2 down into 3.1. it is possibly FIXED in 5.0, but that fix is half a year down the road.

Btw., a repro does not even need a database as the test never executed - it blows in the translation phase before opening a database connection.

[smitpatel]

May be every issue critical issue.

[NetTecture]

The funny thing with EfCore is that the moment you actually try to use it you find out that it is so full of bugs it is not even an alpha. Odata was bad for a time, but the EfCore puts up a new fight on making sure that everyone realizes how much behind even Ef efcore is not - what is it? 10 years?
Have you see n the amazing number of bugs to be fixed next release? Hint: NOTHING. The team is happil working on the next major release.

2.1: Wait .
2.2: Wait
3.0: Wait
3.1: Wait
5.0? Will it finally handle simple LINQ cases?

All the while Ef works and works and works and chugs along. And people get told "hey, we fixed that, it does not happen in the branch to be released in more than half a year, you know, go and play with dolls".

A case for HR, it seems - there is somethign VERY odd with the way the EfCore teams hadnels this. Paid by Oracle to make MS look bad?

[tj111]

@maumar your example is using a defined type. If you abstract your person object to a generic the query will fail. And I'm starting to agree with @NetTecture, we are really starting to regret using EF Core for the ORM for a product that is supposed to be shipping this summer. We will spend more time fighting EF than writing functional code on a lot of our development tasks, and it seems there is no hope to getting any issues addressed.

[NetTecture]

@tj111

I have to disagree with you here. There is hope. Hope in the name of 5.0. As someone from the financial markets let me tell you why there is hope. HOPE DIES LAST. When you have moved off to a better product and EfCore is not fixed in 10 years, there STILL is hope left.

And this is the ONLY reason why there is still hope.

Btw., in another thread - #19830 - @ajcvickers said:

"I talk to many developers using EF Core, both posting on here, and big customers running serious apps in major companies (which are sometimes the same)."

I find that extremely hard to believe given that it does pretty much not matter what I try, if it is not on the level of Hello World, it blows.

[smitpatel]

Duplicate of #19087

[NetTecture]

And the amazing thing here is: This is a one line change with unit tests that was merged in in December and is not in any bug fix. Maybe there would be less bug reports if bug fixes would be deployed? Oh, common sense, you are sooooo lost.