Linq to Sql - Entities As Static Resources?

Here is what I would like to have:

public class WorldCountries 
{ 
  public static Country China = new Country(new Guid("6d4b5217-6fe6-11dc-8314-0800200c9a66"), "China"); 
  public static Country Mexico = new Country(new Guid("6d4b5219-6fe6-11dc-8314-0800200c9a66"), "Mexico"); 
  public static Country UnitedStates = new Country(new Guid("6d4b521a-6fe6-11dc-8314-0800200c9a66"), "United States");
 



  public static List Countries 
  { 
    get 
    { 
      List defaultValues = new List(); 
      defaultValues.Add(China); 
      defaultValues.Add(Mexico); 
      defaultValues.Add(UnitedStates); 
      return defaultValues; 
    } 
  } 
}
 
public class UnitedStates_States  
{  
  public static State Alabama = new State(new Guid("4f4588b0-6fe6-11dc-8314-0800200c9a66"), WorldCountries.UnitedStates, "AL", "Alabama");  
  public static State Alaska = new State(new Guid("4f4588b1-6fe6-11dc-8314-0800200c9a66"), WorldCountries.UnitedStates, "AK", "Alaska");  
  public static State Arizona = new State(new Guid("4f4588b3-6fe6-11dc-8314-0800200c9a66"), WorldCountries.UnitedStates, "AZ", "Arizona");  
  public static State Arkansas = new State(new Guid("4f4588b4-6fe6-11dc-8314-0800200c9a66"), WorldCountries.UnitedStates, "AR", "Arkansas");  
  public static State California = new State(new Guid("4f4588b5-6fe6-11dc-8314-0800200c9a66"), WorldCountries.UnitedStates, "CA", "California");
 



  public static List States  
  {  
    get  
    {  
      List defaultValues = new List();  
      defaultValues.Add(Alabama);  
      defaultValues.Add(Alaska);  
      defaultValues.Add(Arizona);  
      defaultValues.Add(Arkansas);  
      defaultValues.Add(California);

      return defaultValues;  
    }  
  }  
}

  

Then I can CreateDatabase() on my data context and make a call to a function like this:

public void AddAllDefaultValues()  
{  
  // Add world countries first because all relations depend on them  
  AddNewValues(_DataContext.Countries, WorldCountries.Countries);  
  // Add states by country  
  AddNewValues(_DataContext.States, UnitedStates_States.States);  
} 
 


public void AddNewValues(Table Table, List Values)  
  where TEntity : class  
{  
  if (Table.Context != _DataContext)  
  {  
    throw new Exception("Table must be in the same context as the DefaultValueManager");  
  }  
  var currentValues = from values in Table  
                      select values;  
  List currentValuesList = currentValues.ToList();  
  IEnumerable valuesToAdd = Values.Except(currentValuesList); 

  try  
  {  
    Table.AddAll(valuesToAdd);  
    _DataContext.SubmitChanges();  
  }  
  catch (InvalidOperationException ioe)  
  {  
    throw ioe;  
  }  
}

  


So far so good. Everything seems to be working, unit tests verify that my database is created, check that the default values have indeed been populated and are happy.

Now I get into my application and I want to create a new State entity, so I new up the object, call add on my data context, submit changes and... error... huh?

"System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_dbo.Countries'. Cannot insert duplicate key in object 'dbo.Countries'."

  

Here some example code (copied from failing unit test):

State expected = new State(new Guid("694df2b2-7002-11dc-8314-0800200c9a66"), WorldCountries.UnitedStates, "TS", "Test State"); 

_DataContext.States.Add(expected); 
_DataContext.SubmitChanges();

  


The data context is stashed in a static variable in the test class, passed to the manager class that does the DatabaseCreate and AddAllDefaultValues calls.

The same data context is then used in the tests to perform the same operation against the same objects... what's going on?

Here is how I can get around this, but it's far from ideal and renders all the static elements useless:

var countries = from c in _DataContext.Countries  
                where c.CountryName == "United States"  
                select c; 
Country country = countries.SingleOrDefault();

State expected = new State(new Guid("694df2b2-7002-11dc-8314-0800200c9a66"), country, "TS", "Test State");

_DataContext.States.Add(expected); 
_DataContext.SubmitChanges();

  


A comparison between the static entities does reveal some differences but I haven't (yet) identified the key item(s) but the net result is this statement attempting to execute and re-add the country which is what causes the Primary Key constraint to break.

INSERT INTO [dbo].[Countries]([CountryId], [CountryName]) VALUES (@p0, @p1) 
-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [6d4b521a-6fe6-11dc-8314-0800200c9a66] 
-- @p1: Input String (Size = 13; Prec = 0; Scale = 0) [United States] 
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

  

[Copied from output ala Kris Vandermotten]


The only idea that I have at the moment (untested) is to expose each item instead as a static property and doing a lookup and return on demand but it seems to me that there should be a way to enable this with some state flag modifications to the static entities or something.

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.