CODEWITHSUNDEEP
c#asp.net-coreentity-framework-core
jewnbug97
jewnbug97

Reputation: 327

One-to-many relationship on custom columns

I have two models that I'd like to specify a one-to-many relationship between, but I can't seem to get it to work. Here are the models:

    public class Apple
    {
        public int Id { get; set; }
        public int ThingId { get; set; }

        public List<Banana> Bananas { get; set; }
    }
    public class Banana
    {
        public int Id { get; set; }
        public int ThingId { get; set; }
 
        [ForeignKey("ThingId")]
        public Apple Apple { get; set; }
    }

As you can see, both class Apple and class Banana have a column in them called ThingId and that's how I'd like to join the associations together. This is a legacy DB so I can't change the column names. However, when I run a query like this, I don't get an array of B objects back even though I know they exist.

var thing = _context.Apples.Include(a => a.Bananas).FirstOrDefault();
// thing - properly populated but the "Bananas" association is null

Any ideas on how I can get this to work properly using a custom column like this (preferably without fluent syntax since I prefer attributes, but not a big deal if that's necessary).

Thanks!

Edit: The link in the answer below by Rafi did the trick. Here's what I ending up using via the Fluent API.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Banana>()
        .HasOne(b => b.Apple)
        .WithMany(a => a.Bananas)
        .HasForeignKey(a => a.ThingId)
        .HasPrincipalKey(b => b.ThingId);
}

Upvotes: 0

Views: 959

Answers (2)

Mosia Thabo
Mosia Thabo

Reputation: 4267

Given your scenario, we're going to have to set some few Constraints in order to get the relationship to work. In this case, each apple has a unique key called Id, which would be a feasible property to use as foreign key to Apple related entities. But SQL does allow other columns to be used as foreign keys provided that they are unique for each row, which is Apple in your case.

So to achieve this let's look at your given code:

public class Apple
{
    public int Id { get; set; }
    public int ThingId { get; set; }
    public List<Banana> Bananas { get; set; }
}
public class Banana
{
    public int Id { get; set; }
    public int ThingId { get; set; }
    public Apple Apple { get; set; }
}

From your code, we'll have to make ThingId uniquer for every Apple. So let's configure that with Fluent API:

modelBuilder.Entity<Apple>()
    .HasIndex(ux=>ux.ThingId)
    .IsUnique()
    .Isclustered(false);

Then, with continue to configure the one-to-many relationship with fluend API:

modelBuilder.Entity<Apple>()
   .HasMany(apple => apple.Bananas)
   .WithOne(banana => banana.Apple)
   .HasForeignKey(banana => banana.ThingId)
   .Isrequired(false)
   .OnDelete(DeleteBehavior.Cascade); // You can set the DeleteBehavior here

An then your relationship will be set correctly.

Conclusion

Let's recap, A ForeignKey has to be a unique key from the foreign entity. I always recommend you to use FluentAPI for configuring your relationships, because you have control over many attributes of that relationship.

Upvotes: 1

Rafi Henig
Rafi Henig

Reputation: 6424

Try modifying your data models as in the following example:

public class Apple
{
    public int Id { get; set; }
    public List<Banana> Bananas { get; set; }
}

public class Banana
{
    public int Id { get; set; }

    [ForeignKey("Apple")]
    public int AppleId { get; set; }

    public Apple Apple { get; set; }
}

Update:

If ThingId is not a primary key consider using GroupJoin as below:

_context.Apples.GroupJoin(
  bana_context.Bananas,
  apple => apple.ThingId,
  banana => banana.ThingId,
  (apple, bananas) =>
  {
    apple.bananas = bananas.ToList();
    return apple;
  }
)

Upvotes: 2

Related Questions