How to configure a composite primary key containing a foreign key (EF Fluent API)

Question

I have a one-to-many relationship between entities Curve and Point, defined as follows:

public class Curve
{
    public Curve()
    {
        Points = new HashSet<Point>();
    }

    public int CurveId { get; set; }

    public string Name { get; set; }

    public virtual ICollection<Point> Points { get; set; }
}

And:

public class Point
{
    public int Point_Num { get; set; }

    public int CurveId { get; set; }

    public double XCoord { get; set; }

    public double YCoord { get; set; }

    public virtual Curve Curve { get; set; }
}

In my context class, I configure the keys and navigation properties as follows (note that the Point entity has a composite primary key):

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{

    modelBuilder.Entity<Curve>()
        .HasKey(c => c.CurveId);

    modelBuilder.Entity<Point>()
        .HasKey(p => new { p.Point_Num, p.CurveId });

    modelBuilder.Entity<Point>()
        .HasRequired(p => p.Curve)
        .WithMany(c => c.Points)
        .HasForeignKey(p => p.CurveId);

}

Somewhere else in the code, I populate the database:

var curve = new Curve
{
    Name = "curve 1"
};

var points = new List<Point>();
points.Add(new Point
{
    XCoord = 1d,
    YCoord = 1d,
});

points.Add(new Point
{
    XCoord = 2d,
    YCoord = 2d,
});

foreach (var point in points)
{
    curve.Points.Add(point);
}

using (var dbCtxt = new MyDbContext())
{
    try
    {
        dbCtxt.Curves.Add(curve);
        dbCtxt.SaveChanges();
    }
    catch (Exception e)
    {
        Console.WriteLine(e.StackTrace);
    }
 }

An error is thrown when SaveChanges() is called "An error occurred while updating the entries."

If I define Point_Num as the primary key of Point entity, all the entities update just fine. It seems like the problem comes from the fact that the composite primary key (Point_Num, CurveId) contains a foreign key (CurveId)

I cannot get my head around this, there is clearly something I am missing. Any help will be much appreciated!

score 1 · Accepted Answer · answered Apr 05 '17 at 12:17

So I have done what I should have done in the first place, which is creating my tables in SQL and using the Entity Data Model Wizard to generate my entities in C# (using "Code first from database")

Doing so, I noticed that EF generated the Point entity with the following "DatabaseGeneratedOption.None" data annotations on the composite primary key:

public partial class Points
{
    [Key]
    [Column(Order = 0)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Point_Num { get; set; }

    [Key]
    [Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Curve_Id { get; set; }

    ...

    public virtual Curve Curve { get; set; }
}

I impelemented this using Fluent API:

modelBuilder.Entity<Point>().Property(p => p.PointId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

In the bit of code that populates the database, I create say, 2 different "Curve" entities:

        var curve1 = new Curve
        {
            Name = "curve 1"
        };

        var curve2 = new Curve
        {
            Name = "curve 2"
        };

I then create "Point" entities specifying PointId and CurveId. E.g. let's say I want to add one point to each curve:

var points = new List<Point>();

points.Add(new Point
{
    PointId = 1,
    CurveId = 1,
    XCoord = 1.2d,
    YCoord = 3.1d,
});

points.Add(new Point
{
    PointId = 1,
    CurveId = 2,
    XCoord = 0.5d,
    YCoord = 0.75d,
});

When adding entities to the context, it is not sufficient to add the curve1 and curve2, I also need to add the list of points previously created, EF does not upload them automatically:

myContext.Points.AddRange(points);
myContext.Curves.Add(curve1);
myContext.Curves.Add(curve2);
myContext.SaveChanges();

It is not an elegant solution at all. I was hoping there would be a more straightforward way of doing this, but that solved my problem! The database is updated correctly, all the keys and foreign keys are set up properly...

How to configure a composite primary key containing a foreign key (EF Fluent API)

1 Answers1