Identityserver4: EntityFramework Storage exception when inserting Client

[MPapst]

Issue / Steps to reproduce the problem

Clone the samples and start the Combined_AspNetIdentity_and_EntityFrameworkStorage > IdentityServerWithAspIdAndEF sample project. Add /seed argument. I also replaced the connectionstring from sqlite to LocalDB and replaced UseSqlite with UseSqlServer.

Relevant parts of the log file

Clients being populated
[21:18:34 Error] Microsoft.EntityFrameworkCore.Database.Command
Failed executing DbCommand (45ms) [Parameters=[@p0='?', @p1='?', @p2='?', @p3='?', @p4='?', @p5='?', @p6='?', @p7='?', @p8='?', @p9='?', @p10='?', @p11='?' (Size = 2000), @p12='?' (Size = 200), @p13='?' (Size = 200), @p14='?' (Size = 200), @p15='?' (Size = 2000), @p16='?', @p17='?' (Size = 1000), @p18='?', @p19='?', @p20='?', @p21='?' (Size = 2000), @p22='?', @p23='?', @p24='?' (Size = 2000), @p25='?' (Size = 200), @p26='?' (Size = 200), @p27='?', @p28='?', @p29='?', @p30='?', @p31='?', @p32='?', @p33='?', @p34='?', @p35='?', @p36='?', @p37='?', @p38='?', @p39='?', @p40='?', @p41='?', @p42='?', @p43='?', @p44='?', @p45='?' (Size = 2000), @p46='?' (Size = 200), @p47='?' (Size = 200), @p48='?' (Size = 200), @p49='?' (Size = 2000), @p50='?', @p51='?' (Size = 1000), @p52='?', @p53='?', @p54='?', @p55='?' (Size = 2000), @p56='?', @p57='?', @p58='?' (Size = 2000), @p59='?' (Size = 200), @p60='?' (Size = 200), @p61='?', @p62='?', @p63='?', @p64='?', @p65='?', @p66='?', @p67='?', @p68='?', @p69='?', @p70='?', @p71='?', @p72='?', @p73='?', @p74='?', @p75='?', @p76='?', @p77='?', @p78='?', @p79='?' (Size = 2000), @p80='?' (Size = 200), @p81='?' (Size = 200), @p82='?' (Size = 200), @p83='?' (Size = 2000), @p84='?', @p85='?' (Size = 1000), @p86='?', @p87='?', @p88='?', @p89='?' (Size = 2000), @p90='?', @p91='?', @p92='?' (Size = 2000), @p93='?' (Size = 200), @p94='?' (Size = 200), @p95='?', @p96='?', @p97='?', @p98='?', @p99='?', @p100='?', @p101='?'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
MERGE [Clients] USING (
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, 0),
(@p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66, @p67, 1),
(@p68, @p69, @p70, @p71, @p72, @p73, @p74, @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82, @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90, @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98, @p99, @p100, @p101, 2)) AS i ([AbsoluteRefreshTokenLifetime], [AccessTokenLifetime], [AccessTokenType], [AllowAccessTokensViaBrowser], [AllowOfflineAccess], [AllowPlainTextPkce], [AllowRememberConsent], [AlwaysIncludeUserClaimsInIdToken], [AlwaysSendClientClaims], [AuthorizationCodeLifetime], [BackChannelLogoutSessionRequired], [BackChannelLogoutUri], [ClientClaimsPrefix], [ClientId], [ClientName], [ClientUri], [ConsentLifetime], [Description], [EnableLocalLogin], [Enabled], [FrontChannelLogoutSessionRequired], [FrontChannelLogoutUri], [IdentityTokenLifetime], [IncludeJwtId], [LogoUri], [PairWiseSubjectSalt], [ProtocolType], [RefreshTokenExpiration], [RefreshTokenUsage], [RequireClientSecret], [RequireConsent], [RequirePkce], [SlidingRefreshTokenLifetime], [UpdateAccessTokenClaimsOnRefresh], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([AbsoluteRefreshTokenLifetime], [AccessTokenLifetime], [AccessTokenType], [AllowAccessTokensViaBrowser], [AllowOfflineAccess], [AllowPlainTextPkce], [AllowRememberConsent], [AlwaysIncludeUserClaimsInIdToken], [AlwaysSendClientClaims], [AuthorizationCodeLifetime], [BackChannelLogoutSessionRequired], [BackChannelLogoutUri], [ClientClaimsPrefix], [ClientId], [ClientName], [ClientUri], [ConsentLifetime], [Description], [EnableLocalLogin], [Enabled], [FrontChannelLogoutSessionRequired], [FrontChannelLogoutUri], [IdentityTokenLifetime], [IncludeJwtId], [LogoUri], [PairWiseSubjectSalt], [ProtocolType], [RefreshTokenExpiration], [RefreshTokenUsage], [RequireClientSecret], [RequireConsent], [RequirePkce], [SlidingRefreshTokenLifetime], [UpdateAccessTokenClaimsOnRefresh])
VALUES (i.[AbsoluteRefreshTokenLifetime], i.[AccessTokenLifetime], i.[AccessTokenType], i.[AllowAccessTokensViaBrowser], i.[AllowOfflineAccess], i.[AllowPlainTextPkce], i.[AllowRememberConsent], i.[AlwaysIncludeUserClaimsInIdToken], i.[AlwaysSendClientClaims], i.[AuthorizationCodeLifetime], i.[BackChannelLogoutSessionRequired], i.[BackChannelLogoutUri], i.[ClientClaimsPrefix], i.[ClientId], i.[ClientName], i.[ClientUri], i.[ConsentLifetime], i.[Description], i.[EnableLocalLogin], i.[Enabled], i.[FrontChannelLogoutSessionRequired], i.[FrontChannelLogoutUri], i.[IdentityTokenLifetime], i.[IncludeJwtId], i.[LogoUri], i.[PairWiseSubjectSalt], i.[ProtocolType], i.[RefreshTokenExpiration], i.[RefreshTokenUsage], i.[RequireClientSecret], i.[RequireConsent], i.[RequirePkce], i.[SlidingRefreshTokenLifetime], i.[UpdateAccessTokenClaimsOnRefresh])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;

SELECT [t].[Id] FROM [Clients] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
ORDER BY [i].[_Position];
System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'Id', table 'aspnet-MvcMovie.dbo.Clients'; column does not allow nulls. UPDATE fails.

When manually fixing the Client (by setting an Id before insert) the same issue appears for the ClientGrantTypes

[MPapst]

Found it.

Since the Initial Migration in the sample is built using Sqlite, it has added Annotations for Sqlite only:

migrationBuilder.CreateTable(
                name: "Clients",
                columns: table => new
                {
                    Id = table.Column<int>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),

For SqlServer a different annotation is necessary. By adding this one (just another anotation should work for both engines), the value is generated properly when inserting entities to the database:

Id = table.Column<int>(nullable: false)
                        .Annotation("Sqlite:Autoincrement", true)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),

Edit: this also affects the AspNetIdentity Tables.

[brockallen]

This is why migrations are the host's responsibility and not this library's. Glad you found it.

[Katori]

I disagree. The IdentityServer4 documentation uses SqlServer, which makes this confusing for those who come directly from that documentation into this recommended QuickStart. I'm stuck on this problem still because I can't figure out how to re-run the migration after I've altered it. It's something I can solve, but still an unnecessary kink in the chain. I say either use SqlServer in the Sample or add a note that the user will have to manually make this change before running the migration.

[lock[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.