EF6: Table Splitting Not Working

Question

I am trying to create an EF6 database where two tables, Addresses and Visits, share the same values as primary keys. Visits, conceptually, is an extension of Addresses. I'm splitting the tables because most of the records in Addresses don't require the fields contained in Visits.

I'm using the code first approach. Here's the relevant code for the Addresses:

    public class Address 
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    [ForeignKey( "ID" )]
    public virtual Visit Visit { get; set; }

and for Visits:

   public class Visit
   {
    [Key]
    [DatabaseGenerated( DatabaseGeneratedOption.Identity )]
    public int ID { get; set; }

    [ForeignKey("ID")]
    public virtual Address Address { get; set; }

Based on my research, I also needed to include the following in my datacontext's OnModelCreating method:

    modelBuilder.Entity<Visit>()
        .HasOptional( v => v.Address )
        .WithRequired();

Unfortunately, this doesn't work. I can update the database alright, after eliminating scaffolding calls to drop the primary index from Addresses (probably because the add-migration code thinks the primary key is "merely" a foreign key field). But when I run the application I get the following error:

Invalid column name 'Address_ID'. Invalid column name 'Address_ID'.

From my limited experience with EF6 this looks like someplace deep inside the framework it's expecting there to be fields named 'Address_ID', probably in the Visits table (based on the 'table name'_'field name' naming structure I've seen for other implicitly added fields).

Is what I'm trying to do possible? If so, what am I missing in the configuration?

Additional Info

In trying out bubi's proposed solution, which unfortunately still generates the same error, that I could eliminate the OnModelCreating code and still get functional migration code generated.

Resolution

I finally did what I should've done earlier, which is examine the actual T-SQL code generated by the query which was blowing up. It turns out the problem was not in the Visit/Address linkage, but in a completely separate relationship involving another table. Apparently, somewhere along the way I did something to cause EF to think that other table (Voters) had an Address_ID foreign key field. In reality, the Address/Voter relationship should've been, and originally was, tied to a Voter.AddressID field.

Rather than try to unwind a large number of migrations I opted to blow away the database, blow away the migrations and start from scratch. After recreating the database -- but using bubi's suggestion -- I reloaded the data from backup and, voila, I was back in business.

For the sake of completeness, here's the code I ended up having to put into the OnModelCreating method call to get the Address/Visit relationship to work correctly:

modelBuilder.Entity<Visit>()
    .HasRequired( v => v.Address )
    .WithRequiredDependent( a => a.Visit );

modelBuilder.Entity<Address>()
    .HasRequired( a => a.Visit )
    .WithRequiredPrincipal( v => v.Address );

I am a little confused about why I have to use HasRequired in order to be able to use WithRequiredPrincipal/WithRequiredDependent, since not every entry in the Address table has an entry in the Visit table. That would seem to be "optional", not "required". But it appears to work, and maybe the "required" part is just internal to EF's model of the database, not the database itself.

score 1 · Accepted Answer · answered Aug 13 '15 at 13:42

There are 2 problems in the model:
- Only one of the Keys can be autonumbering, the other must get the same Id (this independently by EF).
- A mapping problem.
This model should work.

public class Address
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public string Description { get; set; }

    public virtual Visit Visit { get; set; }
}

public class Visit
{
    public Visit()
    {
        Address = new Address();
    }

    [Key]
    [ForeignKey("Address")]
    public int Id { get; set; }

    public string Description { get; set; }

    public virtual Address Address { get; set; }
}

Example of use

            var visit = new Visit
            {
                Description = "Visit",
                Address = {Description = "AddressDescription"}
            };

            db.Visits.Add(visit);

            db.SaveChanges();

Thanx for the reply, but it didn't work. The same runtime error is still generated (a complaint about not being able to find a column labeled 'Address_ID', which EF is presumably looking for in the Visits table. I updated my question to include some more information about calls to modelBuilder. — Mark Olbert, Aug 13 '15 at 14:36
I sent the code after run it. Try to drop the database and run the code again. — bubi, Aug 13 '15 at 22:08

score 1 · Answer 2 · answered Aug 13 '15 at 17:08

In addition to what bubi mentioned, your modelBuilder statement contradicts the model in that it doesn't mention Address.Visit as the inverse property. So it thinks that the property represents a separate relationship and tries to create the Address_ID column for that relationship.

You need to have

modelBuilder.Entity<Visit>()

    // from your description sounds like every Visit needs an Address
    .HasRequired(v => v.Address )

    // need to mention the inverse property here if you have one
    .WithOptional(a => a.Visit);

...or just remove the statement completely since you're already using attributes, and EF should be able to figure it out by convention.

EF6: Table Splitting Not Working

2 Answers2