开发者

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