Many-to-many mapping with LINQ
Posted
by Alexander
on Stack Overflow
See other posts from Stack Overflow
or by Alexander
Published on 2010-06-11T21:10:05Z
Indexed on
2010/06/11
21:12 UTC
Read the original article
Hit count: 257
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?
© Stack Overflow or respective owner