SQLMetal DataContext Connections Not Generated

Question

I am creating LINQ-to-SQL DataContext and entity classes for a database. The database has several tables, two of which are [AccountMaster] and [AccountCodes]. There is a foreign key relationship between them, and [AccountMaster] .AccountNumber refers to [AccountCodes].

Adding the LINQ-to-SQL file to VS2008 and dragging these tables into the DBML design project creates the collection of accounts in the AccountMaster class accordingly. Meanwhile, using SQLMetal to generate a DataContext does not create EntitySet collections.

Designer Output:

[Table(Name="dbo.A01_AccountMaster")] public partial class A01_AccountMaster //... { //... private long _AccountNumber; private EntitySet<A01aAccountNote> _A01aAccountNotes; //... } 

SQLMetal output:

 [Table(Name="dbo.A01_AccountMaster")] [DataContract()] public partial class A01_AccountMaster //... { //... private long _AccountNumber; //... } 

I follow the guidance in

http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx

I tried to generate the DBML file first with SQLMetal, and then generate the DataContext.cs file from the resulting DBML:

 sqlmetal.exe /server:srv /database:db /user:usr /password:pwd /sprocs /namespace:AccountContext /context:AccountContext /dbml:AccountContext.dbml /language:csharp /serialization:unidirectional sqlmetal.exe /sprocs /namespace:AccountContext /context:AccountContext /code:AccountContext.cs /language:csharp /serialization:unidirectional AccountContext.dbml 

This does not create an association. In fact, checking DBML files from SQLMetal compared to the designer view:

Design View DBML:

 <Type Name="A01_AccountMaster"> <!-- ... --> <Column Name="AccountNumber" Type="System.Int64" DbType="BigInt NOT NULL" CanBeNull="false" /> <Association Name="A01_AccountMaster_A01aAccountNote" Member="A01aAccountNotes" ThisKey="AccountNumber" OtherKey="AccountNumber" Type="A01aAccountNote" /> <!-- ... --> </Type> 

SQLMetal DBML:

 <Type Name="A01_AccountMaster"> <!-- ... --> <Column Name="AccountNumber" Type="System.Int64" DbType="BigInt NOT NULL" CanBeNull="false" /> <!-- ... --> </Type> 

Thus, the association is already absent at the DBML stage.

Since the database contains many tables / sprocs, it is not practical to use a constructor to regenerate the DataContext classes. How to force SQLMetal to create associations correctly?

EDIT:

Running SQLMetal in the entire database, I realized that SOOME entity associations are generated correctly. The foreign key in AccountNotes is defined as:

 ALTER TABLE [dbo].[A01aAccountNotes] WITH CHECK ADD CONSTRAINT [FK_A01aAccountNotes_A01_AccountMaster] FOREIGN KEY([AccountNumber]) REFERENCES [dbo].[A01_AccountMaster] ([AccountNumber]) GO ALTER TABLE [dbo].[A01aAccountNotes] CHECK CONSTRAINT [FK_A01aAccountNotes_A01_AccountMaster] GO 

EDIT2:

I noticed that correctly created associations are those that have the DEL DELETE CASCADE / UPDATE rule. Is it pointless to create associations that do not have this rule, strictly defined at the database level?

Answer 1

It looks like either an error in SQLMetal or some inconsistency in the database.

You will get different results by extracting from two different codebases (don't ask).

One thing you can do is enable SQL tracing to monitor DDQL TSQL commands. SqlMetal sends to the database to retrieve the list. They are a combination of INFORMATION_SCHEMA queries with various associations.

It seems like one part of the connection needed to view associations skips the gaps. If you copy the queries sent by SQL Server to the SQL Management Studio window and run them, you will also see that there are no associations.

As soon as you confirm that a case you might try turning some of the joins into LEFT joins to see which part does not work (or remove some WHERE criteria). At some point, the query will return associations.

Then you can configure the schema so that it works (source for SQLMetal is not available :()

Answer 2

We had a similar problem when the table had a redundant index, which was defined as a unique, non-clustered index in the column, which is the main key of the tables, which is already a clustered index. When we tried to remove a non-clustered index, SQL Server resisted because there were foreign keys depending on it. We had to drop all foreign keys, delete the index, restore foreign keys (now depending on the real PK), and then SQL Metal was happy.