Entity Framework Code First : how to annotate a foreign key for a "Default" value?

Question

I have 2 classes: Client and Survey.

Each Client can have many surveys - but only one default survey.

I have defined the classes like this:

public class Client
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    public string ClientName { get; set; }

    public Nullable<int> DefaultSurveyID { get; set; }

    [ForeignKey("DefaultSurveyID")]
    public virtual Survey DefaultSurvey { get; set; }

    public virtual ICollection<Survey> Surveys { get; set; }
}

public class Survey
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    public string SurveyName { get; set; }

    [Required]
    public int ClientID { get; set; }

    [ForeignKey("ClientID")]
    public virtual Client Client { get; set; }
}

This creates the Client table as I expect:

[dbo].[Clients]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[ClientName] [nvarchar](max) NULL,
[DefaultSurveyID] [int] NULL
)

But the Survey table has an extra foreign key:

[dbo].[Surveys]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[SurveyName] [nvarchar](max) NULL,
[ClientID] [int] NOT NULL,
[Client_ID] [int] NULL
)

Why is Code First generating this relationship and how to I tell it not to?

Different ways of modelling this problem in a database: http://stackoverflow.com/q/5244920/150342 — Colin, Mar 18 '13 at 21:32

educampver · Accepted Answer · 2013-03-19T03:21:31.397

42

The problem is that when you have multiple relationships between two entities, EF Code First isn't able to find out which navigation properties match up, unless, you tell it how, here is the code:

public class Client
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }

    public string ClientName { get; set; }

    /****Change Nullable<int> by int?, looks better****/
    public int? DefaultSurveyID { get; set; }

    /****You need to add this attribute****/
    [InverseProperty("ID")]
    [ForeignKey("DefaultSurveyID")]
    public virtual Survey DefaultSurvey { get; set; }

    public virtual ICollection<Survey> Surveys { get; set; }
}

With your previous version, EF was creating that extra relationship because it didn't know that the DefaultSurvey property was referencing the ID of the Survey class, but you can let it know that, adding the attribute InverseProperty whose parameter is the name of the property in Survey you need DefaultSurvey to match with.

edited Mar 19 '13 at 03:21

answered Mar 18 '13 at 19:22

educampver

2,967
2
23
34

A client can have many surveys but only one default survey, and that survey should be in the set of surveys defined by the first relationship. – Colin Mar 18 '13 at 19:49
@Colin: Sorry, my bad, it was a typo, the relationships are **one-to-many**, I fixed my answer. Try the code I posted and reply. – educampver Mar 18 '13 at 21:00
A survey cannot be the default survey for many clients because the survey can only be linked to one client. Therefore the Survey class should not contain a ClientsDefault collection – Colin Mar 18 '13 at 21:18
@Colin: Sorry then, I can't see a way through :( – educampver Mar 18 '13 at 21:58
@Colin: I got it!!! Totally change my answer, first time I misunderstood your question, check out now!!! – educampver Mar 19 '13 at 03:23
The other answers are really useful, but this is the only one that answers my question in full (Why is Code First generating this relationship and how to I tell it not to?). Brilliant! Many thanks. +1 from me, and marked as the solution. :-) – Colin Mar 19 '13 at 09:29

Phil · Answer 2 · 2013-03-18T19:14:52.403

You can do it using code-first, but not being a code first expert I cheated :-)

1) I created the tables and relationships (as above without the extra Client_ID) in the database using SMS

2) I used Reverse Engineer Code First to create the required classes and mappings

3) I dropped the database and recreated it using context.Database.Create()

Original table defs:

CREATE TABLE [dbo].[Client](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [DefaultSurveyId] [int] NULL,
     CONSTRAINT [PK_dbo.Client] PRIMARY KEY NONCLUSTERED 
    (
        [Id] ASC
    )
)

CREATE TABLE [dbo].[Survey](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [ClientId] [int] NULL,
     CONSTRAINT [PK_dbo.Survey] PRIMARY KEY NONCLUSTERED 
    (
        [Id] ASC
    )
)

Plus foreign keys

ALTER TABLE [dbo].[Survey]  WITH CHECK 
    ADD CONSTRAINT [FK_dbo.Survey_dbo.Client_ClientId] FOREIGN KEY([ClientId])
    REFERENCES [dbo].[Client] ([Id])

ALTER TABLE [dbo].[Client]  WITH CHECK 
    ADD CONSTRAINT [FK_dbo.Client_dbo.Survey_DefaultSurveyId] 
    FOREIGN KEY([DefaultSurveyId]) REFERENCES [dbo].[Survey] ([Id])

Code generated by reverse engineering:

