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

  • 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.

    Database relation diagram

    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

    Wednesday, November 27, 2013 3:36 AM

Answers

  • Hello,

    >>If PermitID is the foreign key into the Permits table, what is the SQL syntax to create the Foreign Key? 

    Do you mean that PermitID is both foreign key and primary key in table Permit?

    >>Do I need to drop the Column I am currently maintaining for this purpose, or can I define the Foreign Key from the existing column?

    We can define the Foreign Key from the existing column.

    >> In order to make the association optional, do I define the Foreign Key or Column as Nullable

    No, we should not define it to be Nullable.

    >> What is the syntax to do this?

    For this, I made an example Association and Customer, Association is 1-0…1 to Customer like below:

    And their syntax are:

    CREATE TABLE [dbo].[Customers] (
    
        [CustomerId]    INT            NOT NULL,
    
        [CustomerName]  NVARCHAR (MAX) NULL,
    
        [AssociationId] INT            NOT NULL,
    
        CONSTRAINT [PK_dbo.Customers] PRIMARY KEY CLUSTERED ([CustomerId] ASC)
    
    );
    

    CREATE TABLE [dbo].[Associations] (
    
        [AssociationId]   INT            NOT NULL,
    
        [AssociationName] NVARCHAR (MAX) NULL,
    
        CONSTRAINT [PK_dbo.Associations] PRIMARY KEY CLUSTERED ([AssociationId] ASC),
    
        CONSTRAINT [FK_dbo.Associations_dbo.Customers_AssociationId] FOREIGN KEY ([AssociationId]) REFERENCES [dbo].[Customers] ([CustomerId]) ON DELETE CASCADE
    
    );
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by BGood Tuesday, December 3, 2013 6:06 PM
    Friday, November 29, 2013 6:28 AM
    Moderator

All replies

  • Hello,

    What Entity Framework model have you used? Database first, Model first or Code First?

    If you are using Database first or Model first, we usually disable the lazy load to stop the circular relationships.

    In the constructor of the context class, we could use codes like below:

    this.Configuration.LazyLoadingEnabled = false;

    After this, if we want it load sub entity, we should use Include to load it like below:

    db.Associations.Include("Customer").ToList();

    If you are using code first, then we should do not to add parent navigation property in sub entity.

    If I have misunderstood, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 27, 2013 9:55 AM
    Moderator
  • Thanks, Fred.

    I am using database first. 

    My database SQL creation scripts create independent tables, reserving a column in the schema for the foreign key, but not defining the foreign key relation in the SQL script. 

    Currently I am using Linq to SQL and sqlmetal.exe to build the .dbml code for the OR/M.  Unfortunately, this approach requires me to manually redefine the relational associations between the tables every time there is a schema change.  With Entity Framework, I was hoping to be able to make schema changes in the designer, and have the relational associations persist after a schema change.

    Do you know of a way to define the relational associations in the SQL creation script so that the associations do not need to be remapped after a schema change?

    Thanks again!

    relation  contain a colum


    -BGood

    Wednesday, November 27, 2013 4:37 PM
  • Hi,

    >>My database SQL creation scripts create independent tables, reserving a column in the schema for the foreign key, but not defining the foreign key relation in the SQL script. 

    So there are no foreign key existing in database actually?

    >>Do you know of a way to define the relational associations in the SQL creation script so that the associations do not need to be remapped after a schema change?

    Is it that you want to keep the sql statement for create foreign key in the SQL creation script?

    In entity framework, the relationship will be created automatically. We do not need to define them.

    However, I notice that you do not define the foreign key in database and then there will no relational associations be created, because in entity framework the relational associations is based on foreign key.

    No foreign key and then no relational associattions.

    If I have misunderstood, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 28, 2013 10:00 AM
    Moderator
  • Hi Fred,

    Thanks for the helpful reply.  In response to your questions:

    • So there are no foreign key existing in database actually?  No, I have not defined a foregn key in the SQL script.  I maintain a table column (PermitID) for this purpose, but neither Linq to SQL, Entity Framework, or SQL itself knows about this and I have been manually manipulating the JOIN in code.
    • Is it that you want to keep the sql statement for create foreign key in the SQL creation script? Yes, I would like to define the foreign key relationship in the SQL script, but because this is legacy ADO.NET code I was using a column a table column to maintain the integer used as the foreign key. 

    For compatibility with Entity Framework, from your replies I now understand that any desired association needs to be defined as a Foreign Key rather than a table column.

    • If PermitID is the foreign key into the Permits table, what is the SQL syntax to create the Foreign Key? 
    • Do I need to drop the Column I am currently maintaining for this purpose, or can I define the Foreign Key from the existing column? 
    • In order to make the association optional, do I define the Foreign Key or Column as Nullable? 
    • What is the syntax to do this?

    Thanks for your help!


    -BGood

    Thursday, November 28, 2013 4:51 PM
  • Hello,

    >>If PermitID is the foreign key into the Permits table, what is the SQL syntax to create the Foreign Key? 

    Do you mean that PermitID is both foreign key and primary key in table Permit?

    >>Do I need to drop the Column I am currently maintaining for this purpose, or can I define the Foreign Key from the existing column?

    We can define the Foreign Key from the existing column.

    >> In order to make the association optional, do I define the Foreign Key or Column as Nullable

    No, we should not define it to be Nullable.

    >> What is the syntax to do this?

    For this, I made an example Association and Customer, Association is 1-0…1 to Customer like below:

    And their syntax are:

    CREATE TABLE [dbo].[Customers] (
    
        [CustomerId]    INT            NOT NULL,
    
        [CustomerName]  NVARCHAR (MAX) NULL,
    
        [AssociationId] INT            NOT NULL,
    
        CONSTRAINT [PK_dbo.Customers] PRIMARY KEY CLUSTERED ([CustomerId] ASC)
    
    );
    

    CREATE TABLE [dbo].[Associations] (
    
        [AssociationId]   INT            NOT NULL,
    
        [AssociationName] NVARCHAR (MAX) NULL,
    
        CONSTRAINT [PK_dbo.Associations] PRIMARY KEY CLUSTERED ([AssociationId] ASC),
    
        CONSTRAINT [FK_dbo.Associations_dbo.Customers_AssociationId] FOREIGN KEY ([AssociationId]) REFERENCES [dbo].[Customers] ([CustomerId]) ON DELETE CASCADE
    
    );
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by BGood Tuesday, December 3, 2013 6:06 PM
    Friday, November 29, 2013 6:28 AM
    Moderator
  • Thanks Fred.  Exactly what I was looking for.

    -BGood

    Tuesday, December 3, 2013 6:07 PM