CODEWITHSUNDEEP
c#entity-frameworkt-sql
Clinton Pierce
Clinton Pierce

Reputation: 13159

Entity Framework 6, table with references to multiple other tables

I have a project that has many tables which will require an address:

Instead of a CustomerAddress, OrderAddress, and VendorAddress table, we're trying to use a common address table like this:

AddressId  (PK, int, identity, not null)
AddressType (int, not null, poss values: Customer, Order, Vendor, etc..)
ForeignKeyId (int, not null)
Name
Street
City
etc...

And a customer table:

CustomerId (PK, int, identity, not null)
...other customer information...

Where if I had a Customer (CustomerId = 56), there might be an Address record like this:

AddressId (some number)
AddressType = 1 (Customer)
ForeignKeyId = 56
Name, Street, City

This is all well and good.

However, the problem comes when I go to add a new Customer to the system. In EF, I think this means I have to add the customer, SaveChanges() and then add the address separately.

Customer c = new Customer { ... };
context.Customer.Add(c);
context.SaveChanges();    // In order to get the CustomerId to be filled in
Address a = new Address { AddressType = 1, ForeignKeyId = c.CustomerId };
context.Address.Add(a);
context.SaveChanges();

This causes a couple of problems structurally with the code. First, the way things go, we may not be adding the Customer and the Address in the same place. Second, more importantly, this necessitates two SaveChanges() which means we no longer get a transaction (or we've got to make our own transaction and carry it around).

What I'd like to do is more like this:

Customer c = new Customer { ... };
context.Customer.Add(c);
Address a = new Address { AddressType = 1 };
context.Address.Add(a);
// Somehow magic happens and Address can be associated with
// Customer as soon as we have a CustomerId assigned.
context.SaveChanges();

I know there's no magic, but is there way for me to accomplish what I want with one SaveChanges()?

Edit: If it's relevant, this is Database First EF.

Upvotes: 1

Views: 1227

Answers (1)

Ian
Ian

Reputation: 748

If the database doesn't have a relationship between the tables, you will have to Add() each object then SaveChanges().

Whats the relationship between the two e.g. one-to-one, one-to-many?

Having a relationship allows you to write your code like this:

Customer c = new Customer
{
    ...
    Address = new Address { AddressType = 1 }
};

context.Customer.Add(c);
context.SaveChanges();

If you don't have a database relationship established and you cannot do it, you should be able to virtualize the relationship in your EM.

In your Context class file, modify

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    //default statement
    //throw new UnintentionalCodeFirstException();

    //establish the one-to-zero-or-one relation
    modelBuilder.Entity<Customer>()
        .HasOptional(c => c.Address);
}

Add an appropriate property to your Customer and Address classes. As an example for a one-to-one relationship.

public virtual Address Address {get; set;} in Customer and public virtual Customer Customer {get; set;} in Address

I'll update the OnModelCreating() shortly. I've got a meeting to attend. If anyone has any additional, just throw it in the comments (hopefully I'm not too jacked up).

EDIT See this SO answer for setting up the relationships.

EDIT 2 IRT DB First, as long as the tables are in the same context, you should only need to SaveChanges() once. To ensure the context is the same across the DAO, initialize the context when the DAO is instantiated.

private DbEntities db = new DbEntities();

You would then use this db context instance throughout.

Upvotes: 1

Related Questions