LINQ to SQL Insert (one to many relationship using custom entity classes and [Association] attribute)
I am currently delving into LINQ to SQL as it looks cool, and potentially much more useful than ADO.NET in many circumstances.
I have used LINQ to SQL before, but I created all the domain classes using the LINQ to SQL Class designer, and I don't really want to use it as I would rather know what is going on 'behind the scenes' rather than putting it down .NET magic. Hence, I have been attempting to create my domain (or entity) classes manually using LINQ attributes.
I was finding it all ok untill I ran into problems whilst trying to model a one to many relationship in my DB. There is a table Meets that has a column LeaderId, which is a foreign key to the table Members
This is how I have the entity classes set up:
Meet.cs
[Table(Name = "dbo.Meets")]
public class Meet
{
private EntityRef<Member> _leader;
[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public int MeetId { get; set; }
[Column]
public string Location { get; set; }
[Column]
public DateTime Date { get; set; }
[Association(Name = "FK_Meets_Members", Storage = "_leader")]
public Member Leader
{
get { return _leader.Entity; }
set { _leader.Entity = value; }
}
}
Member.cs
[Table(Name = "dbo.Members")]
public class Member
{
private EntitySet<Meet> _meets;
[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public int MemberId { get; set; }
[Column]
public string Name { get; set; }
[Column]
public string Hometown { get; set; }
[Column]
public string Info { get; set; }
[Column]
public ComitteeRole ComitteeRole { get; set; }
[Association(Name = "FK_Meets_Members", S开发者_StackOverflow中文版torage = "_meets")]
public ICollection<Meet> Meets
{
get { return _meets; }
set { _meets.Assign(value); }
}
}
Selects work on the Meets table and likewise on the Members table and associated entities are loaded fine. However when I try and do an insert into the Meets table I am getting the good old 'Object reference not set to an instance of an object' error.
Now the problem must lie with what I am using to store the the LeaderId when I am adding a new Meet entity. I am setting the foreign key in my object like so:
Meet meet = new Meet();
meet.Leader.MemberId = 1;
However, when I am doing the LINQ insert it doesn't seem to understand that the MemberId of the Leader property is the value to put in the foreign key column in the Meets table.
I realise this could probably be solved by adding a separate property to the Meet class like so:
[Column]
public int LeaderId { get; set; }
However, I don't want to clutter my class with an extra property that is a duplicate of the MemberId property of the Leader property.
Hope you understand me... Can anyone help?
I figured this problem out, I did have to add the extra LeaderId property to represent the foreign key, then mark this as the foreign key in the Associate attribute named parameter 'ThisKey' like so:
Meet.cs
[Table(Name = "dbo.Meets")]
public class Meet
{
private EntityRef<Member> _leader;
public Meet()
{
_leader = default(EntityRef<Member>);
}
[Column(IsPrimaryKey = true, IsDbGenerated = true)]
public int MeetId { get; set; }
[Column]
public string Location { get; set; }
[Column]
public DateTime Date { get; set; }
[Column]
public int LeaderId { get; set; }
[Association(Name = "Member_Meet", Storage = "_leader", ThisKey = "LeaderId", OtherKey = "MemberId", IsForeignKey = true)]
public Member Leader
{
get { return _leader.Entity; }
set { _leader.Entity = value; }
}
}
精彩评论