0

I'm having a problem with connecting two tables in FluentAPI. It's in fact a mix of FluentAPI and Data Annotations. I Looked at this question but It didn't help me. I tried with Index, composed unique keys.

Basically Foo is the main table. Bar table is optional. The are connected via two columns. key1 and key2 pairs are unique. Think of it as a parent-child relationship with a restriction that 1 parent can have only 1 child:

Data Entities looks like this:

[Table("foo")]
public class Foo
{
    [Key]
    [Column("pk", TypeName = "int")]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int FooId { get; set; }

    [Column("key1", TypeName = "int")]
    public int Key1 { get; set; }
    [Column("key2", TypeName = "int")]
    public int Key2 { get; set; }

    public Bar Bar { get; set; }
}

[Table("bar")]
public class Bar
{
    [Key]
    [Column("key1", TypeName = "int", Order = 1)]
    public int Key1 { get; set; }
    [Key]
    [Column("key2", TypeName = "int", Order = 2)]
    public int Key2 { get; set; }

    public Foo Foo { get; set; }
}

Here's how I was trying to connect them:

modelBuilder.Entity<Bar>().HasRequired(p => p.Foo).WithOptional(p => p.Bar);

What is wrong? Bar DOES require Foo. Foo DOES have optional Bar. <--- this should be totally enough, because Foo has columns named exactly like primary keys in Bar. But it doesn't work.

So I tried specifying foreign keys:

modelBuilder.Entity<Bar>().HasRequired(p => p.Foo).WithOptional(p => p.Bar).Map(p => p.MapKey(new[] { "key1", "key2" }));

It says:

"The number of columns specified must match the number of primary key columns"

Whaaaat? how? how come? Uhh..

I also tried:

modelBuilder.Entity<Bar>().HasIndex(table => new { table.Key1, table.Key2 });

So my questions are:

  1. Why my solution doesn't work? I do have complex key specified

  2. How can I slove it?

Ish Thomas
  • 2,270
  • 2
  • 27
  • 57
  • It's the opposite of what you are assuming. `Foo` is *required*, `Bar` is *optional*, hence `Foo` is the *principal* (parent) and `Bar` is the *dependent* (child), so `Bar` should have FK pointing to `Foo`s PK. – Ivan Stoev May 03 '18 at 19:34
  • In EF6, you can only refer to other entities by primary key, so both entities should share `FooId`, you simply can't use `Key1` and `Key2`. – Gert Arnold May 03 '18 at 21:38
  • @GertArnold wait wait wait.. so you're saying that I should add `FooId` column to the `Bar` table? Because it that case I'm not sure if I will be able to create a FK in the database? – Ish Thomas May 03 '18 at 23:42
  • Well, its name can be anything, but it should be a [shared primary key association](https://weblogs.asp.net/manavi/associations-in-ef-4-1-code-first-part-3-shared-primary-key-associations). – Gert Arnold May 04 '18 at 06:02

1 Answers1

1

This is going to be a little complicated, and I might be completely wrong here, but from my experience EntityFramework relations don't work that way. It seems to me that if Foo is required and Bar is optional, then each Bar should have a way to join back to Foo uniquely based upon Foo's pk value.

That is to say that Bar should be defined as:

[Table("bar")]
public class Bar
{
    [Key]
    [Column("key1", TypeName = "int", Order = 1)]
    public int Key1 { get; set; }
    [Key]
    [Column("key2", TypeName = "int", Order = 2)]
    public int Key2 { get; set; }
    public int FooId { get; set; }

    public Foo Foo { get; set; }
}

You would then need to use this FooId in your description of the relationship, not the composite key contained in Bar. EntityFramework has always required me to join on the entire primary key of the parent POCO, which must be a foreign key of the child POCO. You may still be able to join through the child's key in LINQ queries.

EntityFrameworkToday
  • 96
  • 4