CODEWITHSUNDEEP
c#sqlentity-frameworkdatabase-designentity-relationship
VansFannel
VansFannel

Reputation: 45911

Map a navigation property to a instance var as Foreign key

I'm developing an Entity Framework Code First (v. 4.4.0.0) C# library with .Net Framework 4.0.

I don't know how to set zero-to-one relationship. My model is the following:

A Talk can be created by only one user (StarterUserId).
A Talk can have only one recipient user (RecepientUserId) or only one group (RecipientGroupId).
Note: That means that RecepientUserId is null if RecipientGroupIdis not null; or RecepientUserId is not null if RecipientGroupIdis null.

A user can be a recipient of zero or n Talks, but a group can have zero or one Talk.

This is Talk class:

[DataContract]
public class Talk
{
    [DataMember]
    public int TalkId { get; set; }

    [DataMember]
    public int StarterUserId { get; set; }

    [DataMember]
    public int? RecipientUserId { get; set; }

    [DataMember]
    [ForeignKey("RecipientGroup")]
    public int? RecipientGroupId { get; set; }

    public DateTime DateUtcStarted { get; set; }

    [DataMember]
    public string DateStarted
    {
        get
        {
            return DateUtcStarted.ToString("dd/MM/yyyy HH:mm");
        }
        set
        {
            DateUtcStarted = DateTime.Parse(value);
        }
    }

    public User StarterUser { get; set; }
    public User RecipientUser { get; set; }

    public Group RecipientGroup { get; set; }
}

With this TalkConfiguration class:

class TalkConfiguration : EntityTypeConfiguration<Talk>
{
    public TalkConfiguration()
    {
        Property(t => t.StarterUserId).IsRequired();
        Property(t => t.RecipientUserId).IsOptional();
        Property(t => t.RecipientGroupId).IsOptional();
        Property(t => t.DateUtcStarted).IsRequired();

        Ignore(t => t.DateStarted);

        HasRequired(t => t.StarterUser).
            WithMany(u => u.TalksStarted).
            HasForeignKey(t => t.StarterUserId);
        HasOptional(t => t.RecipientUser).
            WithMany(u => u.InTalks).
            HasForeignKey(t => t.RecipientUserId);

        HasOptional(t => t.RecipientGroup).WithOptionalDependent(g => g.GroupTalk);
    }
}

And this is the Group class:

[DataContract]
public class Group
{
    [DataMember]
    public int GroupId { get; set; }

    [ ... ]

    public Talk GroupTalk { get; set; }
}

And the GroupConfiguration class:

class GroupConfiguration : EntityTypeConfiguration<Group>
{
    public GroupConfiguration()
    {
        [ ... ] // Nothing related to GroupTalk
    }
}

With these classes and configurations I get this Talk table at database:

enter image description here

I want to make Talk.RecipientGroupId as a FOREIGN KEY to Group.GroupId. But this model creates another column, Talk.RecipientGroup_GroupId as FOREIGN KEY to Group.GroupId. And, I don't want that.

How can I do it?

Upvotes: 2

Views: 535

Answers (1)

Slauma
Slauma

Reputation: 177133

Optional:optional one-to-one relationships are mapped as independent associations, not as foreign key associations which means that you can't have a foreign key property in your model class. That's why you can't chain HasForeignKey after WithOptionalDependent. And I'm pretty sure that the [ForeignKey] attribute on RecipientGroupId is simply ignored and EF considers RecipientGroupId as an ordinary scalar property with no relationship purpose.

In the database schema itself the relationship has a foreign key. That's the one you are seeing with an autogenerated default name: RecipientGroup_GroupId. But it's not supported to map this foreign key to a property. However, I think you can rename the column using MapKey

HasOptional(t => t.RecipientGroup)
    .WithOptionalDependent(g => g.GroupTalk)
    .Map(m => m.MapKey("RecipientGroupId"));

If you do that you must remove the RecipientGroupId property from the Talk class, otherwise EF will complain about two conflicting columns with the same name.

I believe, optional:optional are the only one-to-one relationships that are independent associations, all other are foreign key associations where the foreign key property is the primary key property at the same time (according to Arthur Vickers' answer at the bottom of this thread). With optional:optional relationships this would be impossible because a primary key property cannot be nullable.

Since your RecipientGroupId has a [DataMember] attribute it looks that you want to transmit the value over some service boundary and therefore need the foreign key as property value for some reason. In this case the workaround that I would choose is mapping the Talk<->Group relationship as one-to-many relationship with either no navigation property in the Group class at all (mapping it with a parameterless WithMany() call then) or with a collection navigation property and ensure then in business logic that this collection cannot contain more than one element.

Upvotes: 1

Related Questions