Error when trying to insert entity using .NET Core, Entity Framework and SQL Server on Azure

Question

In development phase I was using SQLite and all works fine when I only set Key attribute to Id field. No I'm trying to use SQL Server on Azure. I manage to build database but when I try to add first entity I get this error.

An error occurred while updating the entries. See the inner exception for details. 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 'MSPL.dbo.Users'; column does not allo w nulls. INSERT fails.

Here is my current database context configuration. I'm using code first.

public class MultiSourcePlaylistContext : DbContext
    {
        public MultiSourcePlaylistContext(DbContextOptions<MultiSourcePlaylistContext> options) :base(options)
        { 
        }
        public DbSet<Track> Tracks { get; set; }
        public DbSet<Playlist> Playlists { get; set; }
        public DbSet<User> Users { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        { 
            builder.Entity<Track>()
                .HasOne(track=>track.Playlist)
                .WithMany(playlist => playlist.Tracks)
                .OnDelete(DeleteBehavior.Cascade);
            builder.Entity<Playlist>()
                .HasOne(track=>track.Owner)
                .WithMany(user => user.Playlists) 
                .OnDelete(DeleteBehavior.Cascade);
            base.OnModelCreating(builder); 
        } 
    }

Here is one example Entity

public class User
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }
        public string Username { get; set; }

        public string Password { get; set; }

        public string Fname {get;set;}
        public string Lname {get;set;}
        public string FileFolder{get;set;}
        public ICollection<Playlist> Playlists { get; set; }

    }

So the different is that I started to use SQL Server instead of SQLite. I already added DatabaseGenerated attribute even that I understand that if Entity has Id or entityNameId fields it is automatically Primary field. My goal is to get id automatically set when new entity is inserted.

Here is my service configuration from Startup.cs

if(CurrentEnvironment.IsDevelopment())
            {
                var connection = Configuration["Development:SqliteConnectionString"];
                services.AddDbContext<MultiSourcePlaylistContext>(options =>
                    options.UseSqlite(connection)
                );
            }
            else
            {
                var connection = Configuration["Production:SqlServerConnectionString"];
                services.AddDbContext<MultiSourcePlaylistContext>(options =>
                    options.UseSqlServer(connection)
                );
            }

Here is lines where I made insert

public void PostUser(User user)
        {
            _context.Users.Add(user);
            _context.SaveChanges();
        }

Here is Create script for User table from SQL Management Studio. it shows that there is no Identity set to Id field. I will try to remove all attributes from Entities.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Users](
    [Id] [bigint] NOT NULL,
    [FileFolder] [nvarchar](max) NULL,
    [Fname] [nvarchar](max) NULL,
    [Lname] [nvarchar](max) NULL,
    [Password] [nvarchar](max) NULL,
    [Username] [nvarchar](max) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

Here is more log before exception. There is that critical log which shows my entity's properties. I noticed that there is no Id in the Insert command.

TokenAuthController[0] {"Id":0,"Username":"MyUsername","Password":"AQAAAAEAACcQAAAAEOtXeLCECSRSINe9+kOexywYeP+E/o1nh2hYNO5EG1OBu4eL+7ult2PGOFDs8r+CwQ=="," Fname":"MyName","Lname":"MyLastName","FileFolder":"184d0857-31ef-4bd5-aeb1-51934e98f670","Playlists":null} info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1] Executed DbCommand (728ms) [Parameters=[@p0='?' (Size = 4000), @p1='?' (Size = 4000), @p2='?' (Size = 4000), @p3='?' (Size = 4 000), @p4='?' (Size = 4000)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; INSERT INTO [Users] ([FileFolder], [Fname], [Lname], [Password], [Username]) VALUES (@p0, @p1, @p2, @p3, @p4); SELECT [Id] FROM [Users] WHERE @@ROWCOUNT = 1 AND [Id] = scope_identity();

Answer 1

My problem was that I was generating migration wihout -e Production parameter. I use that parameter in Startup.cs if(CurrentEnvironment.IsDevelopment()) So I was running SQLite migration to SQL server.

So my Entities was correct whole time I just forget to run migration command against Production DB. So now my Id field in migration looks like this:

Id = table.Column<long>(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),

when my User Entity is like this:

public class User
    {
        public long Id { get; set; }
        public string Username { get; set; }

        public string Password { get; set; }

        public string Fname {get;set;}
        public string Lname {get;set;}
        public string FileFolder{get;set;}
        public ICollection<Playlist> Playlists { get; set; }

    }

Here is my commands to Production ie. my SQL Server database.

dotnet ef migrations add newdatabase -e Production

dotnet ef database update -e Production

Answer 2

AFAIK, You could use the Key attribute if the entity does have its own primary key but you want to name the property something other than classnameID or ID. Also, you could use the DatabaseGenerated attribute on your property specifies that the primary key, and specify DatabaseGeneratedOption.Identity for the value generated on add or DatabaseGeneratedOption.Computed for the value generated on add or update. For more details, you could refer to Generated Values.

According to your description, I have checked it with my .NET Core web application (.NET Core 1.0). Here are my test, you could refer to them.

For SQLite:

For SQL Server DB:

The related Nuget packages: