SQLMetal DataContext Associations Not Generated

I am generating the 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 defined between them, with [AccountMaster].AccountNumber being referenced from [AccountCodes].

Adding a LINQ-to-SQL file in VS2008 and dragging these tables onto the DBML design view appropriately generates a collection of AccountNotes within the AccountMaster class. Meanwhile, using SQLMetal to generate the DataContext does not produce any 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 am following the guide at

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

I have tried to first generate the DBML file using 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 generate the associations. In fact, inspecting the DBML files from SQLMetal vs. 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>

So the association is missing already at the DBML step.

Since the database contains tons of tables/sprocs, it is not practical to use the designer to regenerate the DataContext classes. How do I make SQLMetal generate associations correctly?

EDIT:

Running SQLMetal on the entire database, I realized that SOME entity associations are being generated correctly. The foreign key on 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've noticed that the associations correctly created are those which have an ON DELETE CASCADE/UPDATE rule. Is it then nonsensical to generate associations which do not have this rule strictly defined at the database level?

回答1:

Sounds like either there is a bug in SQLMetal or some inconsistency in the database.

You'll get different results extracting as it's two different codebases (don't ask).

One thing you could do is turn on SQL tracing to observe the TSQL DDL commands SqlMetal sends to the database to retrieve the list. They are a combination of INFORMATION_SCHEMA queries with various joins.

It sounds like one part of the join required for seeing the associations is misfiring. If you copy the queries it sends to SQL Server into a SQL Management Studio window and run them you'll probably also see the associations missing.

Once you've confirmed that's the case you could try turning some of the joins into LEFT joins to see which part is failing (or remove some WHERE criteria). At some point the query will return the associations.

You may then be able to tweak the schema so it works (the source for SQLMetal isn't available :( )

回答2:

We had a similar issue where a table had a redundant index that was defined as a unique, non-clustered index on the column that is the tables primary key that is already the clustered index. When we tried to delete the non-clustered index, SQL Server resisted because there were foreign keys depending on it. We had to drop all the foreign keys, delete the index, recreate the foreign keys (now depending on the actual PK) and then SQL Metal was happy.