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?
