none
Bug with "Auto Generated Value" when Updating? RRS feed

  • Question

  • It seems like LINQ to SQL does not properly support the concept of the "UpdateDate" column, as I've defined it:

     

    - DateTime column, Not-Null, defaults to GETDATE()

     

    Here's how it should work:

    - When I insert a record, I don't want to set the column's value -- let the database set it to the default.

    - When I update a record, I want to explicitly set it to DateTime.Now.

     

    I haven't been able to get this to work as above, no matter how I configure the column in the Designer.

     


    If I set:

    • Auto Generated Value = False
    • Auto-Sync = Never 

    INSERTFails! - I get an SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.  This is because it's expecting me to explicitly set the column's value, which I don't want to do.

    UPDATE: Works Correctly -- The column gets updated to DateTime.Now.

     


    If I set:

    • Auto Generated Value = True
    • Auto-Sync = Never 

    INSERTWorks Incorrectly - LINQ does not set the value, as expected, but then it does a separate SELECT statement to do the AutoSync, which I don't care about.  Notice that Auto-Sync is set to "Never"!

    UPDATE: Works Incorrectly -- The column is never added to the SET clause of the query so it's never updated to DateTime.Now.  Also, LINQ does a separate SELECT statement to retrieve the value of the column.  Notice that Auto-Sync is set to "Never"!

     


    Is this a bug?  Ideally, I should be able to configure this column to AutoGenerate the value on Insert (allowing me to AutoSync it if I want), AND it should allow me to update the column's value later.  The problem seems to be with the Auto Generated Value property, which affects not only the Insert but also the Update, contrary to what its description ("Value is auto-generated in database on insert") says.

     

    Thanks!

    Alvaro

    Wednesday, May 7, 2008 1:31 AM

Answers

  • Hello Alvaro,

     

    I've tried to reproduce your issue.  When I set both AutoSync=Always and Auto Generated Value = true, I'm able to do both insert and update.  Here is my repro:

     

    I've created a database that contains the following table:

    Code Snippet

    create table forum(

    c Datetime not null default (getDate()),

    i int primary key)

     

     

    When I create my mapping class, I make sure my column C has the following attribute:

     

    Code Snippet

    [Column(Name="c", Storage="_C", DbType="DateTime NOT NULL", AutoSync=AutoSync.Always,IsDbGenerated=true)]

    public System.DateTime C

    {

    get

    {

    return this._C;

    }

    set

    {

    if ((this._C != value))

    {

    this._C = value;

    }

    }

    }

     

     

    Here is my code:

    Code Snippet

    Forum f = new Forum();

    f.I = 8;

    db.Forum.InsertOnSubmit(f);

    db.SubmitChanges();

    Forum g = new Forum();

    g.I = 9;

    g.C = DateTime.Today;

    db.Forum.InsertOnSubmit(g);

    db.SubmitChanges();

    var q = db.Forum.First();

    q.C = DateTime.Today;

    db.SubmitChanges();

     

     

    In my code, you can see that I am able to insert both a new row with no datetime column specified (Forum f) and a new row with a datetime column specified (Forum g).  And then I'm able to update the column g afterwards.

     

    I hope that helps.

     

    Thanks,

    Kathy

    Tuesday, May 13, 2008 12:03 AM

