更改LINQ到SQL插入的顺序顺序、LINQ、SQL

2023-09-06 09:51:04 作者：一颗尐疍黄

我有两个LINQ to SQL类，直板和DeliciousCandyBar映射到相同名称的表中的SQL Server。

I have two LINQ to SQL classes, CandyBar and DeliciousCandyBar that map to tables of the same name in SQL Server.

有直板和DeliciousCandyBar之间的0..1关系。也就是说，直板可能有0或1 DeliciousCandyBars。相反一个DeliciousCandyBar有且只有一个直板。

There is a 0..1 relationship between CandyBar and DeliciousCandyBar. i.e, A CandyBar can have 0 or 1 DeliciousCandyBars. Conversely a DeliciousCandyBar has exactly one CandyBar.

在LINQ to SQL类，它们看起来（基本上），如

In LINQ to SQL class, they look (basically) like

class CandyBar {
  public int Id { get;set;} // this is primary key w/ autoincrement identity
  public string Name {get;set;}
  public EntityRef<DeliciousCandyBar> DeliciousCandyBar {get;set;}
}

class DeliciousCandyBar {
  public int DeliciousnessFactor {get;set;}
  public int CandyBarId {get;set;} // FK to candyBar Id
  public EntityRef<CandyBar> CandyBar {get;set;} // the association property of the FK
}

要养活数据库（通过l2sql），我的履带出去，发现糖果和美味的糖果酒吧。

To feed the database (via l2sql), my crawler goes out and finds candy bars and delicious candy bar.

不过，与第一美味的直板我履带插入到CandyStoreDataContext，在DataContext抛出时的SubmitChanges被称为一个例外。

But, with the first delicious candy bar my crawler inserts into the CandyStoreDataContext, the DataContext throws an exception when SubmitChanges is called.

履带运行以下code一个美味的糖果酒吧。请注意，这是一个例子。确切的过程是比较复杂的，我使用自定义的DSL履带式的吐出这个对象的结构。本质上，以下的处理。

The crawler runs the following code for one delicious candy bar. Please note this is an example. The exact process is more complex and I use a custom DSL crawler that spits out this object structure. Essentially, the following is performed.

var dc = CandyStoreDataContext();
var bar = new CandyBar() {
    Name = "Flake",
    DeliciousCandyBar = new DeliciousCandyBar() {
      DeliciousnessFactory = 12
    }
};

dc.CandyBars.InsertOnSubmit(bar);

dc.SubmitChanges();

在的SubmitChanges（），则抛出SQLException消息INSERT语句冲突与外键约束FK_CandyBar_DeliciousCandyBar。在数据库CandyStoreData的冲突发生，表'dbo.DeliciousCandyBar，列CandyBarId'。

On SubmitChanges(), a SqlException is thrown with the message "The INSERT statement conflicted with the FOREIGN KEY constraint FK_CandyBar_DeliciousCandyBar. The conflict occured in database CandyStoreData, table 'dbo.DeliciousCandyBar', column 'CandyBarId'".

这个问题变得清晰，当我甩了CandyStoreDataContext.Log到Console.Out，生成的insert语句是南辕北辙。 LINQ到SQL试图先插入DeliciousCandyBar（它试图设置在CandyBarId列一个无效的值），而不是先插入直板造型。

The problem became clear when I dumped the CandyStoreDataContext.Log to Console.Out, the generated insert statements were round the wrong way. LINQ to SQL was trying to insert the DeliciousCandyBar first (which tried to set an invalid value in CandyBarId column), rather than inserting CandyBar first.

我的问题是，我如何得到的LINQ to SQL换用插入语句的顺序？

My question is, how do I get Linq to SQL to swap with the order of the insert statements?

我本以为（错误地）的LINQ到SQL会知道依赖关系的方向，做它周围的其他方法。

I had assumed (incorrectly) that LINQ to SQL would know the direction of relationship dependency and do it the other way around.

更新：

这个帖子暗示我该协会围绕走错了路。但是，这是没有道理给我。是有意义时在数据库中建模。这怎么可能围绕做的另一种方式。

This post suggests I have the Association the wrong way around. But that doesn't make sense to me. It makes sense when modelled in the database. How could this be done the other way around.

在直板，对DelciousCandyBar物业的关联属性 [公会（NAME =DeliciousCandyBar_CandyBar，存储=_ DeliciousCandyBar，ThisKey =ID，OtherKey =CandyBarId，IsForeignKey = TRUE）]

On CandyBar, the association attribute on DelciousCandyBar property is [Association(Name="DeliciousCandyBar_CandyBar", Storage="_DeliciousCandyBar", ThisKey="Id", OtherKey="CandyBarId", IsForeignKey=true)]

在DeliciousCandyBar，在直板物业的关联属性 [公会（NAME =DeliciousCandyBar_CandyBar，存储=_直板，ThisKey =CandyBarId，OtherKey =ID，IsUnique = TRUE，IsForeignKey = FALSE）]

On DeliciousCandyBar, the association attribute on CandyBar property is [Association(Name="DeliciousCandyBar_CandyBar", Storage="_CandyBar", ThisKey="CandyBarId", OtherKey="Id", IsUnique=true, IsForeignKey=false)]

好了，现在我很困惑，为什么是第二个属性标记为外键。

Okay, now I'm confused, why is the second attribute marked as the foreign key.

我会尝试重新创建在SQL Management Studio中的直板和DeliciousCandyBar之间的关系

I'm going to try recreating that relationship between CandyBar and DeliciousCandyBar in SQL Management studio

更新2

好吧，我试图创建的关系是双向的。而SSMS使得它可以很明确了主键的位置（CandyBar.Id）。我有它正确的第一次。否则，级联会倒退。

Okay, I tried creating the relationship both ways. And SSMS makes it really clear where the primary key is located (CandyBar.Id). I had it right the first time. Otherwise, the cascading would go backwards.

更改LINQ到SQL插入的顺序顺序、LINQ、SQL

推荐答案