Do we have to use GUIDs in LinqToSql application to uniquely identify objects

Question

I have inherited a LinqToSql application which is making use of GUID keys for objects. I'd rather use conventional identity fields - much easier for people to use, understand and communicate. However there is some business logic that requires the application to identify unique objects before they're persisted to the DB which is why GUIDs where used in the first place.

Another issue we're having is with fragmented indexes - AFAIK we can't create sequential GUIDs in .Net code.

As this is my first exercise in LinqToSql I'd like to know how others have addressed this issue.

BTW there is no need for the data between multiple servers to be combined - the main (only) reason that I've used GUID keys in the past.

Answer 1

No, you don't have to use Guids, you can use any key type you'd like.

If you are stuck with Guids consider having the database generate them sequentially for you by making the default binding for the pk field newsequentialid(). This will eliminate fragmentation in your clustered index at least. You need to make a few modifications to the .dbml if you do this. On the key field in the .dbml Auto Generated Value = true and Auto-Sync = OnInsert

As far as generating the value before you insert to the database I don't see how using an identity field helps you. You will still have to insert to the database to reliably get the correct value. (Identity columns will have the same Autogenerated/Autosync settings as above)

Ints or Guids, you should be able to wrap the insert in a transaction, insert the record, grab the new key value, run your business logic and if it fails roll back the newly inserted record.