如何从LINQ to SQL中删除子集合中的记录？

I have two tables in my database connected by foreign keys: Page (PageId, other data) and PageTag (PageId, Tag). I've used LINQ to generate classes for these tables, with the page as the parent and the Tag as the child collection (one to many relationship). Is there any way to mark PageTag records for deletion from the database from within the Page class?

我的数据库中有两个表由外键连接:Page(PageId,其他数据)和PageTag(PageId,Tag)。我已经使用LINQ为这些表生成类,页面作为父级,Tag作为子集合(一对多关系)。有没有办法在Page类中从数据库中标记要删除的PageTag记录?

Quick Clearification:

I want the child objects to be deleted when the parent DataContext calls SubmitChanges(), not before. I want TagString to behave exactly like any of the other properties of the Page object.

我希望在父DataContext调用SubmitChanges()之前删除子对象,而不是之前。我希望TagString的行为与Page对象的任何其他属性完全相同。

I would like to enable code like the following:

我想启用如下代码:

Page page = mDataContext.Pages.Where(page => page.pageId = 1);
page.TagString = "new set of tags";

//Changes have not been written to the database at this point.

mDataContext.SubmitChanges();

//All changes should now be saved to the database.

Here is my situation in detail:
In order to make working with the collection of tags easier, I've added a property to the Page object that treats the Tag collection as a string:

以下是我的详细情况:为了更容易地处理标记集合,我在Page对象中添加了一个属性,它将Tag集合视为字符串:

public string TagString {
    get {
        StringBuilder output = new StringBuilder();
        foreach (PageTag tag in PageTags) {
            output.Append(tag.Tag + " ");
        }

        if (output.Length > 0) {
            output.Remove(output.Length - 1, 1);
        }

        return output.ToString();
    }
    set {
        string[] tags = value.Split(' ');
        PageTags.Clear();
        foreach (string tag in tags) {
            PageTag pageTag = new PageTag();
            pageTag.Tag = tag;
            PageTags.Add(pageTag);
        }
    }
}

Basically, the idea is that when a string of tags is sent to this property, the current tags of the object are deleted and a new set is generated in their place.

基本上,这个想法是当一串标签被发送到这个属性时,该对象的当前标签将被删除,并在它们的位置生成一个新的集合。

The problem I'm encountering is that this line:

我遇到的问题是这一行:

PageTags.Clear();

Doesn't actually delete the old tags from the database when changes are submitted.

实际上,在提交更改时,不会从数据库中删除旧标记。

Looking around, the "proper" way to delete things seems to be to call the DeleteOnSubmit method of the data context class. But I don't appear to have access to the DataContext class from within the Page class.

环顾四周,删除事物的“正确”方法似乎是调用数据上下文类的DeleteOnSubmit方法。但我似乎无法从Page类中访问DataContext类。

Does anyone know of a way to mark the child elements for deletion from the database from within the Page class?

有没有人知道如何在Page类中标记从数据库中删除的子元素?

7 个解决方案

#1

After some more research, I believe I've managed to find a solution. Marking an object for deletion when it's removed from a collection is controlled by the DeleteOnNull parameter of the Association attribute.

经过一些研究,我相信我已经设法找到了解决方案。将对象从集合中删除时标记要删除的对象由Association属性的DeleteOnNull参数控制。

This parameter is set to true when the relationship between two tables is marked with OnDelete Cascade.

当使用OnDelete Cascade标记两个表之间的关系时,此参数设置为true。

Unfortunately, there is no way to set this attribute from within the designer, and no way to set it from within the partial class in the *DataContext.cs file. The only way to set it without enabling cascading deletes is to manually edit the *DataContext.designer.cs file.

遗憾的是,无法在设计器中设置此属性,也无法在* DataContext.cs文件的部分类中设置它。在不启用级联删除的情况下设置它的唯一方法是手动编辑* DataContext.designer.cs文件。

In my case, this meant finding the Page association, and adding the DeleteOnNull property:

在我的例子中,这意味着找到页面关联,并添加DeleteOnNull属性:

[Association(Name="Page_PageTag", Storage="_Page", ThisKey="PageId", OtherKey="iPageId", IsForeignKey=true)]
public Page Page
{
    ...
}

And adding the DeleteOnNull attribute:

并添加DeleteOnNull属性:

[Association(Name="Page_PageTag", Storage="_Page", ThisKey="PageId", OtherKey="iPageId", IsForeignKey=true, DeleteOnNull = true)]
public Page Page
{
    ...
}

Note that the attribute needed to be added to the Page property of the PageTag class, not the other way around.

请注意,该属性需要添加到PageTag类的Page属性,而不是相反。

See also:
Beth Massi -- LINQ to SQL and One-To-Many Relationships
Dave Brace -- LINQ to SQL: DeleteOnNull

另请参阅:Beth Massi - LINQ to SQL和一对多关系Dave Brace - LINQ to SQL:DeleteOnNull

#2

Sorry, my bad. That won't work.

对不起这是我的错。那不行。

It really looks like you need to be doing this in your repository, rather than in your Page class. There, you have access to your original data context.

看起来您需要在存储库中执行此操作,而不是在Page类中执行此操作。在那里,您可以访问原始数据上下文。

There is a way to "attach" the original data context, but by the time you do that, it has become quite the code smell.

有一种方法可以“附加”原始数据上下文,但是当你这样做时,它已成为代码的味道。

#3

