1-to-1 relationship RRS feed

  • Question

  • Hi,

    is it possible to define an entity type (or two entity types with a one-to-one association) that is mapped onto two tables that are related by a foreign key, and where deleting an instance of the entity type (or two associated instances of the two entity types through a cascading delete of the principal entity type) deletes the corresponding records in the two tables? Example:

    CREATE TABLE [tblOrganisationProfiles] (
    [OrgID] [char] (3) PRIMARY KEY,
    [ContactPerson] [int] REFERENCES [Contact] (ID),
    ...(profile information)...

    CREATE TABLE [tblContacts] (
    [ID] [int] IDENTITY(1,1) PRIMARY KEY,
    [FirstName] [nvarchar] (50),
    [FamilyName] [nvarchar] (50),

    Or is it necessary to use a shared key for this to work?

    Saturday, August 30, 2008 4:25 PM

All replies

  • No, to do Entity Spitting (one entity type for two or more tables), you need the same Key.

    If you have a relationship as yous proposed, you are on a 0..1 -> * associationship in DB, so I think that in your case, there is a problem in your DB. Whenever, you can change the cardinality of a relationship on your EDM but I think that it's better if you can change your DB.

    Saturday, August 30, 2008 5:40 PM
  • Thanks for your reply!


    Ok, I'm a bit unclear on how to model this (in the DB and in the EF model), but it seems to be common scenario what I have. I have a Registration with a Contact Person. On the DB layer, it makes sense to me to store the Contact Person in a generic Contacts table, and then have a field in the Registrations table point at the relevant Contact. Registrations and Contacts should always be created and deleted in pairs. In the end, I will like to expose the data as a REST API via ADO.NET Data Services, where I with a single HTTP POST I can add another Registration+Contact, and with a single HTTP DELETE I can delete an existing Registration+Contact. It's acceptable to HTTP GET a Registration and to navigate to the Contact or to use the ADO.NET Data Services "expand" feature to retrieve the pair in one go. I'd like to implement this w/o writing code. Perhaps I would need to write a few stored procedures, that's acceptable I guess.


    Thanks for any advice!



    Monday, September 1, 2008 11:59 AM
  • Entity Framework doesn't support entity splitting for the kind of relationship you have, which is 1-*. To do entity splitting in EF v1, each table involved has to have the same key. Currently, you have a schema like so:

    Table1ID, int, PK
    Table2RefID, int, FK

    Table2ID, int PK

    Even though you manage records in both tables as a pair, technically speaking, the database won't restrict multiple records in Table1 from referencing a single record in Table2...that poses a problem for EF. It can't ensure that modifying the record in Table2 won't break other entities that may be in memory and related to it...its just a possible problem, but EF currently can't verify that it won't actually be a problem. To do entity splitting, you need to modify your schema to be like so:

    CommonID, int, PK, FK

    CommonID, int PK, FK

    With the above schema, Table1 and Table2 share a common key, which guarantees that a record with ID 1000 in Table1 will only ever map to a record with ID 1000 in Table2. Its a fixed 1-1 relationship that EF knows how to deal with, and is able to guarantee that any update to either record will not affect other entities in memory. This is a less common scenario...I most often see schemas structured like the first example, even if its technically incorrect. The second scenario is a better way of accomplishing what is needed...you need an explicit 1-1 relationship which can't be accomplished if each table involved has a different key.

    Tuesday, September 2, 2008 10:02 PM
  • Thanks again for this clarification!


     jrista wrote:

    Even though you manage records in both tables as a pair, technically speaking, the database won't restrict multiple records in Table1 from referencing a single record in Table2...that poses a problem for EF.

    I was thinking about adding a UNIQUE constraint on the FK column to avoid this problem. However, such a solution seems to have other problems, e.g. doesn't support many NULLs. And anyway, I'm not sure EF cares about UNIQUE constraints, and if not, it adds no benefit in this case. Furthermore, I'm not even sure it's correct to have such conditions modeled at the DB layer, which I guess should be designed and optimized for other aspects. I thought this is where EF would help me, by providing a layer where the more higher level structure could be represented and mapped to the underlying DB structure.
     jrista wrote:
    I most often see schemas structured like the first example, even if its technically incorrect. The second scenario is a better way of accomplishing what is needed...

    The alternative CommonID solution might work, but I'm a bit hesitant to use it, since 1) the Contacts table should ideally not need to be aware of the existance of the referring Registrants table (Table1); and 2) in Table1 I am able to assign a meaningful key (e.g. the ID of the organization) whereas in Table2 I would most likely use a surrogate key (autonumber). In other words, they should not need to be so tightly dependent. A Contact may not only be associated with a Registration, but may serve many roles, and therefore be associated with other Tables. Should I need to use a common key in all these tables, just to enforce the 1-to-1 condition?
    So the other solution I was thinking of is to use stored procedures that hide the structure of the DB tables, and which provide for adding/deleting/getting Registrant+Contact records in pairs. Will check to see whether this works with EF!
    Wednesday, September 3, 2008 8:28 AM
  • I understand what you mean about keeping Contact isolated. If you think you may need to associate it with other things in the future, then a shared key is probably not the solution. The problem is that EF imposes certain restrictions on what can/can't be accomplished in terms of inheritance and table splitting. You could share the Contact key for all instances that "extend" contact, like Registration. That would make it easy to use EF table splitting capabilities...but that ultimately doesn't provide for a logical schema.


    Instead, I recommend creating views, or set up DefiningQuerys for each composite that needs to be an entity. My personal preference is to use views (if its an INNER JOIN, the view can be indexed to improve performance in SQL Server 2005+). Any SSDL type that has a DefiningQuery is going to be read-only, and will require stored procs to handle CUD operations. That is fine though, since EF couldn't figure out how to properly update your schema anyway, and there is currently no mechanism in EF that allows you...annotate...or fine-tune automatic CUD operations.


    Some things to be aware of if you take the View/DQ route:


    1. You should probably remove the default Registrant and Contact entities from your model (all parts, CSDL, SSDL, MSL). Unless you specifically need both the separate entities and the composite entity, having all of the various entities and associations could become problematic (not just from a maintenance standpoint, but from an update-from-database standpoint).
    2. If you create a View, and use the designer to manage your model, any Update-from-Database will restore the "key" for the view. It will not use any key you may define in a unique clustered index, it will just use all non-nullable columns (barring a couple exceptions). You will need to edit the XML directly to set the key properly. After the key is set in CSDL, it will be fine. Its just the SSDL that gets overwritten each update.
    3. Make sure you don't leave any rogue associations around in your SSDL. The designer likes to keep associations synced between CSDL and SSDL...any associations in your SSDL will either constantly be added back to your conceptual model, or you will get a bunch of validation errors on save/build.
    4. Make sure you map ALL CUD operations...you can't map just one or two. Also, the procs need to take all keys involved in your entity as parameters at a minimum. If you create associations to your conceptual "Registrant" entity, the keys from those associations will also need to be included in the paramters for INSERT, UPDATE, and DELETE procs (yes, the delete too...there are plans to change that, but for now in v1 its required).

    Once you create your view, map it, and hook up the CUD procs, you should be able to query your view and make updates in code "transparently" for all intents and purposes. Let me know if you need any additional info.

    Wednesday, September 3, 2008 4:14 PM