Attention: We have retired the ASP.NET Community Blogs. Learn more >

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

 

12 Comments

  • 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

  • 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

  • Yep sorry I meant OnCreated. Will update the post.


    Thanks

    Stefan

  • 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.

  • 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.  

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

  • 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

  • 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.

  • 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!

  • 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?

  • 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.

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

Comments have been disabled for this content.