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:
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. Hope this tip would help you in writing effective code. Have fun while coding. References: http://msdn.microsoft.com/en-us/library/system.data.metadata.edm.storegeneratedpattern.aspx http://stackoverflow.com/questions/4683804/set-default-value-in-ef-designer-datetime |