Composite Key Problems

Question

I am making an MVC application that takes daily user input and puts it in a table. It is sorted into days, weeks and months and I am trying out composite keys but am getting an error.

Here are my models:

Year model:

public class Year
{
    [ForeignKey("YearID")]
    public int YearID { get; set; }

    public virtual ICollection<Week> Weeks { get; set; }
}

Week model:

public class Week
{
    [Key, Column(Order = 1)]
    public int WeekID { get; set; }

    [Key, Column(Order = 2)]
    public int YearID { get; set; }

    public virtual Year year { get; set; }
    public virtual ICollection<Day> Days { get; set; }
}

And finally, Day model:

public class Day
{
    [Key, Column(Order = 1)]
    public int DayID { get; set; }

    [Key, Column(Order = 2)]
    public int WeekID { get; set; }

    [Key, Column(Order = 3)]
    public int YearID { get; set; }

    public float Reading1 { get; set; }
    public float Reading2 { get; set; }
    public virtual Week Weeks { get; set; }
}

UtilityInitializer class:

namespace Utility3.DAL
{
    public class UtilityInitializer : System.Data.Entity.DropCreateDatabaseAlways<UtilityContext>
        {
        protected override void Seed(UtilityContext context)
        {
            var years = new List<Year>
            {
                new Year{YearID = 2014},
                new Year{YearID = 2015}
            };
            years.ForEach(s => context.Years.Add(s));
            context.SaveChanges();

            var weeks = new List<Week>
            {
                new Week{WeekID = 1, YearID = 2014},
                new Week{WeekID = 2, YearID = 2014},
                new Week{WeekID = 1, YearID = 2015},
                new Week{WeekID = 2, YearID = 2015}
            };
            weeks.ForEach(s => context.Weeks.Add(s));
            context.SaveChanges();


            var days = new List<Day>
            {
                new Day{DayID = 1, WeekID = 1, YearID = 2014},
                new Day{DayID = 2, WeekID = 1, YearID = 2014},
                new Day{DayID = 1, WeekID = 2, YearID = 2014},
                new Day{DayID = 2, WeekID = 2, YearID = 2014},
                new Day{DayID = 1, WeekID = 1, YearID = 2015},
                new Day{DayID = 2, WeekID = 1, YearID = 2015},
                new Day{DayID = 1, WeekID = 2, YearID = 2015},
                new Day{DayID = 2, WeekID = 2, YearID = 2015}

            };

            days.ForEach(s => context.Days.Add(s));
            context.SaveChanges();


        }
    }
}

When I try to make the controllers I get this error:

Unable to retrieve metadata from "Utility3.Models.Year". The property "YearID" cannot be configured as a navigation property. The property must be a valid entity type and the property should have a non-abstract getter and setter. For collection properties the type must implement ICollection<T> where T is a valid entity type.

I have YearID as a foreign key in an attempt to fix the problem. I got a different error without the foreign key.

Why is the YearID throwing that error?

EDIT I tried changing the name in the Week model of YearID to YearNo and changed this segment of code in my initializer:

var weeks = new List<Week>
        {
            new Week{YearNo = years.Single(i => i.YearID == 2014).YearID, WeekID = 1},
            new Week{YearNo = years.Single(i => i.YearID == 2014).YearID, WeekID = 2},
            new Week{YearNo = years.Single(i => i.YearID == 2015).YearID, WeekID = 1},
            new Week{YearNo = years.Single(i => i.YearID == 2015).YearID, WeekID = 2}
        };
        weeks.ForEach(s => context.Weeks.Add(s));
        context.SaveChanges();

But now it is telling me that the sequence contains no matching elements but there is?

Answer 1

I managed to get it working by adding [DatabaseGenerated(DatabaseGeneratedOption.None)] to the models above the keys to allow me to edit them. It's working for now any way .

Answer 2

You should not use a foreign key as part of a composite key on a table. Change your tables to work like this, and it will do what you want.

public class Year
{
    [Key]
    public int YearID { get; set; }

    public virtual ICollection<Week> Weeks { get; set; }
}


public class Week
{
    [Key]
    public int WeekID { get; set; }

    public virtual Year year { get; set; }
    public virtual ICollection<Day> Days { get; set; }
}

public class Day
{
    [Key]
    public int DayID { get; set; }

    public float Reading1 { get; set; }
    public float Reading2 { get; set; }
    public virtual Week Weeks { get; set; }
}