'Cannot insert the value NULL' error is showing when trying to empty a collection

Question

I am using Entity Framework. Class ChatRoom has a List of TranscriptDownloadUserInfo class. Now when I am trying empty is using .Clear(), its showing me 'Cannot insert the value NULL';

ChatRoom.cs

    public class ChatRoom
    {
        [Key]
        public int Key { get; set; }

        public virtual ICollection<TranscriptDownloadUserInfo> TranscriptDownAllowedUsers { get; set; }

        public ChatRoom()
        {
            TranscriptDownAllowedUsers = new SafeCollection<TranscriptDownloadUserInfo>();
        }
    }

TranscriptDownloadUserInfo.cs

    public class TranscriptDownloadUserInfo
    {
        [Key]
        public int Key { get; set; }

        public virtual ChatUser User { get; set; }
        public int? UserKey { get; set; }

        public bool Allowed { get; set; }
    }

TranscriptDownloadUserInfo Mapping

    public TranscriptDownloadUserInfoMap()
    {
        this.HasKey(t => t.Key);

        this.ToTable("TranscriptDownAllowedUsers");
        this.Property(t => t.Key).HasColumnName("Key");
        this.Property(t => t.UserKey).HasColumnName("UserKey");
        this.Property(t => t.Allowed).HasColumnName("Allowed");

        this.HasRequired(t => t.User)
            .WithMany()
            .HasForeignKey(t => t.UserKey);
    }

Add-Migration code

    public override void Up()
    {
        CreateTable(
            "dbo.TranscriptDownAllowedUsers",
            c => new
            {
                Key = c.Int(nullable: false, identity: true),
                UserKey = c.Int(nullable: false),
                Allowed = c.Boolean(nullable: false, defaultValue: false),
                ChatRoom_Key = c.Int(nullable: false),
            })
            .PrimaryKey(t => t.Key)
            .ForeignKey("dbo.ChatUsers", t => t.UserKey, cascadeDelete: true)
            .ForeignKey("dbo.ChatRooms", t => t.ChatRoom_Key)
            .Index(t => t.UserKey)
            .Index(t => t.ChatRoom_Key);
    }

Code that generating the error

room.TranscriptDownAllowedUsers.Clear();
_repository.CommitChanges();

Inner-Exception

Cannot insert the value NULL into column 'ChatRoom_Key', table 'Dabb.dbo.TranscriptDownAllowedUsers'; column does not allow nulls. UPDATE fails.\r\nThe statement has been terminated.

I am not sure why is this error is occurring. Any suggestion will be highly appreciated.

Possible duplicate of [Entity Framework - Clear a Child Collection](http://stackoverflow.com/questions/2058697/entity-framework-clear-a-child-collection) — Eugene Podskal, Feb 26 '17 at 20:48

score 1 · Accepted Answer · edited May 23 '17 at 12:00

1

I think there is a contradiction nullable: false, identity: true because you defined the key column as an identity, so EF assumes null values can be inserted to generate a new key.

In that case you should maybe make it nullable.

That said, you must make your foreign key ChatRoom_Key nullable, since Clear has to remove the references.

edited May 23 '17 at 12:00

Community

1
1

answered Feb 26 '17 at 20:42

Good point. As I dont need that key column. Let me try to delete it. Then lets see what happen. – Arnab Feb 26 '17 at 20:47
I've also updated my answer with another issue found: foreign key must be nullable – Feb 26 '17 at 21:26

score 0 · Answer 2 · answered Feb 26 '17 at 20:30

0

Your fields are not nullable.

            Key = c.Int(nullable: false, identity: true),
            UserKey = c.Int(nullable: false),
            Allowed = c.Boolean(nullable: false, defaultValue: false),
            ChatRoom_Key = c.Int(nullable: false),

You can insert defaults or somethings, but you can't insert nothing.

answered Feb 26 '17 at 20:30

by doing .clear(), I want delete all TranscriptDownAllowedUsers records for that room, I dont want to set column value null. Any suggestion ? – Arnab Feb 26 '17 at 20:32
I'm assuming all the chatroom keys would be the same for each user in that chat room? – Feb 26 '17 at 20:34
There would be multiple chatRooms and multiple chatUsers – Arnab Feb 26 '17 at 20:35
I think there is a `.empty`? A quick search, maybe this will help. http://stackoverflow.com/questions/1969993/is-it-better-to-return-null-or-empty-collection?rq=1 – Feb 26 '17 at 20:39
That error is coming from database as Entity Framework instead of doing "Delete from Dabb.dbo.TranscriptDownAllowedUsers where ChatRoom_Key='111'" this query, its doing "Update Dabb.dbo.TranscriptDownAllowedUsers set ChatRoom_Key=NULL where ChatRoom_Key='111'"... how can I fix this ? – Arnab Feb 26 '17 at 20:44

'Cannot insert the value NULL' error is showing when trying to empty a collection

2 Answers2