Do you have a relationship, in your Linq to SQL entity diagram, linking the Page and PageTags tables? If you don't, that is why you can't see the PageTags class from the Page class.

在Linq to SQL实体图中,您是否有关系链接Page和PageTags表?如果不这样做,那就是您无法从Page类中看到PageTags类的原因。

If the foreign key in the PageTags database table is set to Allow Nulls, Linq to SQL will not create the link when you drag the tables into the designer, even if you created a relationship on the SQL Server.

如果PageTags数据库表中的外键设置为Allow Nulls,则在将表拖动到设计器中时,Linq to SQL将不会创建链接,即使您在SQL Server上创建了关系也是如此。

#4

This is one of those areas where OR mapping can get kind of hairy. Providing this TagString property makes things a bit more convenient, but in the long run it obfuscates what is really happening when someone utilizes the TagString property. By hiding the fact that your performing data modification, someone can very easily come along and set the TagString without using your Page entity within the scope of a DataContext, which could lead to some difficult to find bugs.

这是OR映射可以变得多毛的区域之一。提供这个TagString属性可以使事情更方便,但从长远来看,它会混淆当有人使用TagString属性时真正发生的事情。通过隐藏执行数据修改的事实,有人可以很容易地在DataContext的范围内不使用您的Page实体而设置TagString,这可能导致一些难以发现的错误。

A better solution would be to add a Tags property on the Page class with the L2S model designer, and require that the PageTags be edited directly on the Tags property, within the scope of a DataContext. Make the TagString property read only, so it can be genreated (and still provide some convenience), but eliminate the confusion and difficulty around setting that property. This kind of change clarifies intent, and makes it obvious what is happening and what is required by consumers of the Page object to make it happen.

更好的解决方案是使用L2S模型设计器在Page类上添加Tags属性,并要求在DataContext范围内直接在Tags属性上编辑PageTags。使TagString属性只读,因此它可以进行生成(并且仍然提供一些便利),但消除了设置该属性的困惑和困难。这种变化澄清了意图,并明确了正在发生的事情以及Page对象的消费者为实现这一目标所需要的内容。

Since Tags is a property of your Page object, as long as it is attached to a DataContext, any changes to that collection will properly trigger deletions or insertions in the database in response to Remove or Add calls.

由于Tags是Page对象的属性,因此只要它附加到DataContext,对该集合的任何更改都将正确触发数据库中的删除或插入,以响应Remove或Add调用。

#5

Aaron,

Apparently you have to loop thru your PageTag records, calling DeleteOnSubmit for each one. Linq to SQL should create an aggregate query to delete all of the records at once when you call SubmitChanges, so overhead should be minimal.

显然你必须通过你的PageTag记录循环,为每个记录调用DeleteOnSubmit。 Linq to SQL应该创建一个聚合查询,以便在调用SubmitChanges时立即删除所有记录,因此开销应该是最小的。

replace

PageTags.Clear();

with

foreach (PageTag tag in PageTags)
    myDataContext.DeleteOnSubmit(tag);

#6

Aaron:

Add a DataContext member to your PageTag partial class.

将DataContext成员添加到PageTag分部类。

partial class PageTag 
{ 
    DataClassesDataContext myDataContext = new DataClassesDataContext(); 

    public string TagString { 

..etc.

#7

Larger code sample posted at Robert Harvey's request:

罗伯特哈维的请求发布了更大的代码示例:

DataContext.cs file:

namespace MyProject.Library.Model
{
    using Tome.Library.Parsing;
    using System.Text;

    partial class Page
    {
        //Part of Robert Harvey's proposed solution.
        MyDataContext mDataContext = new TomeDataContext();

        public string TagString {
            get {
                StringBuilder output = new StringBuilder();
                foreach (PageTag tag in PageTags) {
                    output.Append(tag.Tag + " ");
                }

                if (output.Length > 0) {
                    output.Remove(output.Length - 1, 1);
                }

                return output.ToString();
            }
            set {
                string[] tags = value.Split(' ');
                //Original code, fails to mark for deletion.
                //PageTags.Clear();

                //Robert Harvey's suggestion, thorws exception "Cannot remove an entity that has not been attached."
                foreach (PageTag tag in PageTags) {
                    mDataContext.PageTags.DeleteOnSubmit(tag);
                }

                foreach (string tag in tags) {
                    PageTag PageTag = new PageTag();
                    PageTag.Tag = tag;
                    PageTags.Add(PageTag);
                }
            }
        }

        private bool mIsNew;
        public bool IsNew {
            get {
                return mIsNew;
            }
        }

        partial void OnCreated() {
            mIsNew = true;
        }

        partial void OnLoaded() {
            mIsNew = false;
        }
    }
}

Repository Methods:

public void Save() {
    mDataContext.SubmitChanges();
}

public Page GetPage(string pageName) {
    Page page =
        (from p in mDataContext.Pages
        where p.FileName == pageName
        select p).SingleOrDefault();

    return page;
}

Usage:

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Edit(string pageName, FormCollection formValues) {
    Page updatedPage = mRepository.GetPage(pageName);

    //TagString is a Form value, and is set via UpdateModel.
    UpdateModel(updatedPage, formValues.ToValueProvider());
    updatedPage.FileName = pageName;

    //At this point NO changes should have been written to the database.

    mRepository.Save();

    //All changes should NOW be saved to the database.

    return RedirectToAction("Index", "Pages", new { PageName = pageName });
}