CODEWITHSUNDEEP
c#dockerasp.net-coreef-core-3.0ef-core-3.1
Saurabh Pati
Saurabh Pati

Reputation: 847

.NET Core API: Invalid column name '__roleName_0' on upgrading to EF Core 3.1 on docker container running on Linux VM

Some context of the issue:

We have recently upgraded to .NET Core 3.1 from 2.2 and with it also upgraded to EF Core 3.1 from 2.2 and there are few APIs which are failing or have some weird issues which were not there until now. I want help to understand such issues in order to fix them.

Some points of observations to note:

  1. The issue does not occur if I downgrade EF Core 3.1 to 2.2
  2. The issue does not appear when we test API locally, it only appears when we deploy the API.
  3. For deployment, we use a docker container on a linux VM. I have shared docker file below with other details.

Description

Error type: SqlException Error message: Invalid column name '__roleName_0'

public async Task<List<UserView>> GetUsersAsync(string roleName, int offset, int? limit)
{
    var query = _dbContext.UserView.AsNoTracking().Where(u => u.UserRole.Any(ur => ur.Role.Name == roleName));

    var userResult = await query.ToListAsync();

    return userResult;
}

So as you can see in GetUsersAsync function, It has one db operations:

  1. Get users filtered by predicate belonging to that role.

Here is the generated sql from our logs for the following operations:

Get users filtered by predicate belonging to that role.

Executed DbCommand (177ms) [Parameters=[@__roleName_0='?' (Size = 4000)]

SELECT <ColumnNames>
FROM 
(
    SELECT <ColumnNames>
    FROM [V_User] AS [v]
    WHERE (EXISTS (
        SELECT 1
        FROM [UserRole] AS [u]
        INNER JOIN [Role] AS [r] ON [u].[RoleId] = [r].[Id]
        WHERE [v].[Id] = [u].[UserProfileId]) AND ([r].[Name] = @__roleName_0)
)

For some reason EF Core is perceiving the parameter __roleName_0 as column which seems to be the problem. We have tried several resources including reading the breaking changes introduced in EF Core 3.0/3.1 but could not get what is the reason for this issue. Please help.

Other technical details:

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.2" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.2">
<PackageReference Include="IdentityServer4.EntityFramework" Version="3.1.2" />
<PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="3.1.2" />

Dockerfile:

FROM mcr.microsoft.com/dotnet/core/aspnet:3.1.2 AS base
WORKDIR /app
EXPOSE 80

FROM mcr.microsoft.com/dotnet/core/sdk:3.1 AS build
WORKDIR /src
COPY <ProjectName>/<ProjectName>.csproj <ProjectName>/
RUN dotnet restore <ProjectName>/<ProjectName>.csproj
COPY . .
WORKDIR /src/<ProjectName>
RUN dotnet build <ProjectName>.csproj -c Release -r ubuntu.16.04-x64 -f netcoreapp3.1 -o /app

FROM build AS publish
RUN dotnet publish <ProjectName>.csproj -c Release -r ubuntu.16.04-x64 -f netcoreapp3.1 --self-contained -o /app

FROM base AS final
WORKDIR /app
COPY --from=publish /app .
ENTRYPOINT ["dotnet", "<ProjectName>.dll"]

Upvotes: 4

Views: 180

Answers (1)

Timir Bhingradiya
Timir Bhingradiya

Reputation: 53

This behavior might be related to how EF Core 3.1 handles query translation and parameter naming compared to EF Core 2.2. One potential workaround you can try is explicitly specifying the parameter type when constructing the query. Here's an updated code of your GetUsersAsync method that includes this change:

public async Task<List<UserView>> GetUsersAsync(string roleName, int offset, int? limit)
{
    var parameter = new SqlParameter("@roleName", roleName); // Explicitly specify parameter name and type
    var query = _dbContext.UserView.AsNoTracking()
        .FromSqlInterpolated($"SELECT * FROM UserView WHERE EXISTS (SELECT 1 FROM UserRole WHERE UserView.Id = UserRole.UserProfileId AND UserRole.RoleId = Role.Id AND Role.Name = {parameter})")
        .Include(u => u.UserRole);

    var userResult = await query.ToListAsync();

    return userResult;
}

In this, I've used FromSqlInterpolated to construct the SQL query, and I explicitly specified the parameter name and type using SqlParameter. This method can sometimes help in cases where EF Core's query translation behavior causes unexpected issues.

I hope this works.

Upvotes: 1

Related Questions