3

I need to create a relationship where I have a user table that links to an address table. The problem is that I need the address table to also store historic addresses. It is also possible that a user might not have an address at all. 

public class user
{
  public virtual int ID { get; set; }
  ...
  public virtual int? AddressId { get; set; }

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

  public virtual ICollection<Address> HistoricAddresses { get; set; }
}

public class Address
{
  public virtual int ID { get; set; }
  ...
}

I tried various ways to get this to work and got various errors like putting another table between User and Address:

public class HistoricAddress
{
  public virtual int ID { get; set; }
  public Address HistoricAddress { get; set; }
}

public class user
{
  public virtual int ID { get; set; }
  public virtual Address CurrentAddress { get; set; }
  public virtual ICollection<HistricAddress> HistoricAddresses { get; set; }
  ...
}

and various other ways, but this also throws up errors. There must be a proper way of doing this. The last error I got was:

"System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.InvalidOperationException: A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'ID'."

Eranga
  • 32,181
  • 5
  • 97
  • 96
JohnCambell
  • 633
  • 8
  • 23
  • What is the problem with your first code snippet? Code-First conventions should map that correctly without further configuration with two relationships between `User` and `Address`, doesn't it? – Slauma Sep 16 '11 at 10:46

1 Answers1

4

You can map the following model with 3 tables.

public class user
{
  public virtual int ID { get; set; }
  ...
  public virtual int? AddressId { get; set; }

  public virtual Address CurrentAddress { get; set; }

  public virtual ICollection<Address> HistoricAddresses { get; set; }
}

public class Address
{
  public virtual int ID { get; set; }

  public virtual ICollection<User> Users { get; set; }
  ...
}

Override the OnModelCreating method of your custom DbContext

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .HasOptional(user => user.CurrentAddress).WithMany()
        .HasForeignKey(user => user.AddressId);

    modelBuilder.Entity<User>()
        .HasMany(user => user.HistoricAddresses)
        .WithMany(address => address.Users)
            .Map(m =>
            {
                m.ToTable("UserAddresses");
                m.MapLeftKey("UserId");
                m.MapRightKey("AddressId");
            });
}

Tables created are

  • Users
  • Addresses
  • UserAddresses
Eranga
  • 32,181
  • 5
  • 97
  • 96
  • Did you mean two tables because I can't see where the third table comes in here. I also had a problem with the second fluent-api mapping (.HasForeignKey was not supported on a many to many relationship). I tried it without the .HasForeignKey but when running the code got the same error as the one I mentioned in my post. – JohnCambell Sep 16 '11 at 11:41
  • @JohnCambell Third table is `UserAddresses`. `.HasForeignKey` was added by mistake. Please see my updated answer. – Eranga Sep 16 '11 at 11:46
  • got the third table bit now, I was thinking of entities. – JohnCambell Sep 16 '11 at 11:46
  • @JohnCambell Yes only 2 entities. – Eranga Sep 16 '11 at 12:30
  • I think the relationship is working but fails when trying to get data in. As soon as I assign an address to a user it causes the "A dependent property in a ReferentialConstraint is mapped to a store-generated column. Column: 'ID'" error. I was thinking of adding the address first into the collection and then assigning it with the CurrentAddressId, but how does it know what this is in within a transaction before the user is saved. – JohnCambell Sep 16 '11 at 12:50
  • @JohnCambell You may have to do it in two transactions wrapped inside a `TransactionScope` object. – Eranga Sep 17 '11 at 00:54