StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POEntity Framework: Conditional foreign key
text
Body
copied!<p>I have the following schema in the database:</p> <ul> <li><strong>BillingReferences</strong> (ReferencingType <em>tinyint</em>, ReferencingId <em>tinyint</em>, ReferencedType <em>tinyint</em>, ReferencedId <em>tinyint</em>, IsActive <em>bit</em>) - where all fields (except IsActive) are part of a Unique Index.</li> <li><strong>BillingType</strong> (BillingTypeId <em>tinyint</em>, Name <em>varchar(50)</em>)</li> </ul> <p>ReferencingType and ReferencedType is a foreign key of BillingTypes. BillingTypes contains the following rows:</p> <p>BillingTypeId | Name</p> <p>1 | Labels</p> <p>2 | Countries</p> <p>3 | PaymentProviders</p> <p>4 | PaymentOptions</p> <p>5 | Banks</p> <p>ReferecingId and ReferencedId represent the Id of one of the following entities (depends on the Referenced/Referencing Type):</p> <ul> <li><strong>Banks</strong> (BankId <em>tinyint</em>, Name <em>varchar(50)</em>)</li> <li><strong>Countries</strong> (CountryId <em>tinyint</em>, Name <em>varchar(50)</em>)</li> <li><strong>Labels</strong> (LabelId <em>tinyint</em>, Name <em>varchar(50)</em>)</li> <li><strong>PaymentProviders</strong> (PaymentProviderId <em>tinyint</em>, Name <em>varchar(50)</em>)</li> <li><strong>PaymentOptions</strong> (PaymentOptionId <em>tinyint</em>, Name <em>varchar(50)</em>)</li> </ul> <p>In the future each entity will have some more different columns added but for now this is the schema for simplicity.</p> <p>There's a connection of (1-<em>) between every entity (except countries) to countries. Labels have a connection of (1-</em>) to Banks, PaymentProviders and PaymentOptions. And PaymentProviders have a connection of (1-*) to PaymentProviders</p> <p>So for example if I want to connect a bank with BankId 201 to a country with CountryId 3003 I will have a record in BillingReferences that will look like that: ReferencingType = 5 ReferencingId = 201 ReferencedType = 2 ReferencedId = 3003 IsActive = 1</p> <p>We didn't make a connection/reference table for each type of connection because of extendability consideration - If we want to add another entity all we have to do is add its table and add records for it in BillingReferences and BillingType.</p> <p>The problem is that I can't configure a conditional foreign key between BillingReferences and each of the entities and I can't seem to configure/map it with EntityFramework either...</p> <p>I was unable to find any tutorial or example that uses this type of implementation. Am I bound to create a reference table for each connection, or is there a way to configure this with EntityFramework?</p> <p>Thanks for the help :)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload