Linq problem with inserting new rows that have references to existing records

Linq problem with inserting new rows that have references to existing records

牵你的手,一向走下去 发布于 2021-11-30 字数 1428 浏览 835 回复 4 原文

(I believe this is the same problem as this one, but there's no answer there, and I think I can express the problem better here...)

I have two Linq-to-SQL classes, State and County, where County has a FK to State. Here's some test code:

State s = State.GetState("NY"); // here I do a load of a State class via the Linq DataContext
County c = new County();
c.Name = "Rockland";
c.State = s;
MyDataContext.GetTable<County>().InsertOnSubmit(c);
MyDataContext.SubmitChanges(); // throws an exception

The exception thrown is "Violation of PRIMARY KEY constraint 'PK_State'. Cannot insert duplicate key in object 'dbo.State'".

In other words, what appears to be happening here is that despite my having loaded s as an existing record, when I attempt to insert c, Linq is assuming that all related objects, State included, also need to be inserted!

This is completely absurd, and I cannot believe that Microsoft would have made such a huge blunder - so it must be that somewhere my own understanding is faulty.

Can anyone please explain what I am doing wrong, and what the correct approach here is?

Thanks!

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

2022-06-07 4 楼

If the State didn't use the same DataContext instance it might work to call the Attach method first.

热风软妹 2022-06-07 3 楼

Solved!

John Boker asked:

is the State.GetState(...) function
using the same datacontext as
MyDataContext.GetTable() ?

My answer was "Yes" - they are using the same DataContext class... but they were using different instances.

Lesson learned: always use the same instance of your DataContext class for any objects you're planning to persist to your DB!

(John gets credit for the answer, anyway...)

绝不放开 2022-06-07 2 楼

Something isn't right here. I'm assuming State -> Count is a one to many relationship. In which case, the correct way of doing this is:

State s = State.GetState("NY"); // here I do a load of a State class via the Linq DataContext
County c = new County();
c.Name = "Rockland";

s.Counties.Add(c);
db.SubmitChanges();

Since State is the parent table, you need to add the counties to the state's counties collection.

叶落知秋 2022-06-07 1 楼

Is the State.GetState(...) function using the same datacontext as MyDataContext.GetTable<County>()?