none
Primary key column of type uniqueidentifier cannot be generated by the server RRS feed

  • Question

  • Hi all,

    When I am trying to insert a row into a table (whose primary key is GUID and is auto-generated at the database level) using LINQ to SQL, I am getting the following error:

    Primary key column of type uniqueidentifier cannot be generated by the server

    Why am I encountering this error? Any pointers?

    Thanks!
    Monday, October 12, 2009 10:24 PM

All replies

  • Hi diffident,

     

    For the uniqueidentifier primary key columns, if it has some default value at the database, we need to set the certain PK property as Auto Generated Value to True and Auto-Sync to OnInsert.  And the property definition in the Designer file would be:

    ===========================================================================
    [Column(Storage="_MyID", AutoSync=AutoSync.OnInsert, DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]

    public System.Guid MyID
    ===========================================================================

     

    For example, I have a data table named GUIDTest which has two columns MyID (PK, uniqueidentifier) and Name (nvarchar(50)).  The MyID column has the default value (newid()) at the database side.  The following codes work fine to insert data records:

    ===========================================================================

    MyDataContext db = new MyDataContext();

    GUIDTest gt = new GUIDTest();

    gt.Name = "Michael";

    db.GUIDTests.InsertOnSubmit(gt);

    db.SubmitChanges();

    ===========================================================================

    It seems that the uniqueidentifier value is not generated correctly at the database side.  Could you please provide us more detailed information about how the uniqueindentifier column value is generated?  Do you use some system or custom functions?   If it is convenient for you to provide a demo database file, that would be quite helpful.   My mail address is v-micsun @ microsoft.com. 

     

     

    Hope you have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, October 13, 2009 2:06 AM
    Moderator
  • Hi Lingzhi Sun,

    Thanks a lot for your response. I will perform the steps that you have suggested and will get back to you.

    Thanks!
    Tuesday, October 13, 2009 2:42 PM
  • Lingzhi Sun,

    I tried setting the Auto Generated Value to true and Auto-Sync to Always. I verified the property definition in the designer file and it looked correct (see below):

    [Column(Storage="_UUID", AutoSync=AutoSync.Always, DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]
    UniqueIdentifier column is generated at the database level using "newid()" function.

    When I debug the project, I am getting the following error:
    Incorrect AutoSync specification for member 'UUID'

    Please suggest what kind of demo are you expecting. Find below the code snippet where I am inserting row using LINQ to SQL.

                var db = new NetDGadgetDataContext();
    
                var t = new Adm_SP_GadgetView1()
                {
                    Owner_UUID = ownerID,
                    Owner_ID = 45,
                    tXML = outXML,
                    SetXML = setDirectivesXML,
                    Description = description,
                    CreatedDate = DateTime.Now,
                    ModifiedDate = DateTime.Now
                };
    
                db.Adm_SP_GadgetView1s.InsertOnSubmit(t);
                db.SubmitChanges();
    Note that I am not passing UUID while inserting because it is auto-generated at the database level.

    Thanks!
    Tuesday, October 13, 2009 3:18 PM
  • Follow up to my above response.

    If I modify the above code to include UUID and turn off AutoSync and AutoGen as follows:

    var db = new NetDGadgetDataContext();

                var t = new Adm_SP_GadgetView1()
                {
                    UUID = Guid.NewGuid(),
                    Owner_UUID = ownerID,
                    Owner_ID = 45,
                    tXML = outXML,
                    SetXML = setDirectivesXML,
                    Description = description,
                    CreatedDate = DateTime.Now,
                    ModifiedDate = DateTime.Now
                };

                db.Adm_SP_GadgetView1s.InsertOnSubmit(t);
                db.SubmitChanges();

    it inserts the GUID fine. If I comment out that line, it inserts 000000-0000-....... as GUID.
    Tuesday, October 13, 2009 3:26 PM
  • Hi diffident,

    Sorry for my mistake.  The UUID is the primary key column, so please set the Anto-Sync to OnInsert or Default instead of Always.   Does it work now?   For the Default value, please see the additional references.

    Additional references about the Auto-Sync attribute:
    http://weblogs.asp.net/zeeshanhirani/archive/2008/06/29/synchronizing-autogenerated-properties-after-submitchanges.aspx
    http://msdn.microsoft.com/en-us/library/system.data.linq.mapping.autosync.aspx
      
     

    Have a nice day!


    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, October 13, 2009 3:34 PM
    Moderator
  • If I set Auto-Sync to OnInsert and Auto-Gen to true, I get the following error:

    The primary key column of type 'UniqueIdentifier' cannot be generated by the server.

    NOTE: Interestingly, I do not see a "Default" as one of the options in the Auto-Sync. drop down list.

    Thanks!
    Tuesday, October 13, 2009 4:08 PM
  • Hi diffident,

     

    Similar codes and LINQ to SQL .dbml work fine at my lab.  Would you mind letting me know what version of the SQL Server are you using?   Could you please send me the .dbml file and a demo database file for further research?   My mail address is v-micsun @ microsoft.com. 

     

    Besides, the value AutoSync.Default is declared in the enum.  And the Default option means that if the column is marked with IsDbGenerated than update the column on the insert of an entity. If the column is IsVersion column like I have a timestamp column, than update the column on insert or update operation.  We cannot set a column as AutoSync.Default in the O/R designer. 

    ==============================================================================
    [Column(Storage="_MyID", AutoSync=AutoSync.Default, DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true, IsDbGenerated=true)]

    public System.Guid MyID
    ==============================================================================


    Have a nice day!



    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, October 14, 2009 6:20 AM
    Moderator
  • Hi diffident,

     

    How is the problem going now?  

    Have a nice day!


    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, October 20, 2009 12:47 AM
    Moderator