How to get EF 6 to handle DEFAULT CONSTRAINT on a database during INSERT

Question

I am new to EF (its my first week), but not new to databases or to programming. Others have asked similar questions, but I don't feel that it has been asked with the right detail or explained quite as it needs to be explained, so here I go.

Question: How do I get Entity Framework to properly deal with columns in a database that have a DEFAULT CONSTRAINT defined when performing an INSERT? Meaning, if I do not supply a value in my model during an insert operation, how do I get EF to exclude that column from its generated TSQL INSERT command, so that the database-defined DEFAULT CONSTRAINT will work?

Background

I have a simple table I created, just to test Entity Framework 6 (EF6) and its interaction with the columns SQL Server is capable of updating. This utilizes IDENTITY, TIMESTAMP, COMPUTED, and a few columns with a DEFAULT CONSTRAINT applied.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DBUpdateTest](
    [RowID] [int] IDENTITY(200,1) NOT NULL,
    [UserValue] [int] NOT NULL,
    [DefValue1] [int] NOT NULL,
    [DefValue2null] [int] NULL,
    [DefSecond] [int] NOT NULL,
    [CalcValue]  AS 
        (((([rowid]+[uservalue])+[defvalue1])+[defvalue2null])*[defsecond]),
    [RowTimestamp] [timestamp] NULL,
    CONSTRAINT [PK_DBUpdateTest] PRIMARY KEY CLUSTERED 
    (
        [RowID] ASC
    )
    WITH 
    (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 
) 
GO
ALTER TABLE [dbo].[DBUpdateTest] 
ADD CONSTRAINT [DF_DBUpdateTest_DefValue1]      
DEFAULT ((200)) FOR [DefValue1]
GO
ALTER TABLE [dbo].[DBUpdateTest] 
ADD CONSTRAINT [DF_DBUpdateTest_DefValue2null]  
DEFAULT ((30)) FOR [DefValue2null]
GO
ALTER TABLE [dbo].[DBUpdateTest] 
ADD  CONSTRAINT [DF_DBUpdateTest_DefSecond]  
DEFAULT (datepart(second,getdate())) FOR [DefSecond]
GO

EF6 handles the IDENTITY, TIMESTAMP, and COMPUTED columns perfectly, meaning after INSERT or UPDATE (via context.SaveChanges()) EF reads the new values back into the entity object for immediate use.

However, this does not happen for the columns with the DEFAULT CONSTRAINT. And from what I can tell, this is because when EF generates the TSQL to perform the INSERT, it supplies the common default value for nullable or non-nullable types, just as if that column had no DEFAULT CONSTRAINT defined on it. So it seems clear that EF completely ignores the possibility of a DEFAULT CONSTRAINT.

Here is my EF code to INSERT a DBUpdateTest record (and I only update a single column):

DBUpdateTest myVal = new DBUpdateTest();
myVal.UserValue = RND.Next(20, 90);
DB.DBUpdateTests.Add(myVal);
DB.SaveChanges();

Here is the EF generated SQL during an INSERT to DBUpdateTest (which dutifully updates all possible columns):

 exec sp_executesql 
 N'INSERT [dbo].[DBUpdateTest]([UserValue], [DefValue1], [DefValue2null],
          [DefSecond])
   VALUES (@0, @1, NULL, @2)
   SELECT [RowID], [CalcValue], [RowTimestamp]
   FROM [dbo].[DBUpdateTest]
   WHERE @@ROWCOUNT > 0 AND [RowID] = scope_identity()',
 N'@0 int,@1 int,@2 int',@0=86,@1=0,@2=54

Note that it is very clearly supplying what would be the default value for an INT NOT NULL (0) and an INT NULL (null), which completely overcomes the DEFAULT CONSTRAINT.

This is what happens when the EF INSERT command executes, where it supplies a NULL for the nullable column and a ZERO for the INT column

RowID   UserValue   DefValue1   DefValue2null   DefSecond   CalcValue
=========================================================================
211     100         200         NULL            0           NULL

If, on the other hand, I execute this statement:

insert into DBUpdateTest (UserValue) values (100)

I will get a record like so

RowID   UserValue   DefValue1   DefValue2null   DefSecond   CalcValue
=========================================================================
211     100         200         30              7           3787

This works as expected for one reason: the TSQL INSERT command did not provide values for any columns with a defined DEFAULT CONSTRAINT.

