Inserting default values in database while inserting record in Entity Framework

You can set "StoredGeneratedProperty" attribute of table column in the EDMX file to Computed to enable default value insertion in entity framework.

The Problem

While working with Entity Framework, you would have noticed that it does not insert default value into the database. You need to manually set values for those properties which have already a default value set in database table. This seems as an extra work to set values for default columns. Suppose, you are saving created date of a row in your table and for this you are using default constraint as today's days. Now when you insert a new record in database, it works absolutely fine but if you perform this operation using entity framework, it will not let you doing so. To insert default value, you need to manually assign a value to that column.

The Solution

If you are using code first approach, you can set default values using [DefaultValue()]  attribute or if you are using database first and using edmx file then you can set StoreGeneratedPattern = "Computed" of that particular column. Just follow the steps below.

Step 1: Open EDMX file and locate your table in that file.

Step 2: Select the property name. Right click and click on property.

Step 3: You will find a property named as StoreGeneratedPattern with value set as None.

Step 4: Set StoreGeneratedPattern = "Computed".

What should be the default behavior of LINQ-to-Entities while setting the default value of a field in SQL’s field designer ?

Sometimes it’s difficult to come up with a topic that we feel inspired to write about. I am writing about the problem I encountered while working with entity datamodel. After discussing with the team members I feel comfortable writing about it. I also feel good that this article would then help others who might have encountered the same problem.

Lets assume we create a table named TechZeus_Logging_Details having four fieldsPK_ID (auto incremented primary key) , LAST_NAME(varchar) , FIRST_NAME(varchar) and LOG_TIME(datetime)..
CREATE TABLE [dbo].[TECHZEUS_LOGGING_DETAILS] (

[PK_ID] [int] IDENTITY(1,1) NOT NULL,

[LAST_NAME] [varchar](50) NULL,

[FIRST_NAME] [varchar](50) NULL,

[LOG_TIME] [datetime] NULL )

Then we set the default value of a datetime field by getdate() bydefault to set the current time for newly inserted row

ALTER TABLE [dbo].[TECHZEUS_LOGGING_DETAILS] ADD CONSTRAINT [DF_TECHZEUS_LOGGING_DETAILS] DEFAULT (getdate()) FOR [LOG_TIME]

Now while inserting values into this table using direct SQL query , we can just omit the default field because that will be automatically taken care of as the default value is set in SQL server so that we dont need to set it explicitly.

INSERT INTO [dbo].[TECHZEUS_LOGGING_DETAILS](

LAST_NAME,FIRST_NAME)

VALUES(

‘ABC’, ‘XYZ’)

Here the matter of concern is that what we should do while using entity datamodel for such database operations?

The general misconception is to leave the default fields as such while saving changes using context object.

Something like this:

using (var contextObject = new DatabaseEntities())

{

  try {

 
         TECHZEUS_LOGGING_DETAILS objTZLD = new TECHZEUS_LOGGING_DETAILS(); //Create object of the table to insert data

         objTZLD.LAST_NAME = "XYZ"; // Assign value to LAST_FIELD field

         objTZLD.FIRST_NAME = "ABC"; // Assign value to FIRST_NAME field

 
         contextObject.AddToTECHZEUS_LOGGING_DETAILS(objTZLD); //Add object to insert data into table

         contextObject.SaveChanges(); //Commit inserted data or changed data.

       }

  catch(ex)

       {

         //Log exception into Exception Log table.

        }

}

This approach creates problem because Properties connected to [LOG_TIME] column which is set as default in SQL’s field designer will not auto-populate.after the SaveChanges action is performed on the Entity Context, rather NULL or default value(C# default value) will be written to the [LOG_TIME](datetime) field instead of setting the database level default value!!

So, to overcome this problem what should we do ? Well the answer is, we need to follow these 2 steps: Set the default value in SQL Server as usual as we do. After that Set ‘StoredGeneratedPattern‘ attribute to ‘Computed‘ against the default field in the .edmx file.We still need the default value in SQL Server though, the above setting will ensure Entity Framework’s expected behaviour. Now comes the question what is StoredGeneratedPattern in entitymodel? Basically it represents an enumeration that specifies three options indicating whether the column in the store schema definition language (SSDL) (.ssdl) needs a value during insert and update operations and it tells us what should be done if we insert or update rows. We can set it as None, Identity or Computed. i) None: No auto generated value is generated ii)Identity: A new value is generated on insert, but not changed on update iii)Computed: A new value is generated on insert and update. Note: There is always an another method to achieve the same behavior. Here also we have one more option i.e. we can manually set required value in the code itself and don’t rely on the SQL or Entity default settings. That will also ensure that we are not inserting nulls or any default values in tables but my point is why to avoid such provided properties instead of doing everything in our code. At the end of the day these are individual choices. So always take care of such small points which can lead to big issues later on.