modify Xref table with Entity Framework?

Question

I have 3 tables, but when I create my edmx, it only shows 2 on the edmx model. I also can't access my xref table through navigation. I am able to insert the primary key id from each table into the xref table, but I have no idea how to do modify the xref table since I am not getting any navigation properties and it does not show up in the model. Here are the tables

  **Subscription Table**
[Id] [int] IDENTITY(1,1) NOT NULL,
[SubscriptionTypeId] [int] NOT NULL,
[Active] [bit] NULL,
[IsScheduledNotification] [bit] NOT NULL,
[NotificationFrequencyInMinutes] [int] NOT NULL,
[CompanyId] [int] NULL,
    CONSTRAINT [PK__Subscription] PRIMARY KEY CLUSTERED 

    **[Exchange]**
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Code] [nvarchar](100) NULL,
    PRIMARY KEY CLUSTERED 

    **[SubscriptionExchange] - Xref table**
[SubscriptionId] [int] NOT NULL,
[ExchangeId] [int] NOT NULL,
    CONSTRAINT [PK_SubscriptionExchanges] PRIMARY KEY CLUSTERED 
    (
[SubscriptionId] ASC,
[ExchangeId] ASC
    )

So I am using Asp.Net MVC and trying to modify the records. I am currently able to modify the Subcription table but need to modify [SubscriptionExchange] xref table as well.

I already have foreign keys set in the database for SubscriptionExchange table

The problem is when I execute the following code it doesnt take into account updating the SusbcriptionExchange table. The object a below is referring to Subscription table in the database and you would see exchanges collection in the block. I have initialised the collection with the exchange ids. The subscription id is initialised in line one.

     Avanade.Bureau.DataAccessLayer.DatabaseModel.Subscription a = new DataAccessLayer.DatabaseModel.Subscription
                    {
                        Id = model.SubscriptionId,
                        SubscriptionTypeId = model.SubscriptionTypeId,
                        IsScheduledNotification = false,
                        Active = true,
                        NotificationFrequencyInMinutes = 104,
                        Exchanges = GetExchanges(postedExchanges, bureauEntities),
                        Users = GetUsers(postedUsers, bureauEntities),
                        CompanyId = model.CompanyId

                    };
                    bureauEntities.Entry(a).State = EntityState.Modified;
                    bureauEntities.Subscriptions.Attach(a);
                    bureauEntities.SaveChanges();

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

This is by design.

Your XREF table SubscriptionExchange is what is known as a pure junction table (i.e. it has only the XREF fields, and no other additional columns. In this case, EF will abstract the junction table (i.e XREF table) and the 2 sides get a navigational property exposing a collection of the other side.

See Entity Framework: Queries involving many to many relationship tables

If the junction table (sometimes called bridge table, association table, link table, etc) only consists of the foreign keys and no other columns, then that table is abstracted by EF and the two sides get a navigational property exposing a collection of the other side.

If you had additional fields besides the Foreign Key table, then you will see the XREF table in your .edmx model. I added a new field called IsActive to the XREF table, and now I see it in the model.

enter image description here

This design makes sense. If you still must have the Many-2-Many XREF table in your Model, here's a way to accomplish that. But be warned this is not common and you are likely doing something wrong.

edited May 23 '17 at 12:07

Community

1
1

answered Jun 18 '14 at 22:36

Shiva

20,575
14
82
112

The SubscriptionExchange table already has two foreign keys defined in them. SusbcriptionId and ExchangeId. I am also able to see the navigation properties in my designer. – user3751248 Jun 18 '14 at 23:00
Understood. I found out this is by design (the fact you are not seeing the xref table). See my updated Answer. – Shiva Jun 18 '14 at 23:44
Thanks Siva, the link was quite useful and informational but no where it mentions about how to handle the save in the junction table. It only talks about ways and means to show it in the Entity framework. I am still confused at this point if I need to expose the junction table in entity framework as I read it somewhere that it is not the desired way to do it.Could you please let me know looking at my code above how do I go about saving the records to the junction table? Thanks once again – user3751248 Jun 19 '14 at 06:40
Ok, I will try and put a sample together for you later today. – Shiva Jun 19 '14 at 19:04
Thanks Siva. It would be helpful. If you notice in the code above I have one subscription id and collection of exchanges ids. I need to update them in the junction table. – user3751248 Jun 19 '14 at 21:18
Cant it be done without including the junction table. My save functionality works without including the junction table its only the modify functionality that needs to work. – user3751248 Jun 19 '14 at 21:31
I'll have to try it out. Will let you know soon. – Shiva Jun 19 '14 at 21:34
Thanks Shiva waiting for your reply. It would be really helpful. – user3751248 Jun 22 '14 at 10:02
Oops, sorry I got busy. Glad you figured it out yourself :) – Shiva Jun 24 '14 at 18:54

modify Xref table with Entity Framework?

1 Answers1