LINQ to SQL标识列外键依赖性 [英] LINQ to SQL Identity Column Foreign Key Dependency

问题描述

假设我的数据库中有2个表：一个（图像）包含：

ImageId - bigint（自动生成的标识列）
ImageData - varbinary（max）（实际图像数据）

其他（ImageDetails）包含以下内容（这些不是我的实际表格，因此忽略明显的设计缺陷）：
XPosition - float
YPosition - float
Scale - float
Image
bigint（外键到"Images"表）

（注意：我正在做所有事情）我在这里使用我已经定义的存储过程插入，并且从InsertImage存储过程返回auto-id。）

这是我的问题。如果我在Linq to Sql代码中使用InsertOnSubmit（）创建一个图像并将其添加到DataContext，我知道在调用DataContext上的SubmitChanges（）之后我可以访问自动生成的ID值。正如我的代码现在编写的那样，我必须调用SubmitChanges（）来插入Image，然后等待该命令执行，这样我就可以使用ImageId用于我想在插入Image后立即插入的ImageDetail行。
有没有办法将ImageDetails标记为依赖于即将插入的Image，这样我就可以将Image和ImageDetails都添加到DataContext并只调用SubmitChanges（）一次，但仍然有ImageDetails插入仍然使用正确的自动标识值？

我这样做的主要原因是我有一个单独的线程运行处理数据库相关的消息，它实际上执行所有的SubmitChanges（）调用，这样我就可以继续在我的主应用程序线程上压缩我的图像而先前压缩的图像一旦从队列中拉出，就会在后台插入数据库。如果我必须在我的压缩线程上等待数据库事务在每次插入之后返回，然后我才能继续前进，这部分地破坏了我的多线程设置的优势。

先谢谢你的帮助。

Let's say I have 2 tables in my database: One (Images) contains:

ImageId - bigint (auto generated Identity column)
ImageData - varbinary(max) (actual image data)

The other (ImageDetails) contains the following (these aren't my actual tables, so ignore the obvious design flaws):
XPosition - float
YPosition - float
Scale - float
ImageId - bigint (foreign key to "Images" table)

(Note: I am doing all my inserts here using stored procedures that I've already defined, and the auto-id is returned from the InsertImage stored procedure.)

Here's my question.  If I create an image and add it to a DataContext using InsertOnSubmit() in my Linq to Sql code, I know I can access the auto-generated ID value after calling SubmitChanges() on the DataContext.  As my code is written now, I have to call SubmitChanges() to insert the Image, then wait for that command to execute so that I have the ImageId available to use for the ImageDetail rows that I want to insert immediately after the Image is inserted.

Is there a way to flag the ImageDetails as having a dependency on the Image that is about to be inserted so that I can just add both Image and ImageDetails to the DataContext and call SubmitChanges() only once, but still have the ImageDetails insert still use the correct auto-identity value? 

My main reason for doing this is that I have a separate thread running that handles database-related messages which actually executes all the SubmitChanges() calls so that I can continue compressing my images on my main application thread while the previously compressed images get inserted into the database in the background once they are pulled from a queue.  If I have to wait on my compression thread for a database transaction to return after each insert before I can move on, it partially defeats the advantage of my multithreading setup.

Thanks in advance for your help.

推荐答案

我很惊讶这个尚未得到回答;但答案是"是"。

I'm surprised this one hasn't been answered already; but the answer is "Yes".

你的ImageDetails类应该有一个"[Association = ..."称为"图像"之类的属性。然后你会做这些步骤

Your ImageDetails class should have an "[Association=..." property called something like "Images". Then you would do these steps

1. 创建你的新图像对象...图片img = new Images（）;


2. 创建新的ImageDetails对象... ImageDetails det = new ImageDetails（）;


3. 将您的Images对象与ImageDetails对象相关联... det.Images = img;


4. InsertOnSubmit（img）


5. InsertOnSubmit（det）


6. SubmitChanges（）

1. create your new Images object ... Images img = new Images ();
2. create your new ImageDetails object ... ImageDetails det = new ImageDetails();
3. Associate your Images object to your ImageDetails object ... det.Images = img;
4. InsertOnSubmit(img)
5. InsertOnSubmit(det)
6. SubmitChanges()

在此之后，img.ImageId和det.ImageId都将具有自动标识值。

After this both img.ImageId and det.ImageId will have the auto-identity value.

这篇关于LINQ to SQL标识列外键依赖性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！