CODEWITHSUNDEEP
entity-frameworkentity-framework-4pocofluent
BramVader
BramVader

Reputation: 43

Entity Framework 4.2: Multiple entities using the same many-to-many relationships

I have a table Travelers:

CREATE TABLE [dbo].[Travelers](
    [TravelerId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](25) NULL,
    [LastName] [nvarchar](50) NULL

a table Transporters and a join table TransporterTravelers

CREATE TABLE [dbo].[TransporterTravelers](
    [Transporter_TransporterId] [int] NOT NULL,
    [Traveler_TravelerId] [int] NOT NULL,

to create a many-to-many relationship between travelers and transporters. I used POCO classes to create the entities Traveler and Transporter, and the join table was automatically created (using CreateDatabaseIfNotExists initializer). As the project progressed we turned of automatic database creation, because the database has now been filled with data. Recently we added a view vwTravelersSummary to speed up things, that addresses the Travelers table and a couple of other tables using inner / left joins:

CREATE view [dbo].[vwTravelersSummary] as
SELECT
    tr.[TravelerId],
    tr.[FirstName],
    tr.[LastName],

    adr.[Street],
    adr.[Number], 
    adr.[PostalCode],
    adr.[Town],
FROM
    [dbo].[Travelers] tr
    LEFT JOIN (...)

I've created an POCO-class that is mapped to this view:

[DataServiceKey("TravelerId")]
[MetadataType(typeof(TravelerSummaryMeta))]
[Table("vwTravelersSummary")]
public class TravelerSummary
{
    public TravelerSummary()
    {
    }

    [Key]
    public int TravelerId { get; set; }
    ... 
    public string Street { get; set; }
    public int? Number { get; set; }
    public string PostalCode { get; set; }
    public string Town { get; set; }
}

I also needed a many-to-many relationship between this entity and transporters (we are using Data Services and we need this relationship in a query interceptor). So I added the following Fluent API call:

modelBuilder.Entity<TravelerSummary>()
    .HasMany(ts => ts.Transporters)
    .WithMany(/* No navigation from Transporter to TravelersSummary */)
    .Map(mc =>
        {
            mc.ToTable("TransporterTravelers");
            mc.MapLeftKey("Traveler_TravelerId");
            mc.MapRightKey("Transporter_TransporterId");
        }
    );

Everything seems to work, but... the original many-to-many relationship between the Travelers and Transporters has now become crippled. EF now responds with an error:

Invalid object name 'dbo.TransporterTravelers1'.

(due to convention-based naming?). So I specified explicitly also the original many-to-many relationship between travelers and transporters:

modelBuilder.Entity<Traveler>()
    .HasMany(t => t.Transporters)
    .WithMany(tr => tr.Travelers)
    .Map(mc =>
    {
        mc.ToTable("TransporterTravelers");
        mc.MapLeftKey("Traveler_TravelerId");
        mc.MapRightKey("Transporter_TransporterId");
    }
    );

Now I get the following error:

Schema specified is not valid. Errors: (2219,6) : error 0019: The EntitySet 'TravelerSummaryTransporter' with schema 'dbo' and table 'TransporterTravelers' was already defined. Each EntitySet must refer to a unique schema and table.

How can I solve this? Thanks in advance!

Upvotes: 1

Views: 1654

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364269

You cannot. EF doesn't support mapping table more than once and many-to-many relationship is represented by table mapping. Because of that you cannot use the table in two different many-to-many relationships (even they are actually the same but EF doesn't know about it).

The only way to reuse relationship is through inheritance but that is something which would not make sense in your model and it would probably caused you another issues.

Upvotes: 1

Related Questions