What I am trying to do, therefore, is to get EF to exclude the DEFAULT CONSTRAINT columns from the INSERT TSQL if I do not explcitly set values for them in the model object.

Things I Already Tried

1. Recognize Default Constraint? SO: How to get EF to handle a Default Constraint

In the OnModelCreating() method of my DbContext class, it was recommended that I could tell EF that a column with the DEFAULT CONSTRAINT is a COMPUTED field, which it is not. However, I wanted to see if it would get EF to at least read the value back after the INSERT (never mind that it would also likely keep me from being able to assign a value to that column, which is just more of what I do not want):

        modelBuilder.Entity<DBUpdateTest>()
            .Property(e => e.DefValue1)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);

This does not work, and in fact appears to do nothing different at all (ED: actually it does work, see point 2). EF still generates the same TSQL, providing defaults for the columns and defeating the database in the process.

Is there a flag I am missing, a configuration item I am forgetting to set, a function attribute I can use, some inherited class code I can create, to get EF to "handle DEFAULT CONSTRAINT columns correctly?"

2. Get OnModelCreating() to execute? SO: OnModelCreating not called

Janesh (below) showed me that EF will eliminate parameters from its generated TSQL INSERT command if the column is marked with DatabaseGeneratedOption.Computed. It just wasn't working for me because apparently I was using the wrong kind of connect string (!!!).

Here's my App.config, and here's the <connectionStrings> section where I show the "bad" and "good" connect string:

<connectionStrings>
  <add name="TEST_EF6Entities_NO_WORKY" providerName="System.Data.EntityClient" connectionString="metadata=res://*/TCXModel.csdl|res://*/TCXModel.ssdl|res://*/TCXModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=...ConnectStringHere...;App=EntityFramework&quot;"  />
  <add name="TEST_EF6Entities_IT_WORKS" providerName="System.Data.SqlClient" connectionString="data source=...ConnectStringHere...;App=EntityFramework;"  />
</connectionStrings>

The difference: The one that works uses ProviderName of System.Data.SqlClient, the one that does not work uses System.Data.EntityClient. Apparently the SqlClient provider allows the OnModelCreating() method to be called, which allows my use of DatabaseGeneratedOption.Computed to have an effect.

========== NOT YET SOLVED ==========

The purpose of DEFAULT CONSTRAINTS on columns is to allow me to supply (or not supply) a value, and still end up with a valid value on the database side. I don't have to know that SQL Server is doing this, nor do I have to know what the default value is or should be. This happens completely outside my control or knowledge.

The point is, I have the option of not supplying the value. I can supply it, or I can fail to supply it, and I can do that differently for each INSERT if needed.

Using DatabaseGeneratedOption.Computed is really not a valid option for this case because it forces a choice: "you can ALWAYS provide a value (and therefore NEVER utilize the database default mechanism), or you can NEVER provide a value (and therefore ALWAYS utilize the database default mechanism)".

Plus, that option is clearly intended to be used only on actual Computed Columns, and not for columns with DEFAULT CONSTRAINTs, because once applied, the model property effectively becomes READ-ONLY for purposes of INSERT and UPDATE - because that's how a real Computed Column would work. Obviously this stands in the way of my choice to supply or not supply a value to the database.

So, I still ask: How can I get EF to work "correctly" with database columns that have a DEFAULT CONSTRAINT defined?

Answer 1

This bit is the key to your question:

What I am trying to do, therefore, is to get EF NOT to include the DEFAULT CONSTRAINT columns in its INSERT TSQL if I do not explcitly set values for them in the object.

Entity Framework won't do that for you. Fields are either always computed or always included in inserts and updates. But you CAN write the classes to behave in the way that you describe. You have to set the fields (explicitly) to the default values in the constructor, or using backing fields.

public class DBUpdateTest
/* public partial class DBUpdateTest*/ //version for database first
{
   private _DefValue1 = 200;
   private _DefValue2 = 30;

   public DbUpdateTest()
   {
      DefSecond = DateTime.Second;
   }

   public DefSecond { get; set; }

   public DefValue1
   {
      get { return _DefValue1; }
      set { _DefValue1 = value; }
   }

   public DefValue2
   {
      get { return _DefValue2; }
      set { _DefValue2 = value; }
   }
}

If you always insert using these classes, then you probably don't need to set the default in the database, but if you insert using sql from elsewhere, then you will have to add the default constraint to the database too