Best practises for database switch

Radzen Studio Angular
1

Dear forum,
in my project I was using an MSSQL DB with enabled security.
Now I am going to deploy my application soon and for that I want to swap my data source for a Postgres DB.
However now radzen generates migrations with build errors in it:

Here is an excerpt from 00000000000000_CreateIdentitySchema

migrationBuilder.CreateTable(
            name: "AspNetRoleClaims",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", Npgsql.EntityFrameworkCore.PostgreSQL.Metadata.NpgsqlValueGenerationStrategy.SerialColumn),
                ClaimType = table.Column<string>(nullable: true),
                ClaimValue = table.Column<string>(nullable: true),
                RoleId = table.Column<string>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_AspNetRoleClaims", x => x.Id);
                table.ForeignKey(
                    name: "FK_AspNetRoleClaims_AspNetRoles_RoleId",
                    column: x => x.RoleId,
                    principalTable: "AspNetRoles",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

        migrationBuilder.CreateTable(
            name: "AspNetUserClaims",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", Npgsql.EntityFrameworkCore.PostgreSQL.Metadata.NpgsqlValueGenerationStrategy.SerialColumn),
                ClaimType = table.Column<string>(nullable: true),
                ClaimValue = table.Column<string>(nullable: true),
                UserId = table.Column<string>(nullable: false)
            },

...and another one from IdentityModelSnapshot:
protected override void BuildModel(ModelBuilder modelBuilder) { modelBuilder .HasAnnotation("ProductVersion", "1.0.0-rc3") .HasAnnotation("Npgsql:ValueGenerationStrategy", Npgsql.EntityFrameworkCore.PostgreSQL.Metadata.NpgsqlValueGenerationStrategy.SerialColumn);

My IDEs do not like the "Metadata" Property wich apparently does not exist thus the code cannot be compiled.

I double checked the database relations in the postgres db. They are the same as in the former MS SQL DB.
I am glad for any help.

Thanks

2

Not sure what is the build error. Can you post it?

3

image
image1378×113 4.85 KB

"nicht vorhanden" means not available

4

Make sure you have

<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.1.2" />

in your project file.

5

Yes that was the missing package. Thanks for the hint.

However I have another issue. I cannot add users to my application. The action fails with the following exception:

dotnet: fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
An unhandled exception has occurred while executing the request.
Npgsql.PostgresException (0x80004005): 42703: column u.FirstName does not exist
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 444
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1217
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.BufferlessMoveNext(DbContext _, Boolean buffer, CancellationToken cancellationToken)
at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) in C:\projects\npgsql-entityframeworkcore-postgresql\src\EFCore.PG\Storage\Internal\NpgsqlExecutionStrategy.cs:line 72
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncQueryingEnumerable1.AsyncEnumerator.MoveNext(CancellationToken cancellationToken) at System.Linq.AsyncEnumerable.FirstOrDefault_[TSource](IAsyncEnumerable1 source, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.TaskResultAsyncEnumerable1.Enumerator.MoveNext(CancellationToken cancellationToken) at System.Linq.AsyncEnumerable.SelectEnumerableAsyncIterator2.MoveNextCore(CancellationToken cancellationToken)
at System.Linq.AsyncEnumerable.AsyncIterator1.MoveNext(CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.AsyncLinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext(CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteSingletonAsyncQuery[TResult](QueryContext queryContext, Func2 compiledQuery, IDiagnosticsLogger1 logger, Type contextType)
at Microsoft.AspNetCore.Identity.UserManager1.FindByNameAsync(String userName) at Microsoft.AspNetCore.Identity.UserValidator1.ValidateUserName(UserManager1 manager, TUser user, ICollection1 errors)
at Microsoft.AspNetCore.Identity.UserValidator1.ValidateAsync(UserManager1 manager, TUser user)
at Microsoft.AspNetCore.Identity.UserManager1.ValidateUserAsync(TUser user) at Microsoft.AspNetCore.Identity.UserManager1.CreateAsync(TUser user)
at Microsoft.AspNetCore.Identity.UserManager1.CreateAsync(TUser user, String password) at RadzenPm.Controllers.ApplicationUsersController.Post(JObject data) in C:\Users\Jaworski\RiderProjects\RAD_PM\server\Controllers\ApplicationUsersController.cs:line 173 at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at System.Threading.Tasks.ValueTask1.get_Result()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeInnerFilterAsync()
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

I already deleted the asp.net authentication tables and reenabling radzen security but the issue still persists.

I am glad for any help.

Thank you

6

You may need to delete also server/Data/Migrations folder. These are the migrations needed to create security tables and you might have some old code related to SQL Server.