This project is read-only.

Code First & Composite Key

May 9, 2012 at 7:26 AM

How to define composite key in code first approach?
public class EmployeeDepartmentHistory
    {
        [Disabled]
        [MemberOrder(1)]
        public virtual Employee Employee { get; set; }

        [MemberOrder(2)]
        public virtual Department Department { get; set; }
        [Mask("d")]
        [MemberOrder(3)]
        public virtual DateTime EffectiveDate { get; set; }

        [Mask("d")]
        [MemberOrder(4)]
        public virtual DateTime? EndDate { get; set; }
     
    }
Here EmployeeID, DepartmentID, and EffectiveDate will be composite key.


In EF, it can be define as follows


public class EmployeeDepartmentHistory
    {
        [Key, Column(Order = 0)]
        public virtual Employee EmployeeID { get; set; }

        [Key, Column(Order = 1)]
        public virtual Department DepartmentID { get; set; }
        [Key, Column(Order = 2)]
        public virtual DateTime EffectiveDate { get; set; }

        [Mask("d")]
        [MemberOrder(4)]
        public virtual DateTime? EndDate { get; set; }
        public virtual Department Department { get; set; }
        public virtual Department Employee { get; set; }
     
    }
OR
modelBuilder.Entity<Department>()
    .HasKey(t => new {t.EmployeeID,  t.DepartmentID, t.EffectiveDate});

May 9, 2012 at 9:25 AM

I'm not sure if this is currently possible.  It might be possible using the second approach (i.e.  .HasKey(t => new {t.EmployeeID,  t.DepartmentID, t.EffectiveDate})) -  look up 'How to specify configuration details with Code First' in the Naked Objects Developer Manual.  But I haven't tried it and can't guarantee it.

But I come back to my answer to your previous post (which you didn't respond to, so I don't know if you followed the advice or not).  If I was working Code First, I wouldn't be bothering to specify composite keys at all: I'd just give EmployeeDepartmentHistory its own, singular, key.

If you really want composite keys and/or you want to be able to specify the database mapping at this level of detail (e.g. for compatibility with other systems) then I strongly recommend that you work Model First, or even Database First.  N.B.  You can combine these techniques:  starting out code first, then generating the Model from the early database and then working ModelFirst and refining the mappings. Naked Objects works fine with composite keys this way. 

We do say in the developer manual that Code First is not really industrial strength.  Microsoft has moved Code First on a long way since we last worked on that part of the framework, and we will, in future, do some more work on it.  But for industrial strength applications we currently recommend Model First or Database First  -  especially where you want to control the database schema in detail.

May 9, 2012 at 10:05 AM

Thanks for your prompt reply.

BTW: Just I am playing with code first approach of Naked Objects .Net framework. Yes, you are right that " If I was working Code First, I wouldn't be bothering to specify composite keys at all: I'd just give EmployeeDepartmentHistory its own, singular, key." I have already implemented in this way and controlled the composite key behavior by code. For Example

public class EmployeeDepartmentHistory
    {
        #region Title
        public override string ToString()
        {
            var t = new TitleBuilder();
            t.Append(Department).Append(":", Designation);
            return t.ToString();
        }
        #endregion

        [Hidden]
        public virtual int ID { get; set; }
     
        [Disabled]
        [Required]
        [MemberOrder(1)]
        public virtual Employee Employee { get; set; }

        [Required]
        [MemberOrder(2)]
        public virtual Department Department { get; set; }

        [Required]
        [MemberOrder(3)]
        public virtual Designation Designation { get; set; }

        [Required]
        [Mask("d")]
        [MemberOrder(4)]
        public virtual DateTime EffectiveDate { get; set; }

        [Mask("d")]
        [MemberOrder(5)]
        public virtual DateTime? EndDate { get; set; }
     
    }

And adding following behavior in Employee Class:

[MemberOrder(Sequence = "11", Name = "Employment")]
        public Employee AddOrChangeDepartment([Required] Department department, [Required] Designation designation, [Required, Mask("d")] DateTime effectivedate)
        {
            EmployeeDepartmentHistory current = CurrentDepartment();

            var edh = Container.NewTransientInstance<EmployeeDepartmentHistory>();
            edh.Employee = this;
            edh.Department = department;
            edh.Designation = designation;
            edh.EffectiveDate = effectivedate;

            if (current != null)
            {
                if (current.Department != department || current.Designation != designation)
                {
                    current.EndDate = effectivedate.AddDays(-1);

                    Container.Persist(ref edh);
                }
            }
            else
            {
                Container.Persist(ref edh);
            }

            return this;
        }

public Employee ChangeDesignation([Required] Designation designation, [Required, Mask("d")] DateTime effectivedate)
        {
            EmployeeDepartmentHistory current = CurrentDepartment();

            if (current != null)
            {
                if (current.Designation != designation)
                {
                    var edh = Container.NewTransientInstance<EmployeeDepartmentHistory>();
                    edh.Employee = this;
                    edh.Department = current.Department;
                    edh.Designation = designation;
                    edh.EffectiveDate = effectivedate;

                    current.EndDate = effectivedate.AddDays(-1);

                    Container.Persist(ref edh);
                }
            }

            return this;
        }
        private EmployeeDepartmentHistory CurrentDepartment()
        {
            EmployeeDepartmentHistory current = DepartmentHistory.Where(n => n.EndDate == null).FirstOrDefault();
            return current;
        }