EntityFrameworkCore 2.2 and MySql query could not be translated and will be evaluated locally

Question

I am trying to publish a self-contained app built with .net core sdk 2.2 to ubuntu 18.04-x64. The app connects to MySQL via Pomelo.EntityFrameworkCore.MySql and generally runs fine, but I have noticed a difference in the way EntityFrameworkCore builds the SQL queries. On my development machine the following code

        var parameters = new object[]
        {
            new MySqlParameter("count", count)
        };

        return _dbContext.Posts
            .OrderByDescending(x => x.DateCreated)
            .Select(x => new SuggestionsViewModel()
            {
                Id = x.Id,
                Title = x.Title

            }).FromSql(Sql.GetSuggestions, parameters);

translates to query

[INF] Executed DbCommand (0ms) [Parameters=[count='?' (DbType = Int32)], CommandType='"Text"', CommandTimeout='30']
SELECT `x`.`Title`, `x`.`DateCreated`, `x`.`Id` 
FROM ( SELECT
            x.Id,
            x.Title,
            x.DateCreated 
        FROM
            Posts x
        WHERE    
            x.Picked = 1
        ORDER BY
            rand()
        LIMIT
            @count ) AS `x` ORDER BY `x`.`DateCreated` DESC

where the query in the brackets is the constant Sql.GetSuggestions. In production only the inner query is sent to the database and a warning preceeds the sql log line:

[INF] Entity Framework Core 2.2.6-servicing-10079 initialized 'ApplicationDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: None
[WRN] The LINQ expression 'orderby [x].DateCreated desc' could not be translated and will be evaluated locally.
[INF] Executed DbCommand (1ms) [Parameters=[count='?' (DbType = Int32)], CommandType='"Text"', CommandTimeout='30']
    SELECT
        x.Id,
        x.Title,
        x.DateCreated 
    FROM
        Posts x
    WHERE    
        x.Picked = 1
    ORDER BY
        rand()
    LIMIT
        @count

The app is built and published with these commands:

dotnet build --configuration Release --runtime "ubuntu.18.04-x64" --force
dotnet publish --configuration Release --self-contained true --runtime "ubuntu.18.04-x64"

with the 2.2.110 .net core sdk

Now here is something strange: when I pull the repository to ubuntu and build on the server with the same SDK and the same commands, the query constructs as expected. My development machine is Win10Enterprise with IDE Visual Studio 2017.

What am I doing wrong?

Answer 1

I was able to reproduce the issue with the steps you provided. This might be an EF Core 2.2 issue.

---

It does work flawlessly with EF Core 3.1.7 and Pomelo 3.1.2, regardless if run under .NET Core 3.1 or 2.2.

---

Therefore, you should strongly consider upgrading Pomelo and EF Core to 3.1.x. Both are .NET Standard 2.0 compliant, meaning they are also compatible with .NET Core 2.2. For our compatibility matrix, see Compatibility: Dependencies.

I cannot upgrade: the Microsoft.AspNetCore.App 2.2 requires EF Core 2.2

You can use EF Core 3.1 in conjunction with ASP.NET Core 2.2. Take a look at the beginning on another answer by me.

Basically, the Microsoft.AspNetCore.App package is just a meta package, that references a lot of different packages, so that you don't have to do this by yourself. However, nobody is stopping you from referencing the actual packages manually or just overriding the default package versions referenced by Microsoft.AspNetCore.App with the ones you want. The first part of the linked answer demonstrates this.