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();

You forgot to post the code where you actually create a new User and call SaveChanges.. Also, you don't need to add attributes to the Id property. Entity Framework has conventions and will automatically pick it up that Id is the PK of the table. — jpgrassi
– jpgrassi, Commented Jun 15, 2017 at 12:23
If the Id field is of type long, how can it be you're trying to insert null? I would at least expect a value of 0 or -1. — rickvdbosch
– rickvdbosch, Commented Jun 15, 2017 at 12:26
Did you checked on SQL Server if the Id column in your table User is an identity column ? — Misery
– Misery, Commented Jun 15, 2017 at 12:33
Can you post your Migration file? Two possible reasons I see for this: The latest migration wasn't applied on Azure OR the migration itself is messed up. If you post it here, we can see if is correct. You can also run the update command with --verbose to see the sql that is being generated.. — jpgrassi
– jpgrassi, Commented Jun 16, 2017 at 6:33

Bruce Chen · Accepted Answer · 2017-06-16 07:55:09Z

1

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:

answered Jun 16, 2017 at 7:55

Bruce Chen

18.4k2 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Janne Harju · Accepted Answer · 2017-06-16 08:05:35Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related