Many-to-many mapping with LINQ
- by Alexander
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?