public partial class Client
{
    public Client()
    {
        this.Surveys = new List<Survey>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public int? DefaultSurveyId { get; set; }
    public virtual Survey DefaultSurvey { get; set; }
    public virtual ICollection<Survey> Surveys { get; set; }
}

public partial class Survey
{
    public Survey()
    {
        this.Clients = new List<Client>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public int? ClientId { get; set; }
    public virtual ICollection<Client> Clients { get; set; }
    public virtual Client Client { get; set; }
}

public class ClientMap : EntityTypeConfiguration<Client>
{
    #region Constructors and Destructors

    public ClientMap()
    {
        // Primary Key
        this.HasKey(t => t.Id);

        // Properties
        this.Property(t => t.Name).HasMaxLength(50);

        // Table & Column Mappings
        this.ToTable("Client");
        this.Property(t => t.Id).HasColumnName("Id");
        this.Property(t => t.Name).HasColumnName("Name");
        this.Property(t => t.DefaultSurveyId).HasColumnName("DefaultSurveyId");

        // Relationships
        this.HasOptional(t => t.DefaultSurvey)
            .WithMany(t => t.Clients).HasForeignKey(d => d.DefaultSurveyId);
    }

    #endregion
}

public class SurveyMap : EntityTypeConfiguration<Survey>
{
    #region Constructors and Destructors

    public SurveyMap()
    {
        // Primary Key
        this.HasKey(t => t.Id);

        // Properties
        this.Property(t => t.Name).HasMaxLength(50);

        // Table & Column Mappings
        this.ToTable("Survey");
        this.Property(t => t.Id).HasColumnName("Id");
        this.Property(t => t.Name).HasColumnName("Name");
        this.Property(t => t.ClientId).HasColumnName("ClientId");

        // Relationships
        this.HasOptional(t => t.Client)
            .WithMany(t => t.Surveys).HasForeignKey(d => d.ClientId);
    }

    #endregion
}

This is an excellent solution that uses the Fluent API to address the problem. It really helped me understand what can be done with the Entity Framework +1 — Colin, Mar 19 '13 at 09:17
Just giving me the link and telling me about the "EF Power Tools" was worth it's weight in gold. Thanks — glenn garson, Dec 31 '14 at 19:06

score 2 · Answer 3 · answered Mar 18 '13 at 17:42

2

Entity Framework does exactly what it's told to do. What you've told it is that there is both a one-to-many and a one-to-one relationship between Clients and Surveys. It generated both FKs in the Survey table in order to map both of the relationships that you've requested. It has no idea that you're trying to connect the two relationships together, nor do I think does it have the ability to deal with that.

As an alternative you might want to consider adding a IsDefaultSurvey field on the Survey object so that you can query for the default survey through the Surveys collection that you have on the Client object. You could even go one step further and put it in as a NotMapped property on the Client object so that you could still use Client.DefaultSurvey to get the correct survey, and not have to change any of your other code, as follows:

[NotMapped]
public Survey DefaultSurvey
{
  get { return this.Surveys.First(s => s.IsDefaultSurvey); }
}

answered Mar 18 '13 at 17:42

Corey Adler

15,897
18
66
80

You would have to somehow tell the database which one is the default survey. You could do that as part of the edit page for the survey, and then have validation to make sure there is only one default survey on the client. – Corey Adler Mar 18 '13 at 17:48
I'm not quite getting this. I've specified a one-to-many by putting the Client and ClientID properties in the Survey class. I've specified a one-to-one by putting DefaultSurvey and DefaultSurveyID in the Client class - but there is nothing to say it's one-to-one is there? Confused.... – Colin Mar 18 '13 at 18:20
And EF is generating 3 relationships, not just 2? – Colin Mar 18 '13 at 18:29
Here's how this goes: You set up the one to many by having the *collection* of `Survey` on the Client. You set up the 1:1 by having the `DefaultSurvey` field on the Client. The Client fields on the `Survey` object just serve to link up those connections. – Corey Adler Mar 18 '13 at 18:29
2 relationships: One to many on the collection, and a 1:1 on the single `DefaultSurvey` field. – Corey Adler Mar 18 '13 at 18:30
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26393/discussion-between-colin-and-ironman84) – Colin Mar 18 '13 at 18:31
This is a great way to work around the problem, and some people might argue that the database schema is better because additional constraints can be added http://dba.stackexchange.com/q/17456/18422. However, like some others ( http://stackoverflow.com/a/5493920/150342 ) I 'think the solution in the question expresses the "natural" relationships of the model better'. +1 for helping me understand the options available in the Entity Framework – Colin Mar 19 '13 at 09:25

score 1 · Answer 4 · answered Apr 25 '14 at 19:33

Please notice that adding the code below you will fix the issue.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext() : base("DefaultConnection")
    {

    }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
          modelBuilder.Entity<Client>()
                      .HasOptional(x => x.DefaultSurvey)
                      .WithMany(x => x.Surveys);
                      .HasForeignKey(p => p.DefaultSurveyID);
    {
}

Entity Framework Code First : how to annotate a foreign key for a "Default" value?

4 Answers4

Linked