EFCore 3.0 - LINQ expression 'Last()' could not be translated

[shvmgpt116]

I am running a simple application that is using LINQ 'Last()' to get the last row data for the Id column in a table. This used to work fine in EFCore 2.2.4. But while running this test in EFCore 3.0.0, I am getting below error-

The LINQ expression 'Last(DbSet)' 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.

Is this a bug or expected behavior with EFCore 3.0? If this is expected, is there any workaround for this.

Simple Test Application

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace ConsoleApp60
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (var db = new BloggingContext())
                {
                    db.Database.EnsureDeleted();
                    db.Database.EnsureCreated();
                    Blog blog1 = new Blog { Url = "http://blogs.msdn.com/adonet1", Title = "NetCore1" };
                    Blog blog2 = new Blog { Url = "http://blogs.msdn.com/adonet2", Title = "NetCore2" };
                    Blog blog3 = new Blog { Url = "http://blogs.msdn.com/adonet3", Title = "NetCore3" };
                    db.Blogs.Add(blog1);
                    db.Blogs.Add(blog2);
                    db.Blogs.Add(blog3);
                    db.SaveChanges();
                    int i = db.Blogs.Last().Id;        //Here I get the exception.
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
    public class Blog
    {
        public int Id { get; set; }
        public String Url { get; set; }
        public string Title { get; set; }
    }

    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=MySqlServerDB1;Integrated Security=True").UseLoggerFactory(null);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }
    }
}

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 System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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.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.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Last[TSource](IQueryable`1 source)
   at ConsoleApp60.Program.Main(String[] args) in C:\Users\shivamg.ORADEV\source\repos\ConsoleApp9\ConsoleApp9\Program.cs:line 31

Steps to reproduce

Simple run the provided test case with EFCore 3.0

Further technical details

EF Core version: Microsoft.EntityFrameworkCore 3.0.0 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: NET Core 3.0 Operating system: Windows IDE: Visual Studio 2019 16.4.2

[ErikEJ]

Did you have client evaluation disabled in 2.2.4?

[shvmgpt116]

@ErikEJ I see following logs being generated when executing LINQ 'db.Blogs.Last().Id' in EFCore 2.2.4.

Microsoft.EntityFrameworkCore.Query: Debug: Compiling query model: 
'(from Blog <generated>_1 in DbSet<Blog>
select [<generated>_1]).Last()'
Microsoft.EntityFrameworkCore.Query: Warning: Query: '(from Blog <generated>_1 in DbSet<Blog> select [<generated>_1]).Last()' uses First/FirstOrDefault/Last/LastOrDefault operation without OrderBy and filter which may lead to unpredictable results.
Microsoft.EntityFrameworkCore.Query: Debug: Optimized query model: 
'(from Blog <generated>_1 in DbSet<Blog>
select [<generated>_1]).Last()'
Microsoft.EntityFrameworkCore.Query: Warning: The LINQ expression 'Last()' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query: Debug: (QueryContext queryContext) => IEnumerable<Blog> _InterceptExceptions(
|__ source: IEnumerable<Blog> _TrackEntities(
|   |__ results: IEnumerable<Blog> _ToSequence(() => Blog Last(IEnumerable<Blog> _ShapedQuery(
|   |   |__ queryContext: queryContext, 
|   |   |__ shaperCommandContext: SelectExpression: 
|   |   |       SELECT [b].[Id], [b].[Title], [b].[Url]
|   |   |       FROM [Blogs] AS [b], 
|   |   |__ shaper: (MaterializationContext materializationContext) => 
|   |       {
|   |           instance = new Blog()
|   |           instance.<Id>k__BackingField = int TryReadValue(ValueBuffer materializationContext.get_ValueBuffer(), 0, Blog.Id)
|   |           instance.<Title>k__BackingField = string TryReadValue(ValueBuffer materializationContext.get_ValueBuffer(), 1, Blog.Title)
|   |           instance.<Url>k__BackingField = string TryReadValue(ValueBuffer materializationContext.get_ValueBuffer(), 2, Blog.Url)
|   |           return instance
|   |       }))), 
|   |__ queryContext: queryContext, 
|   |__ entityTrackingInfos: { itemType: Blog }, 
|   |__ entityAccessors: List<Func<Blog, object>> 
|       { 
|           Func<Blog, Blog>, 
|       }), 
|__ contextType: ConsoleApp60.BloggingContext, 
|__ logger: DiagnosticsLogger<Query>, 
|__ queryContext: queryContext)
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Opening connection to database 'MySqlServerDB1' on server '(localdb)\MSSQLLocalDB'.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Opened connection to database 'MySqlServerDB1' on server '(localdb)\MSSQLLocalDB'.
Microsoft.EntityFrameworkCore.Database.Command: Debug: Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [b].[Id], [b].[Title], [b].[Url]
FROM [Blogs] AS [b]
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (36ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [b].[Id], [b].[Title], [b].[Url]
FROM [Blogs] AS [b]
Microsoft.EntityFrameworkCore.Database.Command: Debug: A data reader was disposed.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Closing connection to database 'MySqlServerDB1' on server '(localdb)\MSSQLLocalDB'.
Microsoft.EntityFrameworkCore.Database.Connection: Debug: Closed connection to database 'MySqlServerDB1' on server '(localdb)\MSSQLLocalDB'.

I suppose this mean Last() cannot be evaluated server-side and is being evaluated client-side only. I am not sure how to disable/enable client-side evaluation.

[ErikEJ]

Yes, as you can see, all rows from your table a being selected! Not good! Disable client evaluation: https://docs.microsoft.com/en-us/ef/core/querying/client-eval#previous-versions

[shvmgpt116]

Using ".ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));" would throw an exception when trying to use client evaluation. I suppose your point is not to use client evaluation while retrieving the data from db. If I break the LINQ "db.Blogs.Last().Id;" into following- var blog = db.Blogs.ToList(); int i = blog.Last().Id; Would that not cause client evaluation? Please could you confirm?

[smitpatel]

You cannot do last without OrderBy. Please add OrderBy in your query before calling Last() Duplicate of #18211