none
How to model optional (zero..one) Entity Framework association with SQL CE without 'Infinity Mirror' reflection?

    Question

  • I have a legacy relational database using SQL Server Compact 3.5 SP1 and ADO.Net Data Services which I would like to adapt for use with Entity Framework, but I am running into an 'Infinity Mirror' problem which I will explain below.

    The database consists of 3 independent tables: Residential_properties, Commercial_properties, and Building_permits.  The two primary tables, Residential_properties and Commercial_properties, may optionally have an associated record in the Building_permits table which is referenced through the foreign key column "Permit_ID" which is the primary key index of the Building_permits table.  The Building_permits table may alos contain building permit records which are not associated with records in the Residential or Commercial property tables.

    Foreign key relationship between property and permit tables

    In Entity Framework terms, I think this flexible association is called "zero to one", but I have pretty much handled the relational aspects manually in ADO.Net code.  My problem is when I try to migrate the legacy database schema to Linq to SQL or Entity Framework I end up with "Infinity Mirror" circular relationships or associations where the intended parent property table (Residential or Commercial) becomes reflected back through the child table (Building_permits) in a circular graph which is difficult to articulate but can be represented something like this:

    Residential > PermitID > Permit_record > Residential ...

    Commercial > PermitID > Permit_record > Commercial ...

    Permit > Residential

    and even Residential ... Commercial and Commercial ... Residential

    How do I model this as a simple one-way relationship using the existing column "PermitID" as a foreign key?  I am not interested in having Entity Framework handle strict referential integrity since this has been handled in my legacy code.

    My goal is to implement this system so that the schema can be maintained in Visual Studiuo without having to use sqlmetal.exe and tweak the associations files every time the schema changes. 

    Thanks for any help.


    -BGood

    Tuesday, November 12, 2013 3:26 AM

Answers

  • Hello,

    Sorry for delay.
    I'm not familiar with Entity Framework association. As per my understand, relationships between tables is defined through foreign keys and there is not such 'Infinity Mirror' problem in relational databases.
    If you want to create table with that relationship in relational database, SQL Server Compact database for example, you can refer to the following statement:

    Create Table [Building Permits]
    (PermitID  int  Primary key,
    ...)

    Create Table [ResidentialProperties]
    (ResidentialID int Primary key,
    PermitID int Foreign key references [Building Permits](PermitID),
    ...)
    Create Table [CommercialProperties]
    (CommercialID int Primary key,
    PermitID int Foreign key references [Building Permits](PermitID),
    ...)

    If I have any misunderstand, can you post more details about the 'Infinity Mirror' problem and your requirement.
    Since the question is also related to Entity Framework, you can also post a question in the following forum:
    ADO.NET Entity Framework and LINQ to Entities. The Entity Framework experts will assist you.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Thursday, November 14, 2013 8:16 AM