Having trouble with database first generated MVC 4 model with foreign keys

Question

The table with multiple columns pointing to same People table i.e. person class

namespace IGRS.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;

    public partial class HinduMarriageForm
    {
        public int ID { get; set; }
        [ForeignKey("Person"), Column(Order=0)]
        public int HusbandID { get; set; }
        public int HusbandsFatherID { get; set; }
        public int HusbandsMotherID { get; set; }
        public int WifeID { get; set; }
        public int WifesFatherID { get; set; }
        public int WifesMotherID { get; set; }
        public Nullable<int> HusbandsNRIInfoID { get; set; }
        public Nullable<int> WifesNRIInfoID { get; set; }
        public Nullable<int> PersonWhoSolemnizedMarriageID { get; set; }
        public bool SolemnizedUnderSection7 { get; set; }
        public bool SolemnizedUnderSection7A { get; set; }
        public int MarriageVenueID { get; set; }
        public System.DateTime DateOfMarriage { get; set; }
        public int Witness1ID { get; set; }
        public int Witness2ID { get; set; }
        public int Witness3ID { get; set; }
        public int SROID { get; set; }
        public int SRID { get; set; }

        public virtual MarriageVenue MarriageVenue { get; set; }
        public virtual NRIInformation NRIInformation { get; set; }
        public virtual NRIInformation NRIInformation1 { get; set; }
        public virtual Person Person { get; set; }
        public virtual Person Person1 { get; set; }
        public virtual Person Person2 { get; set; }
        public virtual Person Person3 { get; set; }
        public virtual Person Person4 { get; set; }
        public virtual Person Person5 { get; set; }
        public virtual Person Person6 { get; set; }
        public virtual Person Person7 { get; set; }
        public virtual Person Person8 { get; set; }
        public virtual Person Person9 { get; set; }
        public virtual SR SR { get; set; }
        public virtual SRO SRO { get; set; }
    }
}

This is the auto generated by database first MVC4 methodology Entity Framework 5 VS2012 defaults:

namespace IGRS.Models
{
    using System;
    using System.Collections.Generic;

    public partial class Person
    {
        public Person()
        {
            this.HinduMarriageForms = new HashSet<HinduMarriageForm>();
            this.HinduMarriageForms1 = new HashSet<HinduMarriageForm>();
            this.HinduMarriageForms2 = new HashSet<HinduMarriageForm>();
            this.HinduMarriageForms3 = new HashSet<HinduMarriageForm>();
            this.HinduMarriageForms4 = new HashSet<HinduMarriageForm>();
            this.HinduMarriageForms5 = new HashSet<HinduMarriageForm>();
            this.HinduMarriageForms6 = new HashSet<HinduMarriageForm>();
            this.HinduMarriageForms7 = new HashSet<HinduMarriageForm>();
            this.HinduMarriageForms8 = new HashSet<HinduMarriageForm>();
            this.HinduMarriageForms9 = new HashSet<HinduMarriageForm>();
        }

        public int ID { get; set; }
        public string FullName { get; set; }
        public Nullable<int> ReligionID { get; set; }
        public Nullable<int> CasteID { get; set; }
        public Nullable<System.DateTime> DateOfBirth { get; set; }
        public Nullable<int> MaritalStatusID { get; set; }
        public Nullable<int> OccupationID { get; set; }
        public string PermanentAddress { get; set; }
        public string PassportNumber { get; set; }

