I found this something interesting while wandering over community which I would like
to share. The post is whole about: DBML is not considering the table
field's "Default value or Binding" setting which is a NotNull. I mean the
field which can not be null but having default value set needs
to be set IsDbGenerated = true in DBML file explicitly.Consider this situation: There is a simple tblEmployee table with below structure: The fields are simple. EmployeeID is a Primary Key with Identity Specification = True with Identity Seed = 1
to autogenerate numeric value for this
field. EmployeeName and their EmailAddress
to store in rest of 2 fields. And the last one is "DateAdded" with DateTime datatype which doesn't allow NULL but having Default Value/Binding with "GetDate()". That means if we don't pass any value
to this
field then SQL will insert current date in "DateAdded"
field.So, I start with a new website, add a DBML file and dropped the said table
to generate LINQ
To SQL context class. Finally, I write a simple code snippet
to insert data into the tblEmployee table; BUT, I am not passing any value
to "DateAdded"
field. Because I am considering SQL Server's "Default Value or Binding (GetDate())" setting
to this
field and understand that SQL will insert current date
to this
field. using (TestDatabaseDataContext context = new TestDatabaseDataContext()) { tblEmployee tblEmpObjet = new tblEmployee(); tblEmpObjet.EmployeeName = "KaushaL"; tblEmpObjet.EmployeeEmailAddress = "
[email protected]"; context.tblEmployees.InsertOnSubmit(tblEmpObjet); context.SubmitChanges(); }Here comes the twist when application give me below error: This is something not expecting! From the error it clearly depicts that LINQ is passing NULL value
to "DateAdded"
Field while according
to my understanding it should respect Sql Server's "Default value or Binding" setting for this
field. A bit googling and I found very interesting related
to this problem.When we set Primary Key
to any
field with "Identity Specification" Property set
to true; DBML set one important property "IsDbGenerated=true" for this
field. BUT, when we set "Default Value or Biding" property for some field; we need
to explicitly tell the DBML/LINQ
to let it know that this
field is having default binding at DB side that needs
to be respected if I don't pass any value. So, the solution is: You need
to explicitly set "IsDbGenerated=true" for such
field to tell the LINQ that the
field is having default value or binding at Sql Server side so, please don't worry if i don't pass any value for it.You can select the
field and set this property from property window in DBML Designer file or write the property in DBML.Designer.cs file directly. I have attached a working example with required table script with this post here. I hope this would be helpful for someone hunting for the same. Happy Discovery!
