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.