All replies

  • Hello Alvaro,

     

    I've tried to reproduce your issue.  When I set both AutoSync=Always and Auto Generated Value = true, I'm able to do both insert and update.  Here is my repro:

     

    I've created a database that contains the following table:

    Code Snippet

    create table forum(

    c Datetime not null default (getDate()),

    i int primary key)

     

     

    When I create my mapping class, I make sure my column C has the following attribute:

     

    Code Snippet

    [Column(Name="c", Storage="_C", DbType="DateTime NOT NULL", AutoSync=AutoSync.Always,IsDbGenerated=true)]

    public System.DateTime C

    {

    get

    {

    return this._C;

    }

    set

    {

    if ((this._C != value))

    {

    this._C = value;

    }

    }

    }

     

     

    Here is my code:

    Code Snippet

    Forum f = new Forum();

    f.I = 8;

    db.Forum.InsertOnSubmit(f);

    db.SubmitChanges();

    Forum g = new Forum();

    g.I = 9;

    g.C = DateTime.Today;

    db.Forum.InsertOnSubmit(g);

    db.SubmitChanges();

    var q = db.Forum.First();

    q.C = DateTime.Today;

    db.SubmitChanges();

     

     

    In my code, you can see that I am able to insert both a new row with no datetime column specified (Forum f) and a new row with a datetime column specified (Forum g).  And then I'm able to update the column g afterwards.

     

    I hope that helps.

     

    Thanks,

    Kathy

    Tuesday, May 13, 2008 12:03 AM
  • I try it With Linq To Sql and it doesnt work...

     

    This my DateCreation Mapping :

    Code Snippet

    [Column(Storage="_DateCreation",

    AutoSync=AutoSync.Always,

    DbType="DateTime NOT NULL",

    IsDbGenerated=true)]

    public System.DateTime DateCreation

    {

    get

    {

    return this._DateCreation;

    }

    set

    {

    if ((this._DateCreation != value))

    {

    this.OnDateCreationChanging(value);

    this.SendPropertyChanging();

    this._DateCreation = value;

    this.SendPropertyChanged("DateCreation");

    this.OnDateCreationChanged();

    }

    }

    }

     

     

     

    Here i Create a new Client

    My date of creation works right and return good Date from Server (SQL SERVER 2008 here)

    Code Snippet

    private void btnAdd_Click(object sender, RoutedEventArgs e)

    {

    Client c = new Client() { Nom = txtName.Text };

    ctx.Clients.InsertOnSubmit(c);

    ctx.SubmitChanges();

    MainGrid.DataContext = c;

    }

     

     

    Here i Try to Update (ans Set DateCreation to Datetime.Now)

     

    Code Snippet

    private void btnUpdate_Click(object sender, RoutedEventArgs e)

    {

    Client cli = MainGrid.DataContext as Client;

    cli.DateCreation = DateTime.Now;

    cli.Nom = txtName.Text;

    ctx.SubmitChanges();

    Bind();

    }

     

     

    And i Get one Exception :

     

    Value of member 'DateCreation' of an object of type 'Client' changed.
    A member that is computed or generated by the database cannot be changed.

     

     

     

    Monday, June 30, 2008 3:39 PM
  •  

    I had a simular problem and resolved it by creating a bare minimum version of the data class used for inserts only.  That way I can rely on the back end to insert any default values. 

     

    So I have a [TravelCards] class used for Select, Update and Delete and I use the [NewTravelCards] class for Inserts and the [NewTravelCards] class only has 3 of the 40 or so fields found in the full TravelCards table.

    Thursday, December 4, 2008 3:22 PM
  • Dear Kathy,

    I followed your comments and the exception (super_bly has reported) does not happen however no update happens either. in fact I debugged the LINQ2SQL Designer generated code and I think the problems comes from "SendPropertyChanging" as in

    [Column(Storage="_DT", AutoSync=AutoSync.Always, DbType="DateTime NOT NULL", IsDbGenerated=true)]
    public System.DateTime DT
    {
    	get
    	{
    		return this._DT;
    	}
    	set
    	{
    		if ((this._DT != value))
    		{
    			this.OnDTChanging(value);
    			this.SendPropertyChanging();
    			this._DT = value;
    			this.SendPropertyChanged("DT");
    			this.OnDTChanged();
    		}
    	}
    }

    I tried different cases setting IsDbGenerated and AutoSync to any possible values but no result. either getting exception or no update happens.

    would you please let us know how you could solve that problem?

    Regards
    Wednesday, February 10, 2010 8:43 PM