未生成SQLMetal DataContext关联 [英] SQLMetal DataContext Associations Not Generated

问题描述

我正在为数据库生成LINQ-to-SQL DataContext和实体类.该数据库有几个表，其中两个是-[AccountMaster]和[AccountCodes].它们之间定义了一个外键关系，[AccountMaster] .AccountNumber是从[AccountCodes]引用的.

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].

在VS2008中添加LINQ-to-SQL文件并将这些表拖动到DBML设计视图中，会在AccountMaster类内适当地生成AccountNotes的集合.同时，使用SQLMetal生成DataContext不会产生任何EntitySet集合.

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.

设计器输出:

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

SQLMetal输出:

SQLMetal output:

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

我正在按照

我尝试首先使用SQLMetal生成DBML文件，然后从生成的DBML生成DataContext.cs文件:

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

这不会生成关联.实际上，从SQLMetal和设计器视图检查DBML文件:

This does not generate the associations. In fact, inspecting the DBML files from SQLMetal vs. the designer view:

Design View DBML:

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:

SQLMetal DBML:

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

因此在DBML步骤中该关联已经丢失.

So the association is missing already at the DBML step.

由于数据库包含大量的表/存储过程，因此使用设计器重新生成DataContext类是不切实际的.如何使SQLMetal正确生成关联?

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?

在整个数据库上运行SQLMetal，我意识到某些实体关联正在正确生成. AccountNotes上的外键定义为:

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

我注意到正确创建的关联是具有ON DELETE CASCADE/UPDATE规则的关联.那么生成没有在数据库级别严格定义此规则的关联是没有意义的吗?

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?

推荐答案

听起来像SQLMetal中存在错误或数据库中存在某些不一致之处.

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).

您可以做的一件事是打开SQL跟踪，以观察SqlMetal发送到数据库以检索列表的TSQL DDL命令.它们是INFORMATION_SCHEMA查询与各种联接的组合.

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.

听起来像是看到关联所需的联接的一部分是错误的.如果将查询发送到SQL Server的查询复制到SQL Management Studio窗口中并运行它们，您可能还会看到关联丢失.

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.

一旦确认是这种情况，您可以尝试将某些联接转换为LEFT联接，以查看哪个部分发生故障(或删除一些WHERE条件).在某个时候查询将返回关联.

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.

然后，您可以调整模式，使其起作用(SQLMetal的源不可用:()

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

这篇关于未生成SQLMetal DataContext关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！