Linq to SQL - UniqueIdentifier Issue

Exploring new technologies (especially betas) is always good fun.
One of my current toys is DLinq also known as Linq to SQL.
Let me just say, this stuff is pretty cool!

There are a couple of interesting scenarios to explore.
1) starting with an existing data base, one can use Linq to SQL to generate objects to match your database schema pre-generated with all the
necessary attributes and associations. The Linq to SQL DSL supports drag and drop for tables and stored procs and does some cool auto-association
mappings by reading into the foreign key relationships defined in the schema.
(I've found that it does require some re-naming post-gen for plurality issues)

2) Starting with a set of related objects, one can markup the objects with the appropriate attributes to allow Linq to bind them to data sources
with compatible schemas.

One thing that I find very exciting with either approach is the CreateDatabase() and DeleteDatabase() options off the DataContext.
I 'may' write another post to cover this in more detail but think on this:


[In ClassInitialize]
Once the DataContext is definied you can instantiate a new copy with a connection string pointing to an Express database that doesn't exist.
A simple call to CreateDatabase() and now you have a blank database with a shema that supports your objects.
Add some object with known values via DataContext.TableName.Add(Object).

[In TestMethod]
* Write some unit tests using your Linq enabled objects *

[In ClassCleanup]
A final call to DeleteDatabase()

Use a different database name for each Unit Test Class and it becomes order independant.


That being said, the real reason I decided to write up this post was because of a confusing
error message I was getting that took SEVERAL hours to finally track down.

So in the true blogging spirit here is the Problem/Solution (hack till rtm) that seems to be working for the moment.

The Auto-gen code for a table schema that includes a UniqueIdentifier Guid Key / Identity Column is fubar at the moment.
My starting configuration was (key properties):

Type:    System.Guid
Auto Generated Value: True
Auto-Sync:   Never
Primary Key:   True
Read Only:   True

  

Now, this configuration works great if you're using an int identity column but with UniqueIdentifier, something goes terribly wrong:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.Linq.dll
ExMessage: Explicit conversion from data type uniqueidentifier to decimal is not allowed.

  

This took me a long time to track down since the sql generation happens inside of my DataContext and for inserts I didn't see any
way to get access to the executing sql (no... I didn't think to SQL trace, can't see the forest through the trees or some such).
Then I stumbled across a couple very informative posts in rapid succession.

First, I found that I wasn't alone and simply mixing up config after reading this post by WooBoo.
In the comment our friend WooBoo makes reference to a work around (hack) but doesn't go into implementation:

'I can assign new guid to ID columns just after creating new instances but it adds new meaningless lines of code…'

Well you can't add it directly into a generated class because it will just get overwritten... we'll come back to this.

Second, I found a neat little class that lets you dump the DataContext log to the debug window!
Thanks to Kris Vandermotten for the contribution as it made the following easily available:

Using our cool new log tool here is the sql that was generated for my starting configuration:

INSERT INTO [dbo].[Topics]([TopicName], [Description], [IsGlobal], [IsApproved], [IsActive]) VALUES (@p0, @p1, @p2, @p3, @p4)

SELECT [t0].[TopicId]
FROM [dbo].[Topics] AS [t0]


WHERE (CONVERT(Decimal(29,4),[t0].[TopicId])) = (SCOPE_IDENTITY()) <-- this line is bad!!


-- @p0: Input String (Size = 7; Prec = 0; Scale = 0) [ASP.Net]
-- @p1: Input AnsiString (Size = 8; Prec = 0; Scale = 0) [My Topic]
-- @p2: Input Boolean (Size = 0; Prec = 0; Scale = 0) [True]
-- @p3: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False]
-- @p4: Input Boolean (Size = 0; Prec = 0; Scale = 0) [True]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

  

Turning off the Auto Generated Value flag doen't seem to help because then the key is a null guid:

INSERT INTO [dbo].[Topics]([TopicId], [TopicName], [Description], [IsGlobal], [IsApproved], [IsActive]) VALUES (@p0, @p1, @p2, @p3, @p4, @p5)

-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [00000000-0000-0000-0000-000000000000] <-- this won't work so well either

-- @p1: Input String (Size = 7; Prec = 0; Scale = 0) [ASP.Net]
-- @p2: Input AnsiString (Size = 8; Prec = 0; Scale = 0) [My Topic]
-- @p3: Input Boolean (Size = 0; Prec = 0; Scale = 0) [True]
-- @p4: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False]
-- @p5: Input Boolean (Size = 0; Prec = 0; Scale = 0) [True]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

  

The Auto Generated Value flag does however set you up to implement your own Id generation scheme using partial classes.
I'm defaulting here to a simple NewGuid() approach but really anything could be used here, think GUID factory that guarantees unique values
by incrementing some of the byte values or that derives from a full System.DataTime converted to GUID.

Create a new class file using the same name as your Table Mapping Object and in the same NameSpace and add something similar to the following:

  public partial class Topic
  {
    partial void OnCreated()
    {
      _TopicId = Guid.NewGuid();
    }
  }

  

This takes advantage of some of the built in extensibility features of the generated partial classes to extend new functionality OnCreated which yields the following:

INSERT INTO [dbo].[Topics]([TopicId], [TopicName], [Description], [IsGlobal], [IsApproved], [IsActive]) VALUES (@p0, @p1, @p2, @p3, @p4, @p5)
-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [3220b7e7-3d69-4d64-876a-5d20b74e5344]
-- @p1: Input String (Size = 7; Prec = 0; Scale = 0) [ASP.Net]
-- @p2: Input AnsiString (Size = 8; Prec = 0; Scale = 0) [My Topic]
-- @p3: Input Boolean (Size = 0; Prec = 0; Scale = 0) [True]
-- @p4: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False]
-- @p5: Input Boolean (Size = 0; Prec = 0; Scale = 0) [True]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

  

And now it works.