none
Why a single EF request can change a table field type ? (NOT NULL to NULL) RRS feed

  • Question

  • Hi,

    I have a project in ASP.Net / Entity Framework 6 / MSSQL Server 2016.

    In my database, I runned this code for the creation :

    ...

    CREATE TABLE [dbo].[Workspaces](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [nvarchar](max) NOT NULL,
    	[Description] [nvarchar](max) NULL,
     CONSTRAINT [PK_dbo.Workspaces] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO

    ...

    So the Name can NOT be NULL. As weird as it can be, if I run this code :

    ...

    [TestMethod]
            public void ArnTestAddWorkspaceByNameNULL()
            {
                WorkspaceDao WDao = new WorkspaceDao();
                WDao.AddNewWorkspace(null);
    
                Assert.AreEqual("", WDao.GetAllWorkspace().Name);
            }
    public void AddNewWorkspace(string Name)
            {
                DBcontext.Workspace.Add(new Workspace { Name = Name });
                DBcontext.SaveChanges();
            }

    ...

    Instead of having something like "null exception" or "fatal sql error" or anything else, the field change to become NULLABLE.

    If I look at my Database after that, I see:

    [Name] [nvarchar](max) NULL

    Have you got any idea ?

    Regards,

    Arnaud H.



    Friday, August 5, 2016 11:04 PM

Answers

  • Hi arnaud.helin,

    According to your code snippet, it seems that the following line code causes the issue.

    IDatabaseInitializer<DataContext> init = new DropCreateDatabaseAlways<DataContext>();
    

    Please modify like this:

    IDatabaseInitializer<DataContext> init = new DropCreateDatabaseIfModelChanges<DataContext>();
    

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 15, 2016 9:07 AM
    Moderator

All replies

  • Hello,

    SQL Server itself never modify objects like table. That must be a "special" behavior (AKA bug) in either Entity Framework or the Unit Test method; better ask in a .NET forum if this is a known issue.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, August 6, 2016 5:34 AM
  • Hi arnaud.helin,

    According to your description to code snippet, I create a simple demo (code as below), I don't encounter the issue you occurring. it'll throw a exception.

    #WorkspaceDao

    namespace InsertNullDemo
    {
        public class WorkspaceDao
        {
            public void AddNewWorkspace(string Name)
            {
                using (var db = new EFDemoContext())
                {
                    db.Workspaces.Add(new Workspace { Name = Name });
                    db.SaveChanges();
                }
            }
        }
    }
    

    #DBContext

    namespace InsertNullDemo
    {
        using System.Data.Entity;
    
        public partial class EFDemoContext : DbContext
        {
            public EFDemoContext()
                : base("name=EFDemoContext")
            {
            }
    
            public virtual DbSet<Workspace> Workspaces { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
            }
        }
    }
    

    #Usage:

    using System;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using InsertNullDemo;
    
    namespace UnitTestProject1
    {
        [TestClass]
        public class UnitTest1
        {
            [TestMethod]
            public void TestMethod1()
            {
                WorkspaceDao WDao = new WorkspaceDao();
                WDao.AddNewWorkspace(null);
                //'Assert.AreEqual("", WDao.GetAllWorkspace().Name);
            }
        }
    }
    

    #exception

    Could you please provide a simple complete code about issue, which we could reproduce your issue on our side and try to find a solution to resolve it.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 9, 2016 9:42 AM
    Moderator
  • The UNIT TEST CODE:

    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using System.Data.Entity;
    using Mercimek.Models.Dao;
    
    namespace Mercimek.Tests
    {
        [TestClass]
        public class ArnWorkspaceTest
        {
            [TestInitialize]
            public void DBInit()
            {
                IDatabaseInitializer<DataContext> init = new DropCreateDatabaseAlways<DataContext>();
                Database.SetInitializer(init);
                init.InitializeDatabase(new DataContext());
            }
    
            [TestMethod]
            public void ArnTestAddWorkspaceByNameNotNull()
            {
                WorkspaceDao WDao = new WorkspaceDao();
                WDao.AddNewWorkspace(null);
    
                Assert.AreEqual("test", WDao.GetAllWorkspaces().Find(x => x.Name == "test").Name);
            }       
        }
    }

    The POCO of Workspace:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;
    using System.Web;
    
    namespace Mercimek.Models.Poco
    {
        public class Workspace
        {
            //Private Key
            public int Id { get; set; }
    
            public string Name { get; set; }
            public string Description { get; set; }
        }
    }

    The WORKSPACE Dao code:

    using System;
    using Mercimek.Models.Poco;
    using System.Collections.Generic;
    using System.Linq;
    
    namespace Mercimek.Models.Dao
    {
        public interface IWorkspaceDao : IDisposable
        {
            void AddNewWorkspace(string Name);
         }
    public class WorkspaceDao : IWorkspaceDao
        {
            private DataContext DBcontext;
    
            public WorkspaceDao()
            {
                DBcontext = new DataContext();
            }
    
            /// <summary>
            /// Insert a new Workspace by providing a name
            /// </summary>
            /// <param name="Name"></param>
            public void AddNewWorkspace(string Name)
            {
                DBcontext.Workspace.Add(new Workspace { Name = Name });
                DBcontext.SaveChanges();
            }
    
            public void Dispose()
            {
                DBcontext.Dispose();
            }
        }
    }


    I will try to provide you the maximum information about the error. I will edit this answer to put the result.

    This is in Visual Studio :

    This is in SQL :

    Do you want more information ?

    Tuesday, August 9, 2016 2:48 PM
  • Hi arnaud.helin,

    According to your code snippet, it seems that the following line code causes the issue.

    IDatabaseInitializer<DataContext> init = new DropCreateDatabaseAlways<DataContext>();
    

    Please modify like this:

    IDatabaseInitializer<DataContext> init = new DropCreateDatabaseIfModelChanges<DataContext>();
    

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, August 15, 2016 9:07 AM
    Moderator
  • Hi cole wu,

    I will try tonight and give you the feedback.

    Regards,

    Arnaud Helin

    Tuesday, August 16, 2016 8:18 AM
  • Hi Cole Wu,

    You are the best. It resolves my issue.

    Thanks a lot.

    Kind Regards,

    Arnaud H.

    Saturday, August 20, 2016 1:21 AM