        public virtual Caste Caste { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms1 { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms2 { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms3 { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms4 { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms5 { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms6 { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms7 { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms8 { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms9 { get; set; }
        public virtual MaritalState MaritalState { get; set; }
        public virtual Occupation Occupation { get; set; }
        public virtual Religion Religion { get; set; }
    }
}

And the NRIInformation table info:

namespace IGRS.Models
{
    using System;
    using System.Collections.Generic;

    public partial class NRIInformation
    {
        public NRIInformation()
        {
            this.HinduMarriageForms = new HashSet<HinduMarriageForm>();
            this.HinduMarriageForms1 = new HashSet<HinduMarriageForm>();
        }

        public int ID { get; set; }
        public string PassportNumber { get; set; }
        public string PassportIssuedBy { get; set; }
        public System.DateTime PassportValidUpto { get; set; }
        public string VisaIssued { get; set; }
        public System.DateTime VisaValidUpto { get; set; }
        public int TypeOfResidentID { get; set; }
        public string SocialSecurityNumber { get; set; }

        public virtual ICollection<HinduMarriageForm> HinduMarriageForms { get; set; }
        public virtual ICollection<HinduMarriageForm> HinduMarriageForms1 { get; set; }
    }
}

This upon running on the HinduMarriageForm controller code obvioous so not providing gives the error:

Server Error in '/' Application.

Invalid column name 'NRIInformation_ID'. Invalid column name 'NRIInformation_ID1'. Invalid column name 'NRIInformation_ID2'. Invalid column name 'NRIInformation1_ID'. Invalid column name 'Person_ID'. Invalid column name 'Person_ID1'. Invalid column name 'Person_ID2'. Invalid column name 'Person_ID3'. Invalid column name 'Person_ID4'. Invalid column name 'Person_ID5'. Invalid column name 'Person_ID6'. Invalid column name 'Person_ID7'. Invalid column name 'Person_ID8'. Invalid column name 'Person_ID9'. Invalid column name 'Person1_ID'. Invalid column name 'Person2_ID'. Invalid column name 'Person3_ID'. Invalid column name 'Person4_ID'. Invalid column name 'Person5_ID'. Invalid column name 'Person6_ID'. Invalid column name 'Person7_ID'. Invalid column name 'Person8_ID'. Invalid column name 'Person9_ID'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'NRIInformation_ID'. Invalid column name 'NRIInformation_ID1'. Invalid column name 'NRIInformation_ID2'. Invalid column name 'NRIInformation1_ID'. Invalid column name 'Person_ID'. Invalid column name 'Person_ID1'. Invalid column name 'Person_ID2'. Invalid column name 'Person_ID3'. Invalid column name 'Person_ID4'. Invalid column name 'Person_ID5'. Invalid column name 'Person_ID6'. Invalid column name 'Person_ID7'. Invalid column name 'Person_ID8'. Invalid column name 'Person_ID9'. Invalid column name 'Person1_ID'. Invalid column name 'Person2_ID'. Invalid column name 'Person3_ID'. Invalid column name 'Person4_ID'. Invalid column name 'Person5_ID'. Invalid column name 'Person6_ID'. Invalid column name 'Person7_ID'. Invalid column name 'Person8_ID'. Invalid column name 'Person9_ID'.

Source Error:

Line 20: { Line 21: var hindumarriageforms = db.HinduMarriageForms.Include(h => h.MarriageVenue).Include(h => h.SR).Include(h => h.SRO); Line 22: return View(hindumarriageforms.ToList()); Line 23: } Line 24:

Source File: c:\Users\gilgamesh\Documents\Visual Studio 2012\Projects\IGRS\IGRS\Controllers\HinduMarriageFormController.cs Line: 22

Stack Trace:

[SqlException (0x80131904): Invalid column name 'NRIInformation_ID'. Invalid column name 'NRIInformation_ID1'. Invalid column name 'NRIInformation_ID2'. Invalid column name 'NRIInformation1_ID'. Invalid column name 'Person_ID'. Invalid column name 'Person_ID1'. Invalid column name 'Person_ID2'. Invalid column name 'Person_ID3'. Invalid column name 'Person_ID4'. Invalid column name 'Person_ID5'. Invalid column name 'Person_ID6'. Invalid column name 'Person_ID7'. Invalid column name 'Person_ID8'. Invalid column name 'Person_ID9'. Invalid column name 'Person1_ID'. Invalid column name 'Person2_ID'. Invalid column name 'Person3_ID'. Invalid column name 'Person4_ID'. Invalid column name 'Person5_ID'. Invalid column name 'Person6_ID'. Invalid column name 'Person7_ID'. Invalid column name 'Person8_ID'. Invalid column name 'Person9_ID'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +1767866 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) +5352418 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() +61 System.Data.SqlClient.SqlDataReader.get_MetaData() +90 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +365 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1406 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41 System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10 System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +437

[EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.] System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +507 System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute(ObjectContext context, ObjectParameterCollection parameterValues) +730 System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) +131 System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +36 System.Data.Entity.Internal.Linq.InternalQuery1.GetEnumerator() +126 System.Data.Entity.Infrastructure.DbQuery1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator() +99 System.Collections.Generic.List1..ctor(IEnumerable1 collection) +369 System.Linq.Enumerable.ToList(IEnumerable1 source) +58 IGRS.Controllers.HinduMarriageFormController.Index() in c:\Users\gilgamesh\Documents\Visual Studio 2012\Projects\IGRS\IGRS\Controllers\HinduMarriageFormController.cs:22 lambda_method(Closure , ControllerBase , Object[] ) +101 System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14 System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +211 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +27 System.Web.Mvc.Async.<>c_DisplayClass42.b_41() +28 System.Web.Mvc.Async.<>c_DisplayClass81.<BeginSynchronous>b__7(IAsyncResult _) +10 System.Web.Mvc.Async.WrappedAsyncResult1.End() +57 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +48 System.Web.Mvc.Async.<>c_DisplayClass39.b_33() +57 System.Web.Mvc.Async.<>c_DisplayClass4f.b_49() +223 System.Web.Mvc.Async.<>c_DisplayClass37.b_36(IAsyncResult asyncResult) +10 System.Web.Mvc.Async.WrappedAsyncResult1.End() +57 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +48 System.Web.Mvc.Async.<>c__DisplayClass2a.<BeginInvokeAction>b__20() +24 System.Web.Mvc.Async.<>c__DisplayClass25.<BeginInvokeAction>b__22(IAsyncResult asyncResult) +102 System.Web.Mvc.Async.WrappedAsyncResult1.End() +57 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +43 System.Web.Mvc.<>c_DisplayClass1d.b_18(IAsyncResult asyncResult) +14 System.Web.Mvc.Async.<>c_DisplayClass4.b_3(IAsyncResult ar) +23 System.Web.Mvc.Async.WrappedAsyncResult1.End() +62 System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +57 System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23 System.Web.Mvc.Async.WrappedAsyncResult1.End() +62 System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +47 System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10 System.Web.Mvc.<>c_DisplayClass8.b_3(IAsyncResult asyncResult) +25 System.Web.Mvc.Async.<>c_DisplayClass4.b__3(IAsyncResult ar) +23 System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62 System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +47 System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9514812 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.18408

So far I have tried a bunch of things using ForeignKey and Column with Order decorations etc but can only reduce number of errors. I am not sure what is going on wrong with the reflection. Also here are the SQL create table scripts for the 2 foreign key tables and the source table:

CREATE TABLE [dbo].[HinduMarriageForms](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [HusbandID] [int] NOT NULL,
    [HusbandsFatherID] [int] NOT NULL,
    [HusbandsMotherID] [int] NOT NULL,
    [WifeID] [int] NOT NULL,
    [WifesFatherID] [int] NOT NULL,
    [WifesMotherID] [int] NOT NULL,
    [HusbandsNRIInfoID] [int] NULL,
    [WifesNRIInfoID] [int] NULL,
    [PersonWhoSolemnizedMarriageID] [int] NULL,
    [SolemnizedUnderSection7] [bit] NOT NULL,
    [SolemnizedUnderSection7A] [bit] NOT NULL,
    [MarriageVenueID] [int] NOT NULL,
    [DateOfMarriage] [datetime] NOT NULL,
    [Witness1ID] [int] NOT NULL,
    [Witness2ID] [int] NOT NULL,
    [Witness3ID] [int] NOT NULL,
    [SROID] [int] NOT NULL,
    [SRID] [int] NOT NULL,
 CONSTRAINT [PK_HinduMarriageForm] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[NRIInformations](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PassportNumber] [nvarchar](50) NOT NULL,
    [PassportIssuedBy] [nvarchar](255) NOT NULL,
    [PassportValidUpto] [datetime] NOT NULL,
    [VisaIssued] [nvarchar](255) NOT NULL,
    [VisaValidUpto] [datetime] NOT NULL,
    [TypeOfResidentID] [int] NOT NULL,
    [SocialSecurityNumber] [nchar](10) NULL,
 CONSTRAINT [PK_NRIInformation] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[People](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FullName] [nvarchar](255) NOT NULL,
    [ReligionID] [int] NULL,
    [CasteID] [int] NULL,
    [DateOfBirth] [datetime] NULL,
    [MaritalStatusID] [int] NULL,
    [OccupationID] [int] NULL,
    [PermanentAddress] [nvarchar](512) NOT NULL,
    [PassportNumber] [nvarchar](50) NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Not pasting the foreign key SQL creation statements assume they are all there in back end sql server database.

This whole MVC 4 Entity Framework 5 project was Database First Driven and giving these errors. The simpler tables which dont have multiple columns linked to same foreign key table and column are working fine.

Found this stackoverflow comment but couldnt apply it because it assumed that the object/row being pointed to is same which is not in my case as they are different person/people/rows. Here is link to the other stackoverflow q&a: http://stackoverflow.com/questions/5436731/composite-key-as-foreign-key — , Dec 26 '13 at 19:31

Geoff Gunter · Accepted Answer · 2013-12-26T19:57:29.360

From this section of your error message:

    Source Error:

Line 20: { Line 21: var hindumarriageforms = db.HinduMarriageForms.Include(h => h.MarriageVenue).Include(h => h.SR).Include(h => h.SRO); Line 22: return View(hindumarriageforms.ToList()); Line 23: } Line 24:

Source File: c:\Users\gilgamesh\Documents\Visual Studio 2012\Projects\IGRS\IGRS\Controllers\HinduMarriageFormController.cs Line: 22

If you also include the NRIInformation and Person tables, does the problem go away?

var hindumarriageforms = db.HinduMarriageForms.Include(h => h.MarriageVenue).Include(h => h.SR).Include(h => h.SRO).Include(h => h.Person).Include(h => h.NRIInformation);

Edit: As an aside, I usually like to rename the virtuals where instead of it being Person1, Person2, Person3... etc. Change it to PersonForHusband, PersonForHusbandsFather, PersonForHusbandsMother, etc. so that the navigation properties are clear and you don't accidentally use the wrong one. Makes programming with those classes later so much easier to use.

Having trouble with database first generated MVC 4 model with foreign keys

1 Answers1