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!

harveyAJ · Accepted Answer · 2017-04-05 12:17:31Z

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...

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related