Runs perfectly with SQLlite...but cant migrate to SQL Server

[knowshape]

Asad,

Your project is amazing! I can run it locally from VSCode, and register a new user and then authenticate as that user, with Angular (2). This is the ONLY project I have found that does Core and A2 with authentication that I can get working, so thank you for sharing it on GitHub!

My problem: I want to use it with a SQL Server database just like the SQLite AspNetCoreSpa.db you have in the example, but the following instructions you gave do not work for me:

* To run under sql server:
    * delete bin & Migrations folders
    * Flip the switch in appsettings.json called `useSqLite` to `false`, this should point to use local sql server setup   as default instance.
    * Run `dotnet ef migrations add "MigrationName"`

...which runs OK, but when I Debug->Start Debugging from VSCode, in the Debug Console, I get a "Exception thrown: 'System.Data.SQLClient.SqlException' in Microsoft.EntityFrameworkCore.SqlServer.dll ...and it does not run.

Is there any way I could generate the SQL Server version of the database independently, accessible from SQL Server Management Studio, that I could then connect to from your example?

Or, is there a way your project would generate a LocalDB version that I could then convert?

--Thanks, Richard

[asadsahi]

@knowshape

It is difficult to say looking at the error you shown above. What is your sql server instance name? It it is set to default instance you should be able to connect using . or localhost from SSMS. And this project is using this default instance to connect to.

[m2017atTR]

I believe error is about Id key field that not null, In SeedDbData

Unhandled Exception: Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Id', table 'AspNetCoreSpa.dbo.Languages'; column does not allow nulls. INSERT fails.

[asadsahi]

@knowshape I have just tried following same instructions but cannot see any errors. Able to run the application just fine.

@m2017atTR Id is a seed value by default and primary key. If we specify id, it fails in sql server seeding. I had this issue when trying to get it work with both sql lite and sql server. Not specifying the key when seeding the data works in both types of DBs.

[m2017atTR]

• I tryed at SqlLocalDb v11.0 instance I had issue that id[INT] field is not generated on db as AutoIncrement [DatabaseGenerated(DatabaseGeneratedOption.Identity)] is not worked for Id field. https://docs.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/creating-a-more-complex-data-model-for-an-asp-net-mvc-application#the-databasegenerated-attribute

• On SqlLite Id Sequences created as needed... and works well

[asadsahi]

@m2017atTR

I tryed at SqlLocalDb v11.0 instance

I haven't tried with SqlLocalDB, but see whatever fits for your purpose. I think it could be Migration scaffolding works differently for different databases.

I had issue that id[INT] field is not generated on db as AutoIncrement

Not sure why is that, never seen that being a prblem. id(int) with [Key] decorator is set to auto seed primary key by default.

Have you tried with full sql server to see if it fixes the issue? You can try any version of sql server (express, developer etc)

[knowshape]

In this attachment, is my complete SQL Server script for a working version of this database: (I hope this attachment is good...first time I have done this. :o ) (I renamed the database just to keep it separate, and changed it in the connect string in the code.)

AspNetCoreSpa_02.sql.txt

Note: SQL Server DDL creates the Id column thusly:

CREATE TABLE "Content" ( "Id" INTEGER IDENTITY(1,1) PRIMARY KEY, "Key" nvarchar(150) NOT NULL );

...and then, what follows shows how I populated it.

SET IDENTITY_INSERT Content ON

INSERT INTO Content ( Id, [Key]) VALUES(1,'TITLE'); INSERT INTO Content ( Id, [Key]) VALUES(2,'APP_NAV_HOME'); INSERT INTO Content ( Id, [Key]) VALUES(3,'APP_NAV_EXAMPLES'); INSERT INTO Content ( Id, [Key]) VALUES(4,'APP_NAV_LOGIN'); INSERT INTO Content ( Id, [Key]) VALUES(5,'APP_NAV_LOGOUT'); INSERT INTO Content ( Id, [Key]) VALUES(6,'APP_NAV_REGISTER'); INSERT INTO Content ( Id, [Key]) VALUES(7,'APP_NAV_ADMIN');

SET IDENTITY_INSERT Content OFF

Note: I realize this won't fix any issue with the DB generation code, but if you turn off the "seeding" and reference the SQL Server database generated from my attached SQL script, the project will work! Hope this helps!

--Richard aka "KnowShape"

[Azayzel]

My fix for this issue was to migrate to SQLExpress then migrate that to SQL Server.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4abf91a7-f5e8-438b-8b56-2daeb4482dde/best-method-for-moving-sql-server-2005-express-db-to-2008-r2-in-new-domain?forum=sqlexpress

[Swift-T]

I was able to reproduce knowshape's error in sql server express. The problem was probably because you didn't create a database in advance. Using localdb, it runs without a problem.

[asadsahi]

I have just tried pointing to sql server following the instructions on readme and it works. Creating fresh migrations for respective database it work. If some still has this issue please share a repo with this issue and I can have a look in details.

Closing for now.