CODEWITHSUNDEEP
c#.netvb.netentity-frameworkentity-framework-6
David Grogan
David Grogan

Reputation: 53

EF6 Code First : Entity with multiple relationships

I am creating a new database using EF code-first which contain the following classes: 

Address, Contact, Account, Customer (a sub-class of Account), and SalesOrder.

The Address class is the one giving me problems at the moment, it can have no foreign key because it can be linked to any of the other five classes (with more to come), and each of the other classes can have one or more navigation properties pointing back to it.

The navigation properties should look as follows:

Contact.AddressId?
Contact.Address

Account.AddressId?
Account.Address

Customer.DeliveryAddresses

SalesOrder.InvoiceAddressId
SalesOrder.InvoiceAddress

SalesOrder.DeliveryAddressId?
SalesOrder.DeliveryAddress

It should be possible for these classes to share the same Address record, e.g. an Account has an Address, this can also be linked to a SalesOrder, a different Address, linked to the Customer, could be linked to another SalesOrder. All Addresses linked to Accounts and Customers should be unique, but other classes should be able to share links to these Addresses.

I have tried setting it up with all the possible fluent configurations I can think of, with my DbContext having a DbSet property and without (ultimately I don't think it should have it's own DbSet property, as the Addresses should only be accessible from the various root objects, but if that's the only way to get it to work I'm happy to manage the inserts/deletes myself).

I tried making all the navigation properties nullable (ideally SalesOrder.InvoiceAddressId should not be nullable), and also had to remove the Customer.DeliveryAddresses Many-to-Many mapping at one point because that was confusing the issue.

I get various errors depending on how I have it set up, either Multiplicity conflicts due to non-nullable fields, or Cascade on Delete errors when I have no DbSet property and I try and let EF handle the inserts and deletes.

I also end up with unwanted null rows when I do have a DbSet property set. e.g:

add three Address records to the DbSet (Address(1), Address(2), Address(3),
add two Accounts to the DbSet (Account(1) & Account(2)),
add multiple SalesOrders,
set Account(1).AddressId = 1
set Account(2).AddressId = 2, 
set SalesOrder(n).InvoiceAddressId = 1,
set SalesOrder(n).DeliveryAddressId = 3

This will correctly create the Address records, but the related keys will only be set correctly if the various Id foreign-key properties are used, rather than the navigation property, and even if the Id properties are used the foreign keys all look correct, but orphaned records for each SalesOrder (or two per order if both navigation properties are used) end up in my Address table with all their fields bar Id set to NULL.

The only thing I can think of that I haven't tried would be to create multiple sub-classes of Address and use each one with it's related class (e.g. SalesOrderDeliveryAddress), but that doesn't seem ideal. I'd rather not do that unless I have to.

Is what I'm looking for possible to set up in EF, or is there some other way to go about doing it?

Thanks, David

Upvotes: 1

Views: 1384

Answers (1)

Colin
Colin

Reputation: 22595

There are several issues making this confusing. To start with I would switch off the default cascade on delete to get rid of multiple cascade paths and come back to that later.

Then read about adding disconnected trees, foreign keys and navigation properties here: http://msdn.microsoft.com/en-us/magazine/dn166926.aspx

Then I would set up the entities you way you want them and repost a more specific issue. (You have tried lots of stuff so it's hard to work out what happens when here)

Once you've got adding and updating working you can come back and work out where you can put in cascade delete and where it needs to be manual

Upvotes: 1

Related Questions