Entity Framework many-to-many With additional column

Question

I have two classes:

public class Address
{
    public Guid Id { get; set; }

    public virtual ICollection<ToAddressMessageLink> MessagesTo { get; set; }
    public virtual ICollection<CopyAddressMessageLink> MessagesCopyTo { get; set; }
}

public class Message
{
    public Guid Id { get; set; }

    public virtual ICollection<ToAddressMessageLink> To { get; set; }
    public virtual ICollection<CopyAddressMessageLink> CopyTo { get; set; }
}

And I need to save connection between them in a single table. If I simply put many-to-many relation between, EF wouldn't realize what type of connection is actually set(table rows will be identical for them). So I have to create a link class with a discriminator like following:

public class AddressMessageLink
{
    public int LinkType { get; set; }
    public Guid AddressId { get; set; }
    public Guid MessageId { get; set; }
}

But it also doesn't work because I can't set link type/ So I have to use TPH here:

public abstract class AddressMessageLink
{
    public int LinkType { get; set; }
    public Guid AddressId { get; set; }
    public Guid MessageId { get; set; }
}

public class CopyAddressMessageLink : AddressMessageLink
{
    public virtual AddressDto Address { get; set; }
    public virtual MessageDto Message { get; set; }
}

public class ToAddressMessageLink : AddressMessageLink
{
    public virtual AddressDto Address { get; set; }
    public virtual MessageDto Message { get; set; }
}

With a composite key:

HasKey(x=>new {x.AddressId, x.MessageId, x.LinkType});

But it doesn't work either, because EF:

"The foreign key component AddressId is not a declared property on type ToAddressMessageLink".

If I put AddressId and MessageId into derived class I can't set key because there is no component of it in base class.

What can I do it this situation?

score 1 · Answer 1 · answered Mar 09 '17 at 09:57

If u have to classes, that you want to connect, you need to create a class that will connect yours two tables. And you did that there:

public class AddressMessageLink
{
    public int LinkType { get; set; }
    public Guid AddressId { get; set; }
    public Guid MessageId { get; set; }
}

But you don't join virtual attributes. So you have to do like this.

In your tables definition:

public class Address
{
    public Guid Id { get; set; }

    public virtual ICollection<AddressMessageLink> AddressMessageLink { get; set; }
}

public class Message
{
    public Guid Id { get; set; }

    public virtual ICollection<AddressMessageLink> AddressMessageLink { get; set; }
}

And in AddressMessageLink object:

public class AddressMessageLink
{
    public int LinkType { get; set; }
    public Guid AddressId { get; set; }
    public Guid MessageId { get; set; }

    public virtual Address Address { get; set; }
    public virtual Message Message { get; set; }
}

That will connect your tables as many to many.

it would work in thas case, but if i add another property of type ICollection, it would lead to "The relationship Address_MessagesTo' was not loaded because the type is not available" — ALex Qz, Mar 09 '17 at 10:56
Show me, what you want to do, what another property of this type ? You can define only one property of this type in one object. — Damian Sędrowski, Mar 09 '17 at 12:14
`(public class Address { public Guid Id { get; set; } public virtual ICollection MessageTo { get; set; } public virtual ICollection MessageCopyTo { get; set; } } public class Message { public Guid Id { get; set; } public virtual ICollection Address { get; set; }public virtual ICollection CopyToAddress { get; set; } })` — ALex Qz, Mar 09 '17 at 13:17

Entity Framework many-to-many With additional column

1 Answers1