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

问题描述

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

问题在于，帖子可能与某个类别无关，或者某个类别可能与帖子无关.我没有找到制作EntitySet<CategoryPost?>之类的方法.

因此，当我添加第一篇文章时，没有一条SQL语句就一切顺利.同样，该帖子出现在输出中.当我尝试添加第二篇文章时，遇到了一个异常，关于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); }
    }
}

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; }
    }
}

类别

[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提供的功能)验证数据的方法.我通过该示例完成了所有工作，应该验证输入的函数调用搞砸了我的数据流，并在提交到数据库时抛出了该异常.

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

对不起，误报.

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.

这篇关于使用LINQ进行多对多映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！