title: MVC Code First Entity Frame Work reference same oject twice from master object
body: <p>Have a code first MVC4 model that requires foreign key look up to the same table. i.e My Project table has two columns (BaseFiscalId and IMITApprovalCycleId) that reference the same table i.e. FiscalYears. How do I define this in the code.</p>
  
  <p>Currently I have the following:</p>
  
  <pre><code>public class Project
  {
      [Required]
      public int Id { get; set; }
  
      [Required]
      public int InitiativeId { get; set; }
  
      [Required]
      public String ProjectName { get; set; }
  
      [Required]
      public int SubPortFolioId { get; set; }
  
      [Required]
      public String Description { get; set; }
  
      [Required]
      public int ProjectTypeId { get; set; }
  
      [Required]
      public int FundingSourceId { get; set; }
  
      [Required]
      public int FundingPhaseAId { get; set; }
  
      [Required]
      public int ApprovalStatusId { get; set; }
  
      [Required]
      public int IMITApprovalProcessId { get; set; }
  
      [Required]
      public int IMITApprovalCycleId { get; set; }
  
      [Required]
      public int AccountableExecutiveId { get; set; }
  
      [Required]
      public int LeadMinistryId { get; set; }
  
      [Required]
      public int BaseFiscalId { get; set; }
  
      [Required]
      public int TotalSpentToBase { get; set; }
  
      //Navigation Properties --Child Projects
      public ICollection&lt;Spend&gt; Spends { get; set; }
  
      public SubPortfolio SubPortfolio { get; set; }
      public ProjectType ProjectType { get; set; }
      public FundingPhase FundingPhase { get; set; }
      public FundingSource FundingSource { get; set; }
      public ApprovalStatus ApprovalStatus { get; set; }
      public IMITApprovalProcess IMITApprovalProcess { get; set; }
      public FiscalYear IMITApprovalCycle { get; set; }
      public FiscalYear BaseFiscal { get; set; }
      public Portfolio Portfolio { get; set; }
      public Executive AccountableExecutive { get; set; }
      public Ministry LeadMinistry { get; set; }
      public Initiative Initiative { get; set; }
  
  
  }
  </code></pre>
  
  <p>FiscalYears Class</p>
  
  <pre><code>public class FiscalYear
  {
      [ScaffoldColumn(false)]
      public int Id { get; set; }
      [Required]
      public String FiscalYearName { get; set; }
  }
  </code></pre>
  
  <p>This results in the following SQL:</p>
  
  <pre><code>CREATE TABLE [dbo].[Projects] (
         [Id]                     INT            NOT NULL,
         [InitiativeId]           INT            NOT NULL,
         [ProjectName]            NVARCHAR (MAX) NOT NULL,
         [SubPortFolioId]         INT            NOT NULL,
         [Description]            NVARCHAR (MAX) NOT NULL,
         [ProjectTypeId]          INT            NOT NULL,
         [FundingSourceId]        INT            NOT NULL,
         [FundingPhaseAId]        INT            NOT NULL,
         [ApprovalStatusId]       INT            NOT NULL,
         [IMITApprovalProcessId]  INT            NOT NULL,
         [IMITApprovalCycleId]    INT            NOT NULL,
         [AccountableExecutiveId] INT            NOT NULL,
         [LeadMinistryId]         INT            NOT NULL,
         [BaseFiscalId]           INT            NOT NULL,
         [TotalSpentToBase]       INT            NOT NULL,
         [FundingPhase_Id]        INT            NULL,
         [Portfolio_Id]           INT            NULL,
         [Initiative_Id]          INT            NULL,
         CONSTRAINT [PK_dbo.Projects] PRIMARY KEY CLUSTERED ([Id] ASC),
         CONSTRAINT [FK_dbo.Projects_dbo.SubPortfolios_SubPortFolioId] FOREIGN KEY ([SubPortFolioId]) REFERENCES [dbo].[SubPortfolios] ([Id]) ON DELETE CASCADE,
         CONSTRAINT [FK_dbo.Projects_dbo.ProjectTypes_ProjectTypeId] FOREIGN KEY ([ProjectTypeId]) REFERENCES [dbo].[ProjectTypes] ([Id]) ON DELETE CASCADE,
         CONSTRAINT [FK_dbo.Projects_dbo.FundingPhases_FundingPhase_Id] FOREIGN KEY ([FundingPhase_Id]) REFERENCES [dbo].[FundingPhases] ([Id]),
         CONSTRAINT [FK_dbo.Projects_dbo.FundingSources_FundingSourceId] FOREIGN KEY ([FundingSourceId]) REFERENCES [dbo].[FundingSources] ([Id]) ON DELETE CASCADE,
         CONSTRAINT [FK_dbo.Projects_dbo.ApprovalStatus_ApprovalStatusId] FOREIGN KEY ([ApprovalStatusId]) REFERENCES [dbo].[ApprovalStatus] ([Id]) ON DELETE CASCADE,
         CONSTRAINT [FK_dbo.Projects_dbo.IMITApprovalProcesses_IMITApprovalProcessId] FOREIGN KEY ([IMITApprovalProcessId]) REFERENCES [dbo].[IMITApprovalProcesses] ([Id]) ON DELETE CASCADE,
         **CONSTRAINT [FK_dbo.Projects_dbo.FiscalYears_Id] FOREIGN KEY ([Id]) REFERENCES [dbo].[FiscalYears] ([Id])**,
         CONSTRAINT [FK_dbo.Projects_dbo.Portfolios_Portfolio_Id] FOREIGN KEY ([Portfolio_Id]) REFERENCES [dbo].[Portfolios] ([Id]),
         CONSTRAINT [FK_dbo.Projects_dbo.Executives_AccountableExecutiveId] FOREIGN KEY ([AccountableExecutiveId]) REFERENCES [dbo].[Executives] ([Id]) ON DELETE CASCADE,
         CONSTRAINT [FK_dbo.Projects_dbo.Ministries_LeadMinistryId] FOREIGN KEY ([LeadMinistryId]) REFERENCES [dbo].[Ministries] ([Id]) ON DELETE CASCADE,
         CONSTRAINT [FK_dbo.Projects_dbo.Initiatives_Initiative_Id] FOREIGN KEY ([Initiative_Id]) REFERENCES [dbo].[Initiatives] ([Id])
  </code></pre>
  
  <p>Note instead of creating two FK relationships to FiscalYears One each for BaseFiscal and IMITApprovalCycle it create only on on Fiscal_Id which does not exist in the Projects Table.</p>
  
  <p>Thanks</p>
  
  <p>Craig</p>