Handling Default Values With LINQ to SQL

Hi All,

Have run into some issues with using default values in my SQL database with LINQ. I set AutoGenerated in the LINQ Designer to true which made use of my default values but this did not let me update this manually as it would throw an exception. I got a good suggestion from Young Joo to keep autogenerated set to false and provide my default values on the client. To do this I implemented the OnCreated partial method and set up a default value for my item.

I.E.

partial void OnCreated() {
        if (this.DateTimeCreated == null) {
               this.DateTimeCreated = DateTime.Now;
        }
}

 

This will setupt the default value for our object each time we create an instance of it. You will then either override the value when you load from the database, or when you change the values before an insert/update. This solved my issue with handling default values and seems to work quite well.

 

Thanks

Stefan

 

Published Tuesday, January 8, 2008 8:53 AM by stefan.sedich
Filed under: , , , , , ,

Comments

# re: Handling Default Values With LINQ to SQL

Tuesday, January 8, 2008 4:09 AM by Matt Brooks

This is how I solved the problem on a recent project. However, I was not happy with this approach as I feel it is more of a workaround than a solution.

There is likely duplication here between default values specfied in the DBMS (SQL Server) and in your LINQ-to-SQL data model.

In my opinion LINQ-to-SQL should allow you to set values for 'auto generated' columns, relying on the DBMS to generate the value if it is not specified.

We've had a similar problem with SQL Server 2000 'UniqueIdentifier' ROWGUID PRIMARY KEY columns, which map to System.Guid. A value for this primary key should be generated either by the LINQ-to-SQL query engine or the DBMS itself (i.e. newid()). However, we get an error when trying to insert new records 'Value for column of type '[System.NotSupportedException] The primary key column of type 'UniqueIdentifier' cannot be generated by the server'.

Note this does not occur when executing the same insert code against the same database running on SQL Server 2005.

Regards,

Matt

# re: Handling Default Values With LINQ to SQL

Wednesday, January 9, 2008 3:47 PM by James M

Hi Stefan

Good post. But you say onCreating in the text and onCreated in the code - did you mean to stay with OnCreating?

Cheers

James

# re: Handling Default Values With LINQ to SQL

Wednesday, January 9, 2008 11:03 PM by stefan.sedich

Yep sorry I meant OnCreated. Will update the post.

Thanks

Stefan

# re: Handling Default Values With LINQ to SQL

Friday, February 22, 2008 10:01 AM by Roy

I am posting this slightly off topic post because I have seen several people that think that setting IsDbGenerated is the correct way to handle default values, but then discover that they can not update the colmun later.

I just did a test on the IsDbGenerated flag.  I believe that it is missunderstood.  When I set a column to have a calculated value instead of a default value, SQLMetal set the IsDbGenerated flag for that column.  It does not set it for columns with defaults.

# re: Handling Default Values With LINQ to SQL

Friday, April 25, 2008 10:15 PM by Tod

I set the auto generated field to true, then I modify the column in the table in SSCE to have a default of

getdate()

and it works fine. I can't however get it to work with a uniqueidentifier, maybe SSCE doesn't support unique identifiers.  

# re: Handling Default Values With LINQ to SQL

Monday, June 9, 2008 3:44 PM by Travis

AutoGenerated = true on the non-nullable field also worked for me, I simply didn't set the field on insert.

# re: Handling Default Values With LINQ to SQL

Friday, June 20, 2008 6:22 AM by Carol

This one example helped me with using a linqdatasource. I just set the OnInserting event on my linqdatasource and used the following code to set any values that I wanted to pick up before inserting.

Protected Sub LinqDataSource_OnInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceInsertEventArgs)

   Dim product As Product

   product = CType(e.NewObject, Product)

   product.DateModified = DateTime.Now

   product.typeid = Request.QueryString("typeid")

End Sub

Hope this helps someone.

Cheers

Carol

# re: Handling Default Values With LINQ to SQL

Tuesday, September 9, 2008 9:32 AM by Quango2005

A warning - I tried the same approach with using OnCreated to set values, but found it created an new bug- LINQ to SQL will fail on updates as it thinks the values have changed.

# re: Handling Default Values With LINQ to SQL

Tuesday, January 6, 2009 3:43 AM by Scott.Yan

I don't think this is acceptable. Since there are hundreds even thousands talbes in DB, and almost every table have some default value, it's impossible to create such functions for every table. -----Okay, even if I can do this, but the default value could be changed for some reason,  make LINQ layer sync with DB will be an annoying work...  shame on LINQ team!

# re: Handling Default Values With LINQ to SQL

Friday, April 24, 2009 7:31 AM by Laurent Jeanmonod

Hi all,

This doesn't work for me Kamran, if I set the Autosync to OnInsert, I have the same exception as if I don't set it, which is "Column blabla can't have NULL value" or something like that.

I've controlled in the code if the attribute "AutoSync=AutoSync.OnInsert" was set and it is well, what am I doing wrong?

# re: Handling Default Values With LINQ to SQL

Thursday, May 21, 2009 4:17 PM by Daniel Adeniji

Laurent:

if the column is required, please set not null values

for the columns, as well

objReview.AddedBy = Environment.UserName;

objReview.AddedOn = System.DateTime.Now;

objReviews.InsertOnSubmit(objReview);

iFood.SubmitChanges();

Also, found out that if the column being generated is part of the primary key, we have problems matching on the post insert select statements.

# re: Handling Default Values With LINQ to SQL

Wednesday, January 6, 2010 10:46 AM by Frans Nagel

The datetime.now on the webserver may be different than the GETDATE() on the database so it's not the same.