views:

1591

answers:

2

Rightly or wrongly, I am using unique identifier as a Primary Key for tables in my sqlserver database. I have generated a model using linq to sql (c#), however where in the case of an identity column linq to sql generates a unique key on inserting a new record for guid /uniqueidentifier the default value of 00000000-0000-0000-0000-000000000000.

I know that I can set the guid in my code: in the linq to sql model or elsewhere, or there is the default value in creating the sql server table (though this is overridden by the value generated in the code). But where is best to put generate this key, noting that my tables are always going to change as my solution develops and therefore I shall regenerate my Linq to Sql model when it does.

Does the same solution apply for a column to hold current datetime (of the insert), which would be updated with each update?

A: 

There's two things you can do:

  • either just generate the GUID in your C# client side code and use that value
  • create a DEFAULT constraint on the GUID column in SQL Server that defaults to newid() for the column - the SQL Server will make SURE to always add a default - unless you specify a value yourself

As for the self-updating date/time columns - here you probably have to use either client-side logic to do that, or if you want to do it on SQL Server, you'll have to write a trigger. That's really the only way to update a specific column everytime the row gets updated - there's no "autoupdate" constraint or anything like this (and the default constraint only work on INSERTs, not updates).

Something like this might work:

CREATE TRIGGER TRG_UpdateDateTimestamp
ON (your table name)
AFTER UPDATE 
AS
    IF NOT UPDATE(DateTimeStamp)
    BEGIN
       UPDATE (yourtablename) 
       SET DateTimeStamp = GETDATE()
       WHERE EXISTS (SELECT * FROM inserted AS i 
                     WHERE i.OID = (yourtable).OID)
   END

Marc

marc_s
Thanks Marc, the problem with the default linq to sql generated code is that it generates a the 0s guid, so the default created by sqlserver is overwritten ( I believe that I have found a solution to this - I've added an answer myself). The trigger looks useful though and I'll give it a try, though I feel like I need a more generic solution to avoid having to write this code for every table. Perhaps I should have a generic Updates table with cols of tname, id and datetime, have a trigger on each table to run a generic stored procedure to insert a new record into that table? Is that viable?
Richbits
For some reason my answer is not appearing the answer can be found here: http://codecadet.strongerforyou.com/post/Linq-to-SQL-using-Unique-Identifiers.aspx
Richbits
+3  A: 

As you noted in you own post you can use the extensibility methods. Adding to your post you can look at the partial methods created in the datacontext for inserting and updating of each table. Example with a table called "test" and a "changeDate"-column:

partial void InsertTest(Test instance)
{
    instance.idCol = System.Guid.NewGuid();
    this.ExecuteDynamicInsert(instance);
}

partial void UpdateTest(Test instance)
{
    instance.changeDate = DateTime.Now;
    this.ExecuteDynamicUpdate(instance);
}
Stephan Keller
Thanks Stephan, I think that this would probably be my preferred approach, UpdateTest(...) looks good, however one concern in using InsertTest over OnCreated is where in the object lifecycle that might occur in relation to other related objects. Might I create the links to other objects using the default 0s where I have not generated the guid yet?
Richbits
AFAIK if your related objects are attached as an entity-set to your parent-object the foreign keys of the related objects are updated after the insert. However, I find nothing wrong with setting the guid in the OnCreate-method. I use the Insert-Method because I need to set a unique continous number (e.g. an invoice number) that is taken from a number-pool in the database. For unique keys I use identity columns.
Stephan Keller
Thanks, I've tried this out and it seems to work OK. I had used the original OnCreated() method, but have now change to this approach.
Richbits
Just to follow up on this. This approach does not work for Guids as for a collection duplicate 0s keys are added before the insert method is called, therefore an exception is thrown. I have another approach, which I think I shall use for guids: sqlserver default value to newid(), then in linqtosql set auto generated value property to true. This has to be done on each generation of the model, but this is fairly simple.
Richbits