EF Core 5: Linq query works locally, but fails online (Azure)-CodePudding

I'm having issues with a Linq query that can't be translated, but before I go into detail on the code, I have a general question.

My query works if I test my Web Api locally with IIS, but when I publish it to Azure, it gives me an error, saying that it can't be translated.

So my question is, what could be the reason for this? How is it possible, that it produces different results after publish? It's really annoying to make something work only to find out later that it doesn't work when published. This way I will have to publish all of the time to make sure that it actually works.

For the actual problem, my query looks like this:

    var innerJoinQuery =
    from user in _context.Users
    join historyentry in _context.ResourceHistory on user.UserId equals historyentry.UserId
    join resource in _context.UserResource on historyentry.UserId equals resource.UserId
    join userProfile in _context.UserProfiles on resource.UserId equals userProfile.UserId
    where historyentry.ShortName.Equals(shortName)
                    && historyentry.CreatedUtc > startUtc 
                    && historyentry.CreatedUtc < endUtc
    select new BoardEntry()
    {
        UserId = user.UserId,
        ResourceShortName = resource.ShortName,
        ResourceDisplayName = resource.DisplayName,
        UserDisplayName = userProfile.DisplayName,
        Amount = historyentry.Amount
    };

This is the error I get:

The LINQ expression 'ROW_NUMBER() OVER(PARTITION BY u2.UserId ORDER BY u2.UserId ASC, r0.HistoryEntryId ASC, u3.ResourceId ASC, u4.ProfileId ASC)' 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'.

Setup:

EF Core 5.0.17

MariaDB 10.3

Pomelo.EntityFrameworkCore.MySql 5.0.4

CodePudding user response：

Staring from EF Core 5.0 for subqueries which has row count limitation (Take(1), FirstOrDefault, etc.) instead of CROSSS/OUTER APLLY, LINQ Translator generates join to query with Window function ROW_NUMBER. Not all versions of MariaDB supports Window functions and probably Pomelo.EntityFrameworkCore.MySql provider has bug in server version autodetection for MariaDB, so try to specify version explicitly:

builder.UseMySql(connectionString, serverVersion);