locked
An inquiry regarding DDL generated for a many-to-many entity relationship RRS feed

  • Question

  • WHAT I HAVE:

    Visual Basic 2015, Entity Framework, WinForms

    MY ISSUE:

    I'm creating an entity model, Model-First, and I have 2 entities, SocialContact and Residence, which have a many-to-many relationship. When I generated the DB from the model, it creates tables SocialContacts, Residences, and (behind the scenes) junction-table SocialContactResidence. It then creates primary keys for all the tables and foreign keys linked SocialContactRessidence to the other 2 tables. FINALLY: It also creates a (non-clustered) index on foreign-key FK_SocialContactResidence_Residence, but NOT for FK_SocialContactResidence_SocialContact. Is this a bug (putting an index on one foreign key but not the other) or is this by design?

    The DDL is as follows:


    -- --------------------------------------------------
    -- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
    -- --------------------------------------------------
    -- Date Created: 12/14/2016 20:45:05
    -- Generated from EDMX file: C:\Users\Robert G\Visual Studio Projects\SocialContactsTracker\SCTModel1.edmx
    -- --------------------------------------------------

    SET QUOTED_IDENTIFIER OFF;
    GO
    USE [SampleSCTDB1];
    GO
    IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
    GO

    ... (stuff)

    -- --------------------------------------------------
    -- Creating all tables
    -- --------------------------------------------------

    -- Creating table 'SocialContacts'
    CREATE TABLE [dbo].[SocialContacts] (
        [Id] int IDENTITY(1,1) NOT NULL,
        [DateAdded] datetime  NOT NULL,
        [Information] nvarchar(max)  NULL,
        [Picture] varbinary(max)  NULL,
        [BeginningDate] datetime  NOT NULL,

       ... (stuff)

    );
    GO

    -- Creating table 'Residences'
    CREATE TABLE [dbo].[Residences] (
        [City] nvarchar(40)  NOT NULL,
        [State] nvarchar(2)  NOT NULL,
        [ZIP] nvarchar(16)  NOT NULL,
        [Id] int IDENTITY(1,1) NOT NULL,
        [Country] nvarchar(40)  NOT NULL,
        [Address_HouseNumber] decimal(6,2)  NOT NULL,
        [Address_Street] nvarchar(40)  NOT NULL,
        [Address_Apartment] decimal(6,2)  NOT NULL
    );
    GO

    ... (stuff)


    -- Creating table 'SocialContactResidence'
    CREATE TABLE [dbo].[SocialContactResidence] (
        [SocialContacts_Id] int  NOT NULL,
        [Residences_Id] int  NOT NULL
    );
    GO

    ...

    -- --------------------------------------------------
    -- Creating all PRIMARY KEY constraints
    -- --------------------------------------------------

    -- Creating primary key on [Id] in table 'SocialContacts'
    ALTER TABLE [dbo].[SocialContacts]
    ADD CONSTRAINT [PK_SocialContacts]
        PRIMARY KEY CLUSTERED ([Id] ASC);
    GO

    -- Creating primary key on [Id] in table 'Residences'
    ALTER TABLE [dbo].[Residences]
    ADD CONSTRAINT [PK_Residences]
        PRIMARY KEY CLUSTERED ([Id] ASC);
    GO

    ... (stuff)

    -- Creating primary key on [SocialContacts_Id], [Residences_Id] in table 'SocialContactResidence'
    ALTER TABLE [dbo].[SocialContactResidence]
    ADD CONSTRAINT [PK_SocialContactResidence]
        PRIMARY KEY CLUSTERED ([SocialContacts_Id], [Residences_Id] ASC);
    GO

    ... (stuff)

    -- --------------------------------------------------
    -- Creating all FOREIGN KEY constraints
    -- --------------------------------------------------

    ... (stuff)

    -- Creating foreign key on [SocialContacts_Id] in table 'SocialContactResidence'
    ALTER TABLE [dbo].[SocialContactResidence]
    ADD CONSTRAINT [FK_SocialContactResidence_SocialContact]
        FOREIGN KEY ([SocialContacts_Id])
        REFERENCES [dbo].[SocialContacts]
            ([Id])
        ON DELETE NO ACTION ON UPDATE NO ACTION;
    GO

    -- Creating foreign key on [Residences_Id] in table 'SocialContactResidence'
    ALTER TABLE [dbo].[SocialContactResidence]
    ADD CONSTRAINT [FK_SocialContactResidence_Residence]
        FOREIGN KEY ([Residences_Id])
        REFERENCES [dbo].[Residences]
            ([Id])
        ON DELETE NO ACTION ON UPDATE NO ACTION;
    GO

    -- Creating non-clustered index for FOREIGN KEY 'FK_SocialContactResidence_Residence'
    CREATE INDEX [IX_FK_SocialContactResidence_Residence]
    ON [dbo].[SocialContactResidence]
        ([Residences_Id]);
    GO

    ... (stuff)

    -- --------------------------------------------------
    -- Script has ended
    -- --------------------------------------------------


    Robert Gustafson


    Thursday, December 15, 2016 2:37 AM

All replies

  • Hi,

    I can reproduce your issue. When you use a combined primary keys as foreign keys, it does not meet the third paradigm of relational database requirements. In many-to-many relationship ,try not to use the combined  key,  even to avoid to use the combined key to do foreign keys,  you should defines a meaningless ID column in the table for the primary key. 

    In addition, Microsoft’s Entity Framework would automatically create indexes on foreign key constraints. When you use a combined primary keys as foreign keys, I think Microsoft’s Entity Framework must not allow you add non-clustered index for each  foreign key.

    Best Regards,

    Bob


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 21, 2016 5:01 AM
  • I'm not sure I understand this. I defined my setup Model-First, which means that I created the conceptual model from scratch (a more intuitive approach for me), and the above DDL was generated when I clicked "Generate Database From Model". I defined my many-to-many entities by relating them directly to each other (no junction entity) using navigation properties only (EF doesn't allow foreign keys for "direct" many-to-many entity relationships). Are you suggesting that I should explicitly create a junction entity between SocialContacts and Residences and give it an Id column, instead of allowing EF to generate the "hidden" junction table for use by the store only? (I was under the impression that since the junction table exists only in the store model, it wouldn't have a primary key.)

    BTW. Your grammar and punctuation makes what you're saying hard to follow. (No offense.)


    Robert Gustafson


    Friday, December 23, 2016 9:20 AM
  • Hi RobertGustafson,

    Based on your description, I create a demo and reproduce your issue on my side, it seems that entity framework only generate on non-clustered index for combined primary keys.

    we could add FK_SocialContactResidence_SocialContact index by manually.

    In addition, you could also post a feedback on the following link.

    https://connect.microsoft.com/VisualStudio/Feedback 

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 29, 2016 7:43 AM
  • I also wonder why even one foreign key in the junction-table in the store has an index. What need is their for an index there? Also, could the presence of the index complicate efforts to remove the relationship between an instance of SocialConact and an instance of Residence? Like, I've had trouble with the following code:

    Dim sc As SocialContact = result from query

    Dim res As Residence = result from query

    context.Entry(sc).Collection("Residences").Load() 'Residences is navigation property

    sc.Residences.Remove(res)

    context.SaveChanges()



    Robert Gustafson



    Tuesday, January 3, 2017 5:18 PM
  • Hi RobertGustafson,

    Based on your description and related code, it seems that you want to delete many-to-many relationship records via entity framework, if you use entity framework 6, please refer to: 

    http://www.entityframeworktutorial.net/delete-many-to-many-entities-in-entity-framework.aspx

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 5, 2017 8:10 AM
  • I'm using DBContext now, so the above link (which deals with ObjectContext) doesn't apply to me. What's more, my real question was whether an index on one FK on the (hidden store) junction table but not the other is tripping up my attempt to delete a many-to-many relationship. Is the following DDL (which creates the IX_FK_SocialContactResidence_Residence index) supposed to be there, or did something go wrong, requiring me to rebuild the relationship?

    And if it is supposed to be there, why isn't there similar DDL to create an IX_FX_SocialContactResidence_SocialContact index? All I know is that I've been doing everything "right" and still can't delete a relationship instance. (It doesn't make sense for EF to put an index on an FK field in a junction table that exist only in the store, not the conceptual mode--nor does it make sense that doing so would trip up my many-to-many "Remove" code.)

    DDL as follows (see also top of page):

    -- Creating non-clustered index for FOREIGN KEY 'FK_SocialContactResidence_Residence'
    CREATE INDEX [IX_FK_SocialContactResidence_Residence]
    ON [dbo].[SocialContactResidence]
        ([Residences_Id]);
    GO

    Finally, I should point out that my "SocialContact" entity-set has many-to-many relationships with other entity-sets besides "Residence". Could that be my problem, and how do I solve it?



    Robert Gustafson







    Monday, January 9, 2017 3:33 AM