使用LINQ的多对多映射[英] Many-to-many mapping with LINQ

本文是小编为大家收集整理的关于使用LINQ的多对多映射的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我想在 C# 中以多对多关系执行 LINQ to SQL 映射,但数据不是强制性的.
说清楚:
我有一个新闻站点/博客,并且有一个名为 Posts 的表.一个博客可以同时与多个类别相关,因此有一个名为 CategoriesPosts 的表,它与 Posts 表和 Categories 表的外键链接.如果在这种情况下很重要,我已经为每个表设置了一个身份主键,每个表中都有一个 id 字段.
在 C# 中,我为每个表定义了一个类,尽可能明确地定义了每个字段.Post 类和 Category 类有一个 EntitySet 链接到 CategoryPost 对象,而 CategoryPost 类有 2 个 EntityRef 成员链接到彼此类型的 2 个对象.

问题在于帖子可能与任何类别相关或不相关,以及类别中可能包含或不包含帖子.我没有找到制作 EntitySet<CategoryPost?> 之类的方法.

所以当我添加第一篇文章时,没有一条 SQL 语句就一切顺利.此外,这篇文章出现在输出中.当我尝试添加第二个帖子时,我遇到了一个异常,Object reference not set to an instance of an object,关于 CategoryPost 成员.

帖子:

[Table(Name="tm_posts")]
public class Post : IDataErrorInfo
{
    public Post()
    {
        //Initialization of NOT NULL fields with their default values
    }

    [Column(Name = "id", DbType = "int", CanBeNull = false, IsDbGenerated = true, IsPrimaryKey = true)]
    public int ID { get; set; }

    private EntitySet<CategoryPost> _categoryRef = new EntitySet<CategoryPost>();
    [Association(Name = "tm_rel_categories_posts_fk2", IsForeignKey = true, Storage = "_categoryRef", ThisKey = "ID", OtherKey = "PostID")]
    public EntitySet<CategoryPost> CategoryRef
    {
        get { return _categoryRef; }
        set { _categoryRef.Assign(value); }
    }
}

分类帖子

[Table(Name = "tm_rel_categories_posts")]
public class CategoryPost
{
    [Column(Name = "id", DbType = "int", CanBeNull = false, IsDbGenerated = true, IsPrimaryKey = true)]
    public int ID { get; set; }

    [Column(Name = "fk_post", DbType = "int", CanBeNull = false)]
    public int PostID { get; set; }

    [Column(Name = "fk_category", DbType = "int", CanBeNull = false)]
    public int CategoryID { get; set; }

    private EntityRef<Post> _post = new EntityRef<Post>();
    [Association(Name = "tm_rel_categories_posts_fk2", IsForeignKey = true, Storage = "_post", ThisKey = "PostID", OtherKey = "ID")]
    public Post Post
    {
        get { return _post.Entity; }
        set { _post.Entity = value; }
    }

    private EntityRef<Category> _category = new EntityRef<Category>();
    [Association(Name = "tm_rel_categories_posts_fk", IsForeignKey = true, Storage = "_category", ThisKey = "CategoryID", OtherKey = "ID")]
    public Category Category
    {
        get { return _category.Entity; }
        set { _category.Entity = value; }
    }
}

类别

[Table(Name="tm_categories")]
public class Category
{
    [Column(Name = "id", DbType = "int", CanBeNull = false, IsDbGenerated = true, IsPrimaryKey = true)]
    public int ID { get; set; }

    [Column(Name = "fk_parent", DbType = "int", CanBeNull = true)]
    public int ParentID { get; set; }

    private EntityRef<Category> _parent = new EntityRef<Category>();
    [Association(Name = "tm_posts_fk2", IsForeignKey = true, Storage = "_parent", ThisKey = "ParentID", OtherKey = "ID")]
    public Category Parent
    {
        get { return _parent.Entity; }
        set { _parent.Entity = value; }
    }

    [Column(Name = "name", DbType = "varchar(100)", CanBeNull = false)]
    public string Name { get; set; }
}

那我做错了什么?如何使插入不属于任何类别的帖子成为可能?如何插入没有帖子的类别?

推荐答案

好像这个错误和映射无关.映射是正确的.
正如我所写,第一篇文章被插入没有问题,其余的都没有插入.从数据库中删除后,我仍然无法添加帖子.很明显,这与我在数据库中是否有某些东西无关,仅与我对代码进行了一些更改这一事实有关.

那么有哪些变化呢?在 Apress"ASP.NET MVC Pro"中,第一个示例说明了一种以迭代方式(非声明性,使用 IDataErrorInfo 提供的工具)验证数据的方法,我坚持使用该方法.我通过该示例完成了所有操作,并且应该验证输入的函数调用搞砸了我的数据流,并在提交到数据库时抛出了该异常.

删除了该验证,一切正常.

抱歉,误报了.

本文地址:https://www.itbaoku.cn/post/1556920.html

问题描述

I would like to perform LINQ to SQL mapping in C#, in a many-to-many relationship, but where data is not mandatory.
To be clear:
I have a news site/blog, and there's a table called Posts. A blog can relate to many categories at once, so there is a table called CategoriesPosts that links with foreign keys with the Posts table and with Categories table. I've made each table with an identity primary key, an id field in each one, if it matters in this case.
In C# I defined a class for each table, defined each field as explicitly as possible. The Post class, as well as Category class, have a EntitySet to link to CategoryPost objects, and CategoryPost class has 2 EntityRef members to link to 2 objects of each other type.

The problem is that a Post may relate or not to any category, as well as a category may have posts in it or not. I didn't find a way to make an EntitySet<CategoryPost?> or something like that.

So when I added the first post, all went well with not a single SQL statement. Also, this post was present in the output. When I tried to add the second post I got an exception, Object reference not set to an instance of an object, regarding to the CategoryPost member.

Post:

[Table(Name="tm_posts")]
public class Post : IDataErrorInfo
{
    public Post()
    {
        //Initialization of NOT NULL fields with their default values
    }

    [Column(Name = "id", DbType = "int", CanBeNull = false, IsDbGenerated = true, IsPrimaryKey = true)]
    public int ID { get; set; }

    private EntitySet<CategoryPost> _categoryRef = new EntitySet<CategoryPost>();
    [Association(Name = "tm_rel_categories_posts_fk2", IsForeignKey = true, Storage = "_categoryRef", ThisKey = "ID", OtherKey = "PostID")]
    public EntitySet<CategoryPost> CategoryRef
    {
        get { return _categoryRef; }
        set { _categoryRef.Assign(value); }
    }
}

CategoryPost

[Table(Name = "tm_rel_categories_posts")]
public class CategoryPost
{
    [Column(Name = "id", DbType = "int", CanBeNull = false, IsDbGenerated = true, IsPrimaryKey = true)]
    public int ID { get; set; }

    [Column(Name = "fk_post", DbType = "int", CanBeNull = false)]
    public int PostID { get; set; }

    [Column(Name = "fk_category", DbType = "int", CanBeNull = false)]
    public int CategoryID { get; set; }

    private EntityRef<Post> _post = new EntityRef<Post>();
    [Association(Name = "tm_rel_categories_posts_fk2", IsForeignKey = true, Storage = "_post", ThisKey = "PostID", OtherKey = "ID")]
    public Post Post
    {
        get { return _post.Entity; }
        set { _post.Entity = value; }
    }

    private EntityRef<Category> _category = new EntityRef<Category>();
    [Association(Name = "tm_rel_categories_posts_fk", IsForeignKey = true, Storage = "_category", ThisKey = "CategoryID", OtherKey = "ID")]
    public Category Category
    {
        get { return _category.Entity; }
        set { _category.Entity = value; }
    }
}

Category

[Table(Name="tm_categories")]
public class Category
{
    [Column(Name = "id", DbType = "int", CanBeNull = false, IsDbGenerated = true, IsPrimaryKey = true)]
    public int ID { get; set; }

    [Column(Name = "fk_parent", DbType = "int", CanBeNull = true)]
    public int ParentID { get; set; }

    private EntityRef<Category> _parent = new EntityRef<Category>();
    [Association(Name = "tm_posts_fk2", IsForeignKey = true, Storage = "_parent", ThisKey = "ParentID", OtherKey = "ID")]
    public Category Parent
    {
        get { return _parent.Entity; }
        set { _parent.Entity = value; }
    }

    [Column(Name = "name", DbType = "varchar(100)", CanBeNull = false)]
    public string Name { get; set; }
}

So what am I doing wrong? How to make it possible to insert a post that doesn't belong to any category? How to insert categories with no posts?

推荐答案

It seems that the error has nothing to do with mapping. Mapping is correct.
As I wrote, the first post got inserted without problems, and the rest failed to insert. After deleting it from the database, I still couldn't add posts. It became clear that it had nothing to do with either I had something in the DB or not, and only with the fact that I've made some changes to the code.

So what are the changes? In Apress "ASP.NET MVC Pro", the first example illustrated a way to validate data in an iterative way (non-declarative, using the facilities provided by IDataErrorInfo), to which I stuck. I done everything by that example, and the function call that should have validated the input screwed up my data flow, and threw that exception upon submitting to the database.

Removed that validation, and everything worked fine.

Sorry for the false alarms.