search

npgsql / efcore.pg

Entity Framework Core provider for PostgreSQL
PostgreSQL License
1.54k stars 226 forks source link

Npgsql interferes with SQL Server context #1654

Closed moredatapls closed 3 years ago

moredatapls commented 3 years ago

We're running two databases, one SQL Server and one PostgreSQL instance. We're already connecting to the SQL Server through EF Core. Now, we would like to add a second context that connects to the PostgreSQL server.

However, as soon as we add the Npgsql dependency to our project, strange exceptions happen when writing to the SQL Server through EF Core. The only change is the dependency, no other changes are made to our project. Example of one of the exceptions that happen:

fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'BusinessModelContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
       ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'UserToken' when IDENTITY_INSERT is set to OFF.

Our .csproj before the change (simplified, there are many other dependencies too):

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    ...
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.7" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.7">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.1.7" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.7" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite" Version="3.1.7" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.7">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    ...
  </ItemGroup>

</Project>

After the change:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    ...
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.7" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.7">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.1.7" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.7" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite" Version="3.1.7" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.7">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>

    <!-- The only change: -->
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.4" />
    ...
  </ItemGroup>

</Project>

Do you have an idea what could be the cause of the problems?

roji commented 3 years ago

You are likely specifying UseIdentityColumn in your model configuration - that extension method exists in both SqlServer and Npgsql, and the wrong version is getting resolved. Use that method with full qualification (e.g. NpgsqlPropertyBuilderExtensions.UseIdentityColumn(...) if you intend to apply to Npgsql) to disambiguate.

For more help, can you please post your model config?

moredatapls commented 3 years ago

Wow that was it indeed, seems to work now. The curse of extension methods! Are you aware of other possible conflicts or are there any resources on this?

roji commented 3 years ago

I've just now merged a patch to fix a very similar problem in migrations - see https://github.com/dotnet/efcore/issues/23456. Aside from UseIdentityColumn, IncludeProperties on indexes is also a culprit.