Openiddict-core: Please Help... OpenIddict tables aren't presented in migrations (Dot net core)

[robertcck]

Hi all,

I am having the issue as described in the title.
My solution has two projects, DAL and Web

DAL

• Contains the application model and Identity Model (ApplicationUser and Application Role)
• Contains the Migrations
• Contains ApplicationDbContext.
• Its referenced by Web proj

Web

• Using OpenIddict
• Angular 4 app

The app is currently working but I have a very annoying problem in which OpenIddict tables aren't being included in the Migrations.

Can anyone please help. I am stuck on this for half a day googling and finding no solution.

However, during my research.... I did find an Asp net core template from github repo which has a similar setup like mine (DAL and WEB) with OpenIddict table included in Migration when I run it. (dotnet migrations add Initial). I downloaded the code and try to compare it side by side and still can't see anything which can help fixing my issue.

Thanks and looking forward for any suggestions
I can provide code snippet if needed.

[mattburrell]

Have you run Add-Migration to create the new migration for OpenIddict?

[robertcck]

@mattburrell I ran Add-Migration for my applicationContext and I thought this is all I have to do because OpenIddict is configured for the ApplicationDbContext

Please correct me if I am wrong. Would be great if you can clarify what you mean or show me the code if I do understand is incorrectly.

Many thanks

[mattburrell]

@robertcck can you confirm that one of your migrations looks similar to this?

namespace AuthorizationServer.Migrations
{
    public partial class OpenIddict : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "OpenIddictApplications",
                columns: table => new
                {
                    Id = table.Column<string>(nullable: false),
                    ClientId = table.Column<string>(nullable: true),
                    ClientSecret = table.Column<string>(nullable: true),
                    DisplayName = table.Column<string>(nullable: true),
                    LogoutRedirectUri = table.Column<string>(nullable: true),
                    RedirectUri = table.Column<string>(nullable: true),
                    Type = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_OpenIddictApplications", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "OpenIddictScopes",
                columns: table => new
                {
                    Id = table.Column<string>(nullable: false),
                    Description = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_OpenIddictScopes", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "OpenIddictAuthorizations",
                columns: table => new
                {
                    Id = table.Column<string>(nullable: false),
                    ApplicationId = table.Column<string>(nullable: true),
                    Scope = table.Column<string>(nullable: true),
                    Subject = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_OpenIddictAuthorizations", x => x.Id);
                    table.ForeignKey(
                        name: "FK_OpenIddictAuthorizations_OpenIddictApplications_ApplicationId",
                        column: x => x.ApplicationId,
                        principalTable: "OpenIddictApplications",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateTable(
                name: "OpenIddictTokens",
                columns: table => new
                {
                    Id = table.Column<string>(nullable: false),
                    ApplicationId = table.Column<string>(nullable: true),
                    AuthorizationId = table.Column<string>(nullable: true),
                    Subject = table.Column<string>(nullable: true),
                    Type = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_OpenIddictTokens", x => x.Id);
                    table.ForeignKey(
                        name: "FK_OpenIddictTokens_OpenIddictApplications_ApplicationId",
                        column: x => x.ApplicationId,
                        principalTable: "OpenIddictApplications",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                    table.ForeignKey(
                        name: "FK_OpenIddictTokens_OpenIddictAuthorizations_AuthorizationId",
                        column: x => x.AuthorizationId,
                        principalTable: "OpenIddictAuthorizations",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateIndex(
                name: "IX_OpenIddictApplications_ClientId",
                table: "OpenIddictApplications",
                column: "ClientId",
                unique: true);

            migrationBuilder.CreateIndex(
                name: "IX_OpenIddictAuthorizations_ApplicationId",
                table: "OpenIddictAuthorizations",
                column: "ApplicationId");

            migrationBuilder.CreateIndex(
                name: "IX_OpenIddictTokens_ApplicationId",
                table: "OpenIddictTokens",
                column: "ApplicationId");

            migrationBuilder.CreateIndex(
                name: "IX_OpenIddictTokens_AuthorizationId",
                table: "OpenIddictTokens",
                column: "AuthorizationId");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "OpenIddictScopes");

            migrationBuilder.DropTable(
                name: "OpenIddictTokens");

            migrationBuilder.DropTable(
                name: "OpenIddictAuthorizations");

            migrationBuilder.DropTable(
                name: "OpenIddictApplications");
        }
    }
}

Then you would run the EF command Update-Database (assuming you are using Visual Studio) or dotnet ef database update if you're using the CLI.

[robertcck]

@mattburrell

Thanks for your reply.

After II ran Add-Migration nameOfMigration
I will only get my application specific table and OpenIddict tables are not included in the migration. For some reason it's not included

As mentioned on my first post, my web project configured to use OpenIddict and the DbContext from DAL project

The dal project has the identity as well as application entity models and the dbcontext

Question: how can I run migration just for OpenIddict like what you had?
Ideally i can have them within my application migration but I guess a separed one is acceptable

[mattburrell]

@robertcck could you share your setup code? I assume you are calling options.UseOpenIddict(); in your services.AddDbContext<ApplicationDbContext> setup?

[robertcck]

@mattburrell

Yes, Here is the setup. Please let me know if you need to see anything else

This is the setup in my webApplication Project
```c#
// Add framework services.
services.AddDbContext(options =>
{
options.UseSqlServer(Startup.Configuration.GetConnectionString("Default"), b => b.MigrationsAssembly("webapplication"));
options.UseOpenIddict();
});

// For api unauthorised calls return 401 with no body
services.AddIdentity(options =>
{
options.Password.RequiredLength = 4;
options.Password.RequireNonAlphanumeric = false;
options.Password.RequireDigit = false;
options.Password.RequireUppercase = false;
options.Cookies.ApplicationCookie.AutomaticChallenge = false;
options.Cookies.ApplicationCookie.LoginPath = "/login";
options.Cookies.ApplicationCookie.Events = new CookieAuthenticationEvents
{
OnRedirectToLogin = ctx =>
{
if (ctx.Request.Path.StartsWithSegments("/api") &&
ctx.Response.StatusCode == (int)HttpStatusCode.OK)
{
ctx.Response.StatusCode = (int)HttpStatusCode.Unauthorized;
}
else if (ctx.Response.StatusCode == (int)HttpStatusCode.Forbidden)
{
ctx.Response.StatusCode = (int)HttpStatusCode.Forbidden;
}
else
{
ctx.Response.Redirect(ctx.RedirectUri);
}
return Task.FromResult(0);
}
};
})
.AddEntityFrameworkStores()
.AddUserStore>()
.AddRoleStore>()
.AddDefaultTokenProviders();

services.Configure(options =>
{
options.ClaimsIdentity.UserNameClaimType = OpenIdConnectConstants.Claims.Name;
options.ClaimsIdentity.UserIdClaimType = OpenIdConnectConstants.Claims.Subject;
options.ClaimsIdentity.RoleClaimType = OpenIdConnectConstants.Claims.Role;
});
// Register the OpenIddict services.
services.AddOpenIddict()
// Register the Entity Framework stores.
.AddEntityFrameworkCoreStores()

            // Register the ASP.NET Core MVC binder used by OpenIddict.
            // Note: if you don't call this method, you won't be able to
            // bind OpenIdConnectRequest or OpenIdConnectResponse parameters.
            .AddMvcBinders()

            // Enable the token endpoint.
            .EnableTokenEndpoint("/connect/token")

            // Enable the password and the refresh token flows.
            .AllowPasswordFlow()
            .AllowRefreshTokenFlow()

            // During development, you can disable the HTTPS requirement.
            .DisableHttpsRequirement()

            // Register a new ephemeral key, that is discarded when the application
            // shuts down. Tokens signed using this key are automatically invalidated.
            // This method should only be used during development.
            .AddEphemeralSigningKey();
        return services;
    }

```

[mattburrell]

@robertcck I think you are probably running into a quirk with EF Core. Have you tried deleting your database, deleting all your migrations and adding the initial migration again?

[robertcck]

@mattburrell
good point.... i will do that and see if this resolve this issue.

I will try and let you know my result.
Thanks again 👍

[Bartmax]

shouldn't services.AddDbContext( .. be services.AddDbContext<ApplicationDbContext>(... ?
Also, you DbContext is inheriting from IdentityDbContect?
like this:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
...
}

[robertcck]

@Bartmax Thanks for you reply

Yes, that was a missing bit on my code above. I have set it up like this
services.AddDbContext(options =>
{
options.UseSqlServer(Startup.Configuration.GetConnectionString("Default"), b => b.MigrationsAssembly("webapplication"));
options.UseOpenIddict();
});

Yes ApplicationDbContext is inheriting from IdentityDbContext
public class ApplicationDbContext : IdentityDbContext
{
...
}

[kevinchalet]

@robertcck did moving the context class back to your main project helped work around this issue?

If it still doesn't work, you can try to register the OpenIddict entities in the OnModelCreating method:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.UseOpenIddict();

    // Customize the ASP.NET Identity model and override the defaults if needed.
    // For example, you can rename the ASP.NET Identity table names and more.
    // Add your customizations after calling base.OnModelCreating(builder);
}

[robertcck]

@PinpointTownes Thanks for your reply and your solution works. I have thought about this before but never have done it.

Here are the steps i did to fix the issue

• Added OpenIddict packages to DAL project
• Used @PinpointTownes solution above
• Deleted all tables in database
• Delete all existing migrations
• Run Add-Migration Initial

Thanks for everyone.
A big thank you for @mattburrell and @PinpointTownes 👍

[kevinchalet]

Glad it worked for you @robertcck.

I'll update the README to mention that registering the OpenIddict entities directly from the context is required when it's part of a separate project.

That said, it really sounds like an EF bug/limitation. Could you please open a new ticket in the EF repo to track it? Thanks!

[Bartmax]

I don't think it's a requirement. I have an ApplicationDbContext on another project defined as:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {

    }
    // ...
}

and works just fine.

[kevinchalet]

@Bartmax really strange. And you don't override OnModelCreating nor OnConfiguring?

[Bartmax]

this is my override
c# protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<SearchLog>(); modelBuilder.Entity<SuggestLog>(); modelBuilder.Entity<DetailLog>(); base.OnModelCreating(modelBuilder); }

[kevinchalet]

@Bartmax hum. What's the exact EF version you're using?

[Bartmax]

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

  <PropertyGroup>
    <TargetFramework>netstandard1.4</TargetFramework>
    <PackageTargetFallback>$(PackageTargetFallback);dotnet5.6;portable-net45+win8</PackageTargetFallback>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="1.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="1.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="1.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="1.1.2" />
    <PackageReference Include="Newtonsoft.Json" Version="9.0.1" />
    <PackageReference Include="OpenIddict.Models" Version="1.0.0-beta2-0614"></PackageReference>
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.1" />
  </ItemGroup>
</Project>

[kevinchalet]

@robertcck when you have a minute, please share your .csproj/EF Core versions :smile:

[robertcck]

@Bartmax @PinpointTownes

As @mattburrell said
It should just work because I did see another project from Github somewhere that works and that's why I found it strange too.

However when I think about it. I think its probably something to do with my Nuget.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <config>
    <add key="globalPackagesFolder" value="./packages" />
  </config>
  <packageSources>
    <add key="NuGet" value="https://api.nuget.org/v3/index.json" />
    <add key="aspnet-contrib" value="https://www.myget.org/F/aspnet-contrib/api/v3/index.json" />
    <add key="AspNetCore" value="https://dotnet.myget.org/F/aspnetcore-ci-dev/api/v3/index.json" />
    <add key="nuget.org" value="https://api.nuget.org/v3/index.json" protocolVersion="3" />
    <add key="dotnet-core" value="https://dotnet.myget.org/F/dotnet-core/api/v3/index.json" />
  </packageSources>
</configuration>

I configured my solution to download Nuget packages into ./packages folder include OpenIddict. Perhaps when I run the EFCore Migration it could not found my OpenIddict dll since it is still looking into my default Nuget packages folder?

Here is my .csproj
Web.proj

```

netcoreapp1.1
portable
true
Exe
1.1.1
true
$(PackageTargetFallback);dotnet5.6;portable-net45+win8

All


All

Always

[Bartmax]

Perhaps when I run the EFCore Migration it could not found my OpenIddict dll since it is still looking my default Nuget packages folder?

you can try removing the key, restore and see if that is the issue. (don't think it is tho but it's worth a shot)

[robertcck]

@Bartmax

What do you mean by removing the key? Do you mean the Nuget.config?

[Bartmax]

yes removing this:

<add key="globalPackagesFolder" value="./packages" />

[robertcck]

I could give it a shot

[robertcck]

@PinpointTownes @Bartmax
I removed the key, run dotnet restore and also removed modelBuilder.UseOpenIddict();

Migration script is dropping OpenIddict ><

[Bartmax]

the only thing that I see different from my startup is:
C# services.AddDbContext<ApplicationDbContext>(options => { options.UseSqlServer(Startup.Configuration.GetConnectionString("Default"), b => b.MigrationsAssembly("webapplication")); options.UseOpenIddict(); });
i don't have options.UseOpenIddict(); on mine on the services.AddDbContext call.

[Bartmax]

Maybe you are missing the constructor with the options or the call to base():

public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {

        }

[robertcck]

@Bartmax

No I did have the call to base()
`
public ApplicationDbContext(DbContextOptions options): base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.UseOpenIddict();

        #region Security Tables
        modelBuilder.Entity<ApplicationUser>().ToTable("AspNetUsers");

        modelBuilder.Entity<ApplicationRole>().ToTable("AspNetRoles");
        #endregion

....
`

[robertcck]

Adding modelBuilder.UseOpenIddict() to my ApplicationDbContext override works
That was the solution provided by @PinpointTownes

[kevinchalet]

i don't have options.UseOpenIddict(); on mine on the services.AddDbContext call.

@Bartmax wait, where do you register the OpenIddict entities, then? :sweat_smile:

[zinczinc]

@PinpointTownes : Instead of code-first, where can I find the SQL Server script to create the OpenIddict tables directly in the database?

[kinosang]

@zinczinc You can use LocalDB or install a SQL Server locally, use the ef migration to create the tables, and use a tool to dump the SQL script from LocalDB (or local-installed SQL Server)

[zinczinc]

@kinosang : I know I can do that (code-first), then get the tables after db generation. I am just a bit lazy maybe. I was wondering if there are readily available sql scripts of those tables somewhere so I can just run the scripts. Going all the way to creating a whole solution in VS just to get the tables !!!! Maybe someone can post the sql scripts...-:)

[kevinchalet]

@zinczinc the whole point of leveraging an ORM like EF Core is that you don't have to deal with SQL and all its dialects yourself.

If we wanted to provide SQL tables creation scripts, we'd need one for MSSQL, one for MySQL, one for Sqlite, one for PostgreSQL, one for Oracle... it would be a nightmare.

The steps listed by @kinosang are your best chances to get the raw SQL you're looking for.

[dcworldwide]

dding modelBuilder.UseOpenIddict() to my ApplicationDbContext override works

Is this required in latest (2.0.1)?

Is it documented in the main project readme.md? I didn't see it. It wasn't obvious in the docs either

[kinosang]

@dcworldwide didn't you see options.UseOpenIddict() in the README.md nor the docs?

[dcworldwide]

Yes I followed the readme exactly. Migrations did not include openiddict tables. I had to use modelBuilder.UseOpenIddict() within my model builder to solve that issue. That isn't documented on the readme.

[danielleiszen]

Just a short notice if someone bumped into this situation. The migration creation mechanism calls BuildWebHost which calls ConfigureServices in Startup.cs as default behavior. If ConfigureServices depends on something that fails when called from the migration builder this part silently exits and the configuration is skipped. But the migration is still created. In my case I depend on environment values that are missing when called from dotnet ef migrations. The cli notifies about the error like
An error occurred while accessing the Microsoft.Extensions.Hosting services. Continuing without the application service provider. Error: ...

In order to avoid this problem, I created an IDesignTimeDbContextFactory form my dbcontext and set it up using modelBuilder.UserOpenIddict() there. This way the migration builder could add the tables necessary, but the model gets configured in Startup when the dependencies are there.

Issue about this behavior Design: Allow IDesignTimeDbContextFactory to short-circuit service provider creation

[kevinchalet]

@danielleiszen thanks for sharing that, I'm sure it will help people hitting issues with migrations :